select statement help

[VickP07]

Hey guys so i have a page where i am creating a table to show all patient data
the select statement is working and the correct data is being displayed buttttttttttt i am seeing that it is repeating some patient data more than once.

Here is how my table looks:
Medical Record | First Name | Last Name |Medication Name| Dose | Unit Of Measurement | Appointment Description | Visit Notes |

So then it should display data like this:
111-1212AB | Chad | Smith | Allegra | 15 | mg | patient will return next week | patient's blood was sent to lab|

111-1212AB | Chad | Smith | Zyrtec| 15 | mg | appointment desc goes here | patient's blood was sent to lab again|

but i am seeing that some patients repeat twice so the above example will be displayed 4 times making it seem like that patient has been assigned 4 individual medications and appointment desc and visit notes

here is my sql statement:

$sql2 ="
SELECT patients.med_rec, patients.fname, patients.lname, medications.med_name, prescriptions.dose, UOM.name, appointments.descr, notes.visit_note
FROM prescriptions, patients, medications, UOM, appointments, notes
WHERE prescriptions.pat_id = patients.patient_id
AND medications.medication_id = prescriptions.med_id
AND UOM.unit_id = prescriptions.uom_id
AND appointments.pat_id = patients.patient_id
AND notes.pat_id = patients.patient_id;
";
here is where i create my table displaying all the patient data:

<table border="2" CELLPADDING="2" CELLSPACING="5" width ="1000">  
<tr>
<th>Medical Record</th>
<th>First Name</th>
<th>Last Name</th>
<th>Medication Name</th>
<th>Dose</th>
<th>Unit Of Measurement</th>
<th>Appointment Description</th>
<th>Visit Notes</th>
</tr>
 
<? 
#test if select statement works
$result2 = mysql_query( $sql2 );
$result2 or die("My query ($sql2) failed." );	

//echo "<hr>DEBUG SQL: " . $sql2 . "<hr/>\n";

#While statement used to pass data from DB into tables

while( $row = mysql_fetch_array( $result2 ) ) { ?>
<br />
<tr>
<td><?=$row['med_rec']?></td>
<td><?=$row['fname']?></td>
<td><?=$row['lname']?></td>
<td><?=$row['med_name']?></td>
<td><?=$row['dose']?></td>
<td><?=$row['name']?></td>
<td><?=$row['descr']?></td>
<td><?=$row['visit_note']?></td>
</tr>
<? } ?>

</table>

[Old Pedant]

Yes? Why are you surprised?

If you have 2 medications and 2 visit notes, now is MySQL supposed to know which note goes with which medication? So, instead, it combines each note with each medication. And then if you add in 2 appointments, you would actually expect to see *EIGHT* rows. Every possible combination of the 3 pairs of values.

It's called the Cartesian Product and it's the root basis of all relational database queries. Only by the appropriate WHERE clauses do you limit this action.

If you looked more closely at your results, I'll bet you actually saw this (showing only the relevant fields, and color coding them to show the independent values):

111-1212AB | Allegra | patient's blood was sent to lab      |
111-1212AB | Allegra | patient's blood was sent to lab again|
111-1212AB | Zyrtec  | patient's blood was sent to lab      | 
111-1212AB | Zyrtec  | patient's blood was sent to lab again|

But for that matter, given your table layout, how do *YOU* know which VisitNote goes with which Medication?

Maybe the root problem is that you don't have a way of tying all visit notes and all prescriptions to as single appointment?

But the first thing you have to do is figure out how, indeed, you would display the information if, say, on a single visit of a single patient, 5 medications were prescribed, 4 notes were made about the visit, and 2 future appointments were made.

There is simply NO REASONABLE WAY to show that information in a "spreadsheet" style of display, is there?

So figure out your display format, first, then we can figure out what else to do.

I strongly suspect you are going to want to change entirely how you are doing this and make at least 2 and more likely 3 and possibly 4 separate SQL queries. But we won't know until you figure out what the report should look like.