Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    Sep 2011
    Posts
    78
    Thanks
    15
    Thanked 0 Times in 0 Posts

    select statement help

    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:
    Code:
    <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>

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,166
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    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):
    Code:
    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.
    Last edited by Old Pedant; 12-03-2011 at 03:43 AM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •