MYSQL / PHP返回并显示来自多个表的数据作为集合

Question

I have four tables that I'm joining and want to return all rows in table lab_test that belong to a lab_test_group and display them like (groups in bold...):

---

Creatinine clearance

Creatinine (Fluid)

Creatinine (24 hour Urine)

Thyroid function tests (1)

Free T4

Thyroid Stimulating Hormone

---

The table structures with relevant data are:

lab_test

+-------------+-----------------------------+
| lab_test_pk |          lab_test           |
+-------------+-----------------------------+
|         191 | Creatinine (Fluid)          |
|         208 | Free T4                     |
|         782 | Creatinine (24 hour Urine)  |
|        1161 | Thyroid Stimulating Hormone |
+-------------+-----------------------------+

model_lab_test_lookup

+--------------------------+-------------+------------+
| model_lab_test_lookup_pk | lab_test_fk | pathway_fk |
+--------------------------+-------------+------------+
|                       26 |           2 |         90 |
|                       27 |           8 |         90 |
+--------------------------+-------------+------------+

lab_test_group

+-------------------+----------------------------+
| lab_test_group_pk |         group_name         |
+-------------------+----------------------------+
|                 2 | Creatinine clearance       |
|                 8 | Thyroid function tests (1) |
+-------------------+----------------------------+

lab_test_group_lookup

+--------------------------+-------------------+--------------+
| lab_test_group_lookup_pk | lab_test_group_fk |  lab_test_fk |
+--------------------------+-------------------+--------------+
|                        6 |                 2 |          191 |
|                        7 |                 2 |          782 |
|                       41 |                 8 |          208 |
|                       42 |                 8 |         1161 |
+--------------------------+-------------------+--------------+

The query that I'm using is:

SELECT * 
  FROM lab_test_group, 
       lab_test_group_lookup, 
       model_lab_test_lookup, 
       lab_test 
WHERE lab_test_group.lab_test_group_pk = model_lab_test_lookup.lab_test_fk 
  AND lab_test_group_lookup.lab_test_group_fk = lab_test_group.lab_test_group_pk
  AND lab_test_group_lookup.lab_test_fk = lab_test.lab_test_pk
  AND model_lab_test_lookup.pathway_fk = '$pathway_pk' 
GROUP 
   BY lab_test_group.lab_test_group_pk

In this example $pathway_pk == 90.

With the following code to display:

<?php
while ($row_lab_test_groups = mysql_fetch_assoc($result_lab_test_groups)){
    $test_groups_array[] = $row_lab_test_groups;

    echo "<tr><td colspan='5'>" . $row_lab_test_groups['group_name'] . "</td></tr>";

    foreach($test_groups_array as $r){
         echo "<tr><td>" . $r['lab_test'] . "</td></tr>";
    }
}           
?>

Now this returns:

Creatinine clearance -

Creatinine (Fluid)

Thyroid function tests (1) -

Creatinine (Fluid)
Free T4

Part of the problem is the GROUP BY where only one record for each lab_test is returned, instead of the two lab_tests for each lab_test_group.

The question is how can I get the all the lab tests showing under the relevant lab_test_group? That is two for each group.

NOTE:

lab_test_fk in table model_lab_test_lookup is used to hold keys for individual lab_test rows as well as keys for lab_test_group...

Answer 1

You overdid the table structures just a little bit. There's really no need to have "linking" tables with just primary and foreign keys. But that train has passed and we'll turn a blind eye to it. Use the following SQL statement on what you have:

SELECT * FROM 
lab_test_group AS ABB2
   JOIN lab_test_group_lookup AS ABB1 ON ABB1.lab_test_group_fk = ABB2.lab_test_group_pk
      JOIN model_lab_test_lookup AS ABB3 ON ABB3.lab_test_fk = ABB2.lab_test_group_pk
        JOIN lab_test AS ABB4 ON ABB4.lab_test_pk = ABB1.lab_test_fk
WHERE ABB3.pathway_fk = $pathway_pk;

It's really advantageous to use actual JOIN operators in a complex query like this instead of putting all the links in the WHERE statement. You'll be able to picture the joints between the tables much better.

Working example on SQLFiddle here