title: MYSQL/PHP return and display data from multiple tables as sets
body: <p>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...):</p>
  
  <hr>
  
  <p><strong>Creatinine clearance</strong> </p>
  
  <p>Creatinine (Fluid)  </p>
  
  <p>Creatinine (24 hour Urine)  </p>
  
  <p><strong>Thyroid function tests (1)</strong> </p>
  
  <p>Free T4</p>
  
  <p>Thyroid Stimulating Hormone</p>
  
  <hr>
  
  <p>The table structures with relevant data are:</p>
  
  <p>lab_test</p>
  
  <pre><code>+-------------+-----------------------------+
  | lab_test_pk |          lab_test           |
  +-------------+-----------------------------+
  |         191 | Creatinine (Fluid)          |
  |         208 | Free T4                     |
  |         782 | Creatinine (24 hour Urine)  |
  |        1161 | Thyroid Stimulating Hormone |
  +-------------+-----------------------------+
  </code></pre>
  
  <p>model_lab_test_lookup</p>
  
  <pre><code>+--------------------------+-------------+------------+
  | model_lab_test_lookup_pk | lab_test_fk | pathway_fk |
  +--------------------------+-------------+------------+
  |                       26 |           2 |         90 |
  |                       27 |           8 |         90 |
  +--------------------------+-------------+------------+
  </code></pre>
  
  <p>lab_test_group</p>
  
  <pre><code>+-------------------+----------------------------+
  | lab_test_group_pk |         group_name         |
  +-------------------+----------------------------+
  |                 2 | Creatinine clearance       |
  |                 8 | Thyroid function tests (1) |
  +-------------------+----------------------------+
  </code></pre>
  
  <p>lab_test_group_lookup</p>
  
  <pre><code>+--------------------------+-------------------+--------------+
  | lab_test_group_lookup_pk | lab_test_group_fk |  lab_test_fk |
  +--------------------------+-------------------+--------------+
  |                        6 |                 2 |          191 |
  |                        7 |                 2 |          782 |
  |                       41 |                 8 |          208 |
  |                       42 |                 8 |         1161 |
  +--------------------------+-------------------+--------------+
  </code></pre>
  
  <p>The query that I'm using is:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>In this example <code>$pathway_pk</code> == 90.</p>
  
  <p>With the following code to display:</p>
  
  <pre><code>&lt;?php
  while ($row_lab_test_groups = mysql_fetch_assoc($result_lab_test_groups)){
      $test_groups_array[] = $row_lab_test_groups;
  
      echo "&lt;tr&gt;&lt;td colspan='5'&gt;" . $row_lab_test_groups['group_name'] . "&lt;/td&gt;&lt;/tr&gt;";
  
      foreach($test_groups_array as $r){
           echo "&lt;tr&gt;&lt;td&gt;" . $r['lab_test'] . "&lt;/td&gt;&lt;/tr&gt;";
      }
  }           
  ?&gt;
  </code></pre>
  
  <p>Now this returns:</p>
  
  <p><strong>Creatinine clearance -</strong></p>
  
  <p>Creatinine (Fluid)   </p>
  
  <p><strong>Thyroid function tests (1) -</strong></p>
  
  <p>Creatinine (Fluid)<br>
  Free T4 </p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p><strong>NOTE:</strong></p>
  
  <p>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...</p>