Multiple tables?

horndog

I have some tables that I need to pull data out of for a query and I am kind of at a lost.

Here is my tables and what I need pulled out of them:

patient_table

PatientID
PatientLastName
PatientFirstName

exams_table

examID
patientID
examDate

examFees_table

ExamID
ProcedureID
ProcedureName
ExamFee

Procedure_table

ProcedureID
ProcedureName
ProcedureFee

Now one of the issues is that the Procedure_table holds the exam procedures and the ID which is stored in the examFees table.

For example:

ProcedureID = 0 ProcedureName = ExamTotal ProcedureFee = $85.00

Ultimatley I would like to view by patients that visited in one day

So, I would display them like this:

PatientLastName, PatientFirstName
Procedure 1
Procedure 2
etc
Exam Total

And would like them ordered by date

need more info? I am just trying to get it where I can get it to query 4 tables, is there a way to do this?

PyroX

See: http://www.google.com/search?hl=en&lr=&ie=UTF-8&oe=UTF-8&q=php+mysql+query+multiple+tables&spell=1

And Read: http://www.mysql.com/doc/en/Multiple_tables.html

horndog

that only says two tables I have four

PyroX

I am not going to hold you hand, if you cannot fegure out the query syntax from this page: http://www.mysql.com/doc/en/Multiple_tables.html then your out of luck.

horndog

i was looking for an example, I didn't ask for a hand holding.

PyroX

The example is on that page, see this:

SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species FROM pet AS p1, pet AS p2 WHERE p1.species = p2.species AND p1.sex = "f" AND p2.sex = "m";

Change it to fit your needs:

SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species, p3.hair, p4.eyes FROM pet AS p1, pet2 AS p2, body AS p3, head AS p4 WHERE p1.id = p2.id AND p3.id=p1.id AND p4.id=p1.id AND p1.sex = "f" AND p2.sex = "m";

horndog

thank you, I will try that version... I will post my current trial.

I dumped the queries in a while loop

horndog

here is my code... In the while loop. Two problems, only returns one record, and The ExamDate field is DATETIME datatype and I am trying to figure out how to extract that in a legibal format

$db = @mysql_select_db("Brent", $connection) or die("Couldn't select database.");

$sql_exam = "SELECT ExamID, PatientID, ExamDate FROM $exams ORDER BY ExamDate ";

$result_exam = @($sql_exam, $connection) or die("Couldn't execute query1.");

while ($row = mysql_fetch_array($result_exam)) {
$ID = $row['ExamID'];
$PatientID = $row['PatientID'];
$ExamDate = $row['ExamDate'];
}

$sql_patients = "SELECT PatientID, PatientFirstName, PatientLastName FROM $patients ";

$result_patients = @($sql_patients, $connection) or die("Couldn't execute query2.");
while ($row = mysql_fetch_array($result_patients)) {
$PatientID = $row['PatientID'];
$PatientFirstName = $row['PatientFirstName'];
$PatientLastName = $row['PatientLastName'];

}

$sql_examfees = "SELECT ExamID, ProcedureID, ExamFee FROM examfees ORDER BY ExamID";
$result_examfees = @($sql_examfees, $connection) or die("Couldn't execute query3.");
while ($row = mysql_fetch_array($result_examfees)) {
$ExamID = $row['ExamID'];
$ProcedureID = $row['ProcedureID'];
$ExamFee = $row['ExamFee'];
}

Echo $ExamDate, $PatientLastName, $PatientFirstName, $ExamFee;
?>

Krush019

You need to build the results into an array. Right now your over writing your vars (ID, PatientID, etc) everytime your while loops.
So you something like this i think:

$i = 0;
while ($row = mysql_fetch_array($result_exam)) {
$array[$i]['ID'] = $row['ExamID'];
$array[$i]['PatientID'] = $row['PatientID'];
$array[$i]['ExamDate'] = $row['ExamDate'];
$i++;}

that would build you results into an array row by row.

horndog

You will have to excuse my stupidity, but I have been not using PHP in about a year.... I get an "Undefined Index of ExamDate, and I cannot figure out why

$db = @mysql_select_db("Brent", $connection) or die("Couldn't select database.");

$sql_exam = "SELECT exams.ExamID, exams.PatientID, DATE_FORMAT(exams.ExamDate, '%W %M %D, %Y')AS Joined FROM exams LIMIT 10" ;

$result_exam = @($sql_exam, $connection) or die("Couldn't execute query1.");

$i = 0;
while ($row = mysql_fetch_array($result_exam)) {
$array[$i]['ExamID'] = $row['ExamID'];
$array[$i]['PatientID'] = $row['PatientID'];
$array[$i]['ExamDate'] = $row['ExamDate'];
$i++;}

$sql_patients = "SELECT PatientID, PatientFirstName, PatientLastName FROM $patients ";

$result_patients = @($sql_patients, $connection) or die("Couldn't execute query2.");
$i = 0;
while ($row = mysql_fetch_array($result_patients)) {
$PatientID = $row['PatientID'];
$PatientFirstName = $row['PatientFirstName'];
$PatientLastName = $row['PatientLastName'];

}

Echo $PatientLastName, $PatientFirstName, $ExamFee;