View Full Version : select statement help

12-14-2011, 11:00 PM
hey guys okay so here is what i am doing:
i have a page that lists a table of patients that are in the DB if the user clicks on a patients Medical number, which is in the table, then another page is brought up where the user can update prescriptions for this patient.

BUT my problem is this: if a patient has not been assigned a prescription yet the patient is still being displayed since my sql statement says to get all patients in the DB and list them.

I want to do some test if a patient has not been assigned a prescription then dont show them in this table, only show the ones that have.

here is my code that i am using to select all the patients in the DB and put them in the TABLE (in my prescriptions table the table i have a prescription_id (primary) start_date, instructions, dose, pat_id, uom_id, and med_id) so the foreign key pat_id would get the patients id whenever a prescription is added for a certain patient!!!!)


if($role != 1) //test to make sure no other users besides doctors can access this page
header("Location: Login.php");

$sql = "
SELECT patients.med_rec, patients.fname, patients.lname
FROM patients;

#test if select statement works
$result = mysql_query( $sql );
$result or die("My query ($sql) failed." );


Old Pedant
12-14-2011, 11:33 PM
Two ways.

SELECT med_rec, fname, lname
FROM patients
WHERE id IN ( SELECT pat_id FROM prescriptions )

Or ...

SELECT DISTINCT patients.med_rec, patients.fname, patients.lname
FROM patients INNER JOIN prescriptions ON patients.id = prescriptions.pat_id

Old Pedant
12-14-2011, 11:34 PM
NOTE: You have no ORDER BY clause in your SQL, so the order in which the patients are listed is completely arbitrary. Generally, you should never omit the ORDER BY clause, even when the results look right. There is no guarantee that removing some records and adding some won't mess up the ordering if you omit the ORDER BY.

12-14-2011, 11:43 PM
thanks works great! quick question even though it really isn't related to this post at all:

Just wanted to know if this is at all possible. I have php page that create tables through html that show data from my database. I want to know if i added like a print button on the page then get whatever is in the table and sent it to a pdf file to print out for a user. are there any examples that u may know of or is this in anyway remotely possible

Old Pedant
12-15-2011, 01:08 AM
Ummm...if you print from PHP, the printer would have to be co-located with your server. So if the server is in Arizona and the user is in Ohio, that might not be an ideal solution.

If you print from the HTML page, then the printer must be co-located with the user's machine.

So which makes more sense in your situation?

Old Pedant
12-15-2011, 01:09 AM
Oh...but you *could* use PHP to create a PDF file that the user would then download and print. Is that what you meant?