比较两个表中的数据并检查相同的数据

Question

你好，我的数据库里有两张桌子。

第一种是桌子上的疾病，看上去像上面那样.

​

​

第二个是桌子病人，看起来像这样.

​

​

我有一个编辑页面，我想让用户能够更新他/她的疾病。下面是一个例子。

​

​

我要做的是检查表格病人的疾病列，用表格疾病的列名检查，如果表病人的数据与表疾病的数据相同，那么从复选框中检查相同的疾病。

我试着想办法做这件事--这是我的代码.

<?php
$sql = "SELECT name FROM disease UNION SELECT disease FROM patient WHERE username='$username'";
$query_resource = mysql_query($sql);

  while( $name = mysql_fetch_assoc($query_resource) ):
?>
    <span><?php echo $name['name']; ?></span>
    <input type="checkbox" name="disease[]" value="<?php echo $name['name']; ?>" /><br />


<?php endwhile; ?>

我把疾病储存在数据库里的方法是.

$disease = implode(",",$_POST["disease"]);

因为一个用户可能有很多疾病

Answer 1

然而，我的回答不是你会寻找什么。如果您构建这个应用程序，您应该考虑集成person和desies表之间的关系。因为它是MySql关系数据库:)

如果我把你说对了:没有测试

<?php

$sql = "SELECT disease FROM patient WHERE username='$username'";
$query_resource = mysql_query($sql);
$person = mysql_fetch_assoc($query_resource);

$persons_desisies = explode(',', $person['disease']);

$sql = "SELECT name FROM disease";
$query_resource = mysql_query($sql);
$disease = Array(); 
  while( $name = mysql_fetch_assoc($query_resource) ):
      ?>
      <span><?php echo $name['name']; ?></span>
      <input type="checkbox" name="disease[]" value="<?php echo $name['name']; ?>" <?php if(in_array($name['name'], $persons_desisies)): ?> checked="checked" <?php endif; ?>/><br />

  <?php endwhile; ?>

想法--你得到病人的需求列表，然后检查完整的需求列表。:)

Answer 2

供进一步审议：

DROP TABLE IF EXISTS patients;

CREATE TABLE patients 
(patient_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,name VARCHAR(12)
);

INSERT INTO patients VALUES
(101,'Adam'),
(102,'Ben'),
(103,'Charlie'),
(104,'Dan');

DROP TABLE IF EXISTS diseases;

CREATE TABLE diseases 
(disease_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,disease VARCHAR(50) NOT NULL UNIQUE
);

INSERT INTO diseases (disease) VALUES
('Allergy'),
('Cardiological'),
('Dermatological'),
('Gastrointestinal'),
('Gynaecological'),
('Ophthalmological'),
('Pathological'),
('Otorhinolaryngological'),
('Nephrological'),
('Neurological');

DROP TABLE IF EXISTS patient_disease;

CREATE TABLE patient_disease
(patient_id INT NOT NULL
,disease_id INT NOT NULL
,PRIMARY KEY(patient_id,disease_id)
);

INSERT INTO patient_disease VALUES 
(101,3),
(101,5),
(101,6),
(102,1),
(103,1),
(103,7),
(103,8);

SELECT p.*
     , d.*
     , CASE WHEN pd.disease_id IS NULL THEN 'no' ELSE 'yes' END manifest 
  FROM patients p 
  JOIN diseases d 
  LEFT 
  JOIN patient_disease pd 
    ON pd.patient_id = p.patient_id 
   AND pd.disease_id = d.disease_id 
 ORDER BY p.patient_id
     , d.disease_id;
+------------+---------+------------+------------------------+----------+
| patient_id | name    | disease_id | disease                | manifest |
+------------+---------+------------+------------------------+----------+
|        101 | Adam    |          1 | Allergy                | no       |
|        101 | Adam    |          2 | Cardiological          | no       |
|        101 | Adam    |          3 | Dermatological         | yes      |
|        101 | Adam    |          4 | Gastrointestinal       | no       |
|        101 | Adam    |          5 | Gynaecological         | yes      |
|        101 | Adam    |          6 | Ophthalmological       | yes      |
|        101 | Adam    |          7 | Pathological           | no       |
|        101 | Adam    |          8 | Otorhinolaryngological | no       |
|        101 | Adam    |          9 | Nephrological          | no       |
|        101 | Adam    |         10 | Neurological           | no       |
|        102 | Ben     |          1 | Allergy                | yes      |
|        102 | Ben     |          2 | Cardiological          | no       |
|        102 | Ben     |          3 | Dermatological         | no       |
|        102 | Ben     |          4 | Gastrointestinal       | no       |
|        102 | Ben     |          5 | Gynaecological         | no       |
|        102 | Ben     |          6 | Ophthalmological       | no       |
|        102 | Ben     |          7 | Pathological           | no       |
|        102 | Ben     |          8 | Otorhinolaryngological | no       |
|        102 | Ben     |          9 | Nephrological          | no       |
|        102 | Ben     |         10 | Neurological           | no       |
|        103 | Charlie |          1 | Allergy                | yes      |
|        103 | Charlie |          2 | Cardiological          | no       |
|        103 | Charlie |          3 | Dermatological         | no       |
|        103 | Charlie |          4 | Gastrointestinal       | no       |
|        103 | Charlie |          5 | Gynaecological         | no       |
|        103 | Charlie |          6 | Ophthalmological       | no       |
|        103 | Charlie |          7 | Pathological           | yes      |
|        103 | Charlie |          8 | Otorhinolaryngological | yes      |
|        103 | Charlie |          9 | Nephrological          | no       |
|        103 | Charlie |         10 | Neurological           | no       |
|        104 | Dan     |          1 | Allergy                | no       |
|        104 | Dan     |          2 | Cardiological          | no       |
|        104 | Dan     |          3 | Dermatological         | no       |
|        104 | Dan     |          4 | Gastrointestinal       | no       |
|        104 | Dan     |          5 | Gynaecological         | no       |
|        104 | Dan     |          6 | Ophthalmological       | no       |
|        104 | Dan     |          7 | Pathological           | no       |
|        104 | Dan     |          8 | Otorhinolaryngological | no       |
|        104 | Dan     |          9 | Nephrological          | no       |
|        104 | Dan     |         10 | Neurological           | no       |
+------------+---------+------------+------------------------+----------+