What's the most efficient way to pull the data from mysql so that it is formatted as follows:

I have a table that has patient information (name, dob, ssn, etc.) and a table that has lists of medications that they take. (aspirin, claritin, etc.) The tables are related by a unique id from the patient table. So, it's easy enough to pull all of Mary Smith's medications.

But, what I need to do is to show a paginated list of patients that shows their name, other stuff from the patient table and has a column with a line-separated list of their medications. Roughly, this:

If I do a simple left join, I get 3 repeated rows of Mary Smith with one medication per row.

The patient table can have thousands of records, so I don't want to do a query to get all th开发者_高级运维e patients and then loop through and get their meds. And, because it's paginated based on patient, I can't figure out how to get the correct number of patients for the page, along with all their medications.

(The patients/medications thing is just a rough example of the data; so please don't suggest restructuring how the data is stored.)

---

GROUP_CONCAT to the rescue!

SELECT patients.first_name, patients.last_name, GROUP_CONCAT(prescriptions.medication SEPARATOR ", ") AS meds FROM patients LEFT JOIN prescriptions ON prescriptions.patient_id = patients.id GROUP BY patients.id;

---

You've got a few choices.

1. rowspan clauses with one drug per cell per user. You'd need to run two SQL queries to precalculate how big each user's span would have to be, or suck the query results into PHP and do the counting there.
2. Simple state machine - start a new row each time the user changes, then just keep adding more drug names seperated with <br /> while the user's name stays constant.

The second one's probably easiest:

$previous_name = null;
$first = true;

echo "<table";

while($row = mysql_fetch_assoc($results)) {
     if ($row['name'] <> $previous_name) {
         if (!$first) {
             echo "</td></tr>"; // end previous row, if it's not the first row we've output
             $first = false;
         }
         echo "<tr><td>$row[name]</td><td>"
         $previous_name = $row['name'];
     }
     echo "$row['drug']<br />";
}
echo "</td></tr></table>";

---

I think what you are looking for is referred to a 'concation of subquery'. Check http://forums.mysql.com/read.php?20,157425,157796#msg-157796 and http://mysql.bigresource.com/SELECT-CONCAT-Subquery-S5cIpzqO.html