Complex Query of 4 tables

[horndog]

I have the following code that is confusing the hell out of me. In order to simplify things for me, i just did 4 seperate queries.

Now, what I would like to do is to take the information that I have pulled and be able to pull all records that is in there by the date That I have set to Sort by. Now the issue is that the Exam Fees table stores all the fees from the exams and orders them by ExamID.

Taking the ExamID and matching it up with the ExamID from the Exams table and taking that and listing all the procedures that went with this exam from the procedures table. Get it?

So if you went into the doc's and had your head examined and the doc charged $50 for the visit and $50 for the prozac you will be on, I want it to listed those items(procedures) by examID.

Get it?

So it should look like this:

Exam Date: January 1, 2003
Patient: Joe Blow
Procedures:
Head Exam
Prozac

I am also getting a Undefined Variable; ExamFee error, which is probably a typo

Now for the crazy code:

<?
require('variables.php');
?>

<?  

//Get Exam Table Data

$connect = @mysql_connect($dbserver, $dbuser, $dbpass) or die("Could not Connect to Server: Query 1");
$db = @mysql_select_db($dbname, $connect) or die("could not select DB");
$sql = "SELECT ExamID, PatientID, ExamDate FROM $exams WHERE ExamDate >= Date_Sub(CURDATE(), INTERVAL 8 MONTH) ORDER BY ExamDate";
$result = @mysql_query($sql, $connect) or die("Could not Execute Query #1");

While ($row = mysql_fetch_array($result)) {
$ExamID = $row['ExamID'];
$PatientID = $row['PatientID'];
$ExamDate = $row['ExamDate'];
}
?>

<?
// Get Exam Fees Table Data

$connect = @mysql_connect($dbserver, $dbuser, $dbpass) or die("Could not Connect to Server Query 2");
$db = @mysql_select_db($dbname, $connect) or die("could not select DB");
$sql = "SELECT * FROM $examfees WHERE ExamID = \"$ExamID\" ";
$result = @mysql_query($sql, $connect) or die("Could not Execute Query #2");

While ($row = mysql_fetch_array($result)) {
$ExamID2 = $row['ExamID'];
$ProcedureID = $row['ProcedureID'];
$ExamFee = $row['ExamFee'];
}
?>

<?

// Get Procedure List
$connect = @mysql_connect($dbserver, $dbuser, $dbpass) or die("Could not Connect to Server: Query 3");
$db = @mysql_select_db($dbname, $connect) or die("could not select DB");
$sql = "SELECT * FROM procedure_tbl ";
$result = @mysql_query($sql, $connect) or die("Could not Execute Query #3");

While ($row = mysql_fetch_array($result)) {
$ProcedureID2 = $row['ProcedureID'];
$Procedure = $row['ProcedureName'];
$ProcedureFee = $row['ProcedureFee'];
}
?>

<?
// Get Patient Data
$connect = @mysql_connect($dbserver, $dbuser, $dbpass) or die("Could not Connect to Server: Query 4");
$db = @mysql_select_db($dbname, $connect) or die("could not select DB");
$sql = "SELECT PatientID, PatientFirstName, PatientLastName FROM patients WHERE PatientID = \"$PatientID\" ";
$result = @mysql_query($sql, $connect) or die("Could not Execute Query #4");

While ($row = mysql_fetch_array($result)) {
$PatientID2 = $row['PatientID'];
$PatientFirstName = $row['PatientFirstName'];
$PatientLastName = $row['PatientLastName'];
}
?>
<? echo "$ExamDate"; ?><br>
<? echo "$PatientLastName, $PatientFirstName"; ?><br>
<? echo "$ProcedureID2"; ?><br>
<? echo "$ExamFee"; ?>

[nnichols]

It's a bit late in the evening for this stuff but I'll have a go if you post with a clearer explanation.

Give the structure of the four tables (column_names, sample data) and the relationship between them (one-to-many,many-to-many, etc.)

Hope I can help

Nick

[horndog]

well, is there a way to find that out what relationships are being used?

I didn't build the database, just going to run queries against it

[nnichols]

no you can't check for relationships but you should be able to figure it out through your application logic.

Will row1 in tableA relate to one or more rows in tableB?
and so on ...

[horndog]

ok, assuming that I did this right table.column_name

exam.ExamID = examfee.ExamID
examfee.ProcedureID = procedure_tbl.ProcedureID
exam.PatientID = patients.PatientID

So it would be many to many relationships (duh)

[nnichols]

Try this -

SELECT x.ExamDate, pa.PatientFirstName, pa.PatientLastName, xf.ExamFee, pr.ProcedureName, pr.ProcedureFee
FROM exam AS x, examfee AS xf, procedure AS pr, patients AS pa

WHERE x.ExamID = examfee.ExamID 
AND xf.ProcedureID = pr.ProcedureID 
AND x.PatientID = pa.PatientID
AND x.ExamDate >= Date_Sub(CURDATE(), INTERVAL 8 MONTH)

ORDER BY ExamDate

This will give a lot of repeated data, because when it returns 2 procedures for the same exam it has to show all of the other relevant data.

Give it a try and see what happens. I haven't set up tables to test it against so I'm not quite sure.

Good luck!

P.S. If this is no use try the mySQL forum on devshed.com - the guys there are shit-hot

[horndog]

thanks I will give it a whirl

I was getting data back from my long version, but why was I only getting one result? I should of gotten more? Its been a while, I am abit rusty

[scbmx]

you can shorten the code in that script alot by using mysql_pconnect and opening a persistent connection to you db instead of connecting over and over for each table...just a tip

[Weedpacket]

Originally posted by scbmx
you can shorten the code in that script alot by using mysql_pconnect and opening a persistent connection to you db instead of connecting over and over for each table...just a tip

Don't even need to use mysql_pconnect - just drop all but the first mysql_connect. Once you're connected you stay connected until either you say otherwise or the script ends.

[planetsim]

not sure how true this is.. but doesnt mysql_pconnect(); put stress on Mysql.. Cos theres numerous of persistant connections being made..

[nnichols]

not sure how true this is.. but doesnt mysql_pconnect(); put stress on Mysql.. Cos theres numerous of persistant connections being made..

I don't think you're right about that plantsim.

The idea of using persistent connections is that you establish a connection and reuse it instead of constantly opening and closing connections. I don't know how efficient PHPs handling of persistent connections is, but it has got to be better than using non-persistent connections, especially if you're not closing them.

[Weedpacket]

Originally posted by nnichols
I don't know how efficient PHPs handling of persistent connections is, but it has got to be better than using non-persistent connections, especially if you're not closing them.

Not necessarily

[horndog]

ok, one step at a time, I am a bit of a rookie at this stuff...

So what am I doing wrong to only returning one row? Why am I not returning everything the query is stating for that time frame?

[horndog]

Hmm... Now I get undefined variables: ExamDate, ExamID, ProcedureID

Here is the new shorter code:

I am just trying to echo it back just to see if it works

<?  

//Get Exam Table Data

$connect = @mysql_connect($dbserver, $dbuser, $dbpass) or die("Could not Connect to Server: Query 1");
$db = @mysql_select_db($dbname, $connect) or die("could not select DB");
$sql = "SELECT x.ExamDate, pa.PatientFirstName, pa.PatientLastName, xf.ExamFee, pr.ProcedureName, pr.ProcedureFee 
FROM exams AS x, examfees AS xf, procedure_tbl AS pr, patients AS pa 
WHERE x.ExamID = xf.ExamID AND xf.ProcedureID = pr.ProcedureID AND x.PatientID = pa.PatientID 
AND x.ExamDate >= Date_Sub(CURDATE(), INTERVAL 1 DAY) ORDER BY ExamDate";

$result = @mysql_query($sql, $connect) or die("Could not Execute Query #1");
While ($row = mysql_fetch_array($result)) {
$ExamDate = $row['ExamDate'];
$ExamID = $row['ExamID'];
$ProcedureID = $row['ProcedureID'];


}
?>

<? echo "$ExamDate"; ?>
<? echo "$ExamID"; ?>
<? echo "$ProcedureID"; ?>

[nnichols]

In your first code block you do this -

While ($row = mysql_fetch_array($result)) {
$ExamID = $row['ExamID'];
$PatientID = $row['PatientID'];
$ExamDate = $row['ExamDate'];
}

After your while loop has finished you are left with your three vars having the final values returned from the query.

You then run your second query based on this single result.

If you want to do it this way you need to encapsulate the next three code blocks in functions. So you get all the iterations of the sequential queries.

<?  

//Get Exam Table Data

$connect = @mysql_connect($dbserver, $dbuser, $dbpass) or die("Could not Connect to Server: Query 1");
$db = @mysql_select_db($dbname, $connect) or die("could not select DB");
$sql = "SELECT ExamID, PatientID, ExamDate FROM $exams WHERE ExamDate >= Date_Sub(CURDATE(), INTERVAL 8 MONTH) ORDER BY ExamDate";
$result = @mysql_query($sql, $connect) or die("Could not Execute Query #1");

While ($row = mysql_fetch_array($result)) {
$ExamID = $row['ExamID'];
$PatientID = $row['PatientID'];
$ExamDate = $row['ExamDate'];
get_exam_fees($ExamID);
}
?>

<?
// Get Exam Fees Table Data
function get_exam_fees($ExamID) {
$connect = @mysql_connect($dbserver, $dbuser, $dbpass) or die("Could not Connect to Server Query 2");
$db = @mysql_select_db($dbname, $connect) or die("could not select DB");
$sql = "SELECT * FROM $examfees WHERE ExamID = \"$ExamID\" ";
$result = @mysql_query($sql, $connect) or die("Could not Execute Query #2");

While ($row = mysql_fetch_array($result)) {
$ExamID2 = $row['ExamID'];
$ProcedureID = $row['ProcedureID'];
$ExamFee = $row['ExamFee'];
}
}
?>

Hope you can understand what I'm getting at here.

Good luck!

[nnichols]

Sorry about that. I was answering your previous question and you then you posted another.

Try this and see how you go -

<?  

//Get Exam Table Data

$connect = @mysql_pconnect($dbserver, $dbuser, $dbpass) or die("Could not Connect to Server: Query 1");
$db = @mysql_select_db($dbname, $connect) or die("could not select DB");
$sql = "SELECT x.ExamDate, pa.PatientFirstName, pa.PatientLastName, xf.ExamFee, pr.ProcedureName, pr.ProcedureFee 
FROM exams AS x, examfees AS xf, procedure_tbl AS pr, patients AS pa 
WHERE x.ExamID = xf.ExamID AND xf.ProcedureID = pr.ProcedureID AND x.PatientID = pa.PatientID 
AND x.ExamDate >= Date_Sub(CURDATE(), INTERVAL 1 DAY) ORDER BY ExamDate";

$result = @mysql_query($sql, $connect) or die("Could not Execute Query #1");
While ($row = mysql_fetch_array($result)) {
$ExamDate = $row['ExamDate'];
$ExamID = $row['ExamID'];
$ProcedureID = $row['ProcedureID'];

echo 'ExamDate: ' . $ExamDate;
echo 'ExamID: ' . $ExamID;
echo 'ProcedureID: ' . $ProcedureID . '<br><hr>';
}
?>

You need to include your echo statements within the while loop as the query is returning multiple rows.

Hope this helps 🙂

[horndog]

I see what you are doing.... What about the code you suggested ?

[horndog]

DOH!! sorry about that!

[horndog]

Ok, getting closer!

Now I am getting these:

Notice: Undefined index: ExamID in E:\Program Files\Apache Group\Apache2\htdocs\reports\index.php on line 19

Notice: Undefined index: ProcedureID in E:\Program Files\Apache Group\Apache2\htdocs\reports\index.php on line 20

Lines 19 & 20

$ExamID = $row['ExamID'];
$ProcedureID = $row['ProcedureID'];

[nnichols]

My fault - I forgot to ad those columns to the select -

<?  

//Get Exam Table Data

$connect = @mysql_pconnect($dbserver, $dbuser, $dbpass) or die("Could not Connect to Server: Query 1");
$db = @mysql_select_db($dbname, $connect) or die("could not select DB");
$sql = "SELECT x.ExamDate, x.ExamID, pa.PatientFirstName, pa.PatientLastName, xf.ExamFee, pr.ProcedureID, pr.ProcedureName, pr.ProcedureFee 
FROM exams AS x, examfees AS xf, procedure_tbl AS pr, patients AS pa 
WHERE x.ExamID = xf.ExamID AND xf.ProcedureID = pr.ProcedureID AND x.PatientID = pa.PatientID 
AND x.ExamDate >= Date_Sub(CURDATE(), INTERVAL 1 DAY) ORDER BY ExamDate";

$result = @mysql_query($sql, $connect) or die("Could not Execute Query #1");
While ($row = mysql_fetch_array($result)) {
$ExamDate = $row['ExamDate'];
$ExamID = $row['ExamID'];
$ProcedureID = $row['ProcedureID'];

echo 'ExamDate: ' . $ExamDate;
echo 'ExamID: ' . $ExamID;
echo 'ProcedureID: ' . $ProcedureID . '<br><hr>';
}
?>

Let's give it another try and see what happens :p

You should download a copy of MySQL-Front or similar tool for ease of testing your queries against your db. The project has been discontinued but if you try here there are links I think.