I am trying to write a report that queries a bunch of tables and information and to display it as so.
It works somewhat...
This is the issue. It will display the count of records that meet the search criteria, lets say 4, and only displays 2 records, odd...
Other than that it works fine.. Another issue is that how would you do the same report, but how would you do Year to Date or current month to date, and other reports... any hints?
The code
<?
$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 ed.ExamID, ed.DoctorID, dc.DoctorID, dc.DoctorFirstName, dc.DoctorLastName, x.ExamDate, x.ExamID, pa.PatientFirstName, pa.PatientLastName, xf.ExamFee,
pr.ProcedureID, pr.ProcedureName FROM exams AS x, examfees AS xf, procedure_tbl AS pr, patients AS pa,
examdoctors AS ed, doctors AS dc WHERE x.ExamID = xf.ExamID AND x.ExamID = ed.ExamID AND
xf.ProcedureID = pr.ProcedureID AND x.PatientID = pa.PatientID AND xf.ExamFee!=0 AND ed.DoctorID = dc.DoctorID AND
x.ExamDate >= Date_Sub(CURDATE(), INTERVAL 10 YEAR) ORDER BY ExamDate DESC, ProcedureID DESC";
$result = mysql_query($sql, $connect) or die("Could not Execute Query #1");
$num_rows = mysql_num_rows($result);
echo "<b><u>Exams Performed Today:</u></b> $num_rows";
$ExamTotals = '';
$ExamID = '';
While ($row = mysql_fetch_array($result)) {
$DoctorID = $row['DoctorID'];
$DoctorFirstName = $row['DoctorFirstName'];
$DoctorLastName = $row['DoctorLastName'];
$PrevExamID = $ExamID;
$ExamDate = $row['ExamDate'];
$ExamID = $row['ExamID'];
$ProcedureID = $row['ProcedureID'];
$ProcedureName = $row['ProcedureName'];
$ProcedureFee = $row['ExamFee'];
$PatientFirstName = $row['PatientFirstName'];
$PatientLastName = $row['PatientLastName'];
if ($PrevExamID == $ExamID) {
if ($ProcedureID == 0) {
$ExamTotals += $ProcedureFee;
echo "<br><br><b>$ProcedureName:</b> \$$ProcedureFee";
}
else {
echo "<br><b>$ProcedureName:</b> \$$ProcedureFee";
}
}
else {
echo '<br><hr><b>Patient Name:</b> ' ."$PatientFirstName $PatientLastName";
echo '<br><b>ExamDate:</b> ' . $ExamDate;
echo '<br><b>Doctor:</b> ' . $DoctorLastName;
echo '<br><b>ExamID:</b> ' . "$ExamID<br>";
echo "<br><b><u>Procedures:</U></b><br>";
echo "<br><b>$ProcedureName:</b> \$$ProcedureFee";
}
}
echo "<BR><hr><b>Exam Totals:</b> <b><font color=\"#FF0000\" face=\"Arial, Helvetica, sans-serif \">\$$ExamTotals.00</font></b>";
?>
$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 ed.ExamID, ed.DoctorID, dc.DoctorID, dc.DoctorFirstName, dc.DoctorLastName, x.ExamDate, x.ExamID, pa.PatientFirstName, pa.PatientLastName, xf.ExamFee,
pr.ProcedureID, pr.ProcedureName FROM exams AS x, examfees AS xf, procedure_tbl AS pr, patients AS pa,
examdoctors AS ed, doctors AS dc WHERE x.ExamID = xf.ExamID AND x.ExamID = ed.ExamID AND
xf.ProcedureID = pr.ProcedureID AND x.PatientID = pa.PatientID AND xf.ExamFee!=0 AND ed.DoctorID = dc.DoctorID AND
x.ExamDate >= Date_Sub(CURDATE(), INTERVAL 10 YEAR) ORDER BY ExamDate DESC, ProcedureID DESC";
$result = mysql_query($sql, $connect) or die("Could not Execute Query #1");
$num_rows = mysql_num_rows($result);
echo "<b><u>Exams Performed Today:</u></b> $num_rows";
$ExamTotals = '';
$ExamID = '';
While ($row = mysql_fetch_array($result)) {
$DoctorID = $row['DoctorID'];
$DoctorFirstName = $row['DoctorFirstName'];
$DoctorLastName = $row['DoctorLastName'];
$PrevExamID = $ExamID;
$ExamDate = $row['ExamDate'];
$ExamID = $row['ExamID'];
$ProcedureID = $row['ProcedureID'];
$ProcedureName = $row['ProcedureName'];
$ProcedureFee = $row['ExamFee'];
$PatientFirstName = $row['PatientFirstName'];
$PatientLastName = $row['PatientLastName'];
if ($PrevExamID == $ExamID) {
if ($ProcedureID == 0) {
$ExamTotals += $ProcedureFee;
echo "<br><br><b>$ProcedureName:</b> \$$ProcedureFee";
}
else {
echo "<br><b>$ProcedureName:</b> \$$ProcedureFee";
}
}
else {
echo '<br><hr><b>Patient Name:</b> ' ."$PatientFirstName $PatientLastName";
echo '<br><b>ExamDate:</b> ' . $ExamDate;
echo '<br><b>Doctor:</b> ' . $DoctorLastName;
echo '<br><b>ExamID:</b> ' . "$ExamID<br>";
echo "<br><b><u>Procedures:</U></b><br>";
echo "<br><b>$ProcedureName:</b> \$$ProcedureFee";
}
}
echo "<BR><hr><b>Exam Totals:</b> <b><font color=\"#FF0000\" face=\"Arial, Helvetica, sans-serif \">\$$ExamTotals.00</font></b>";
?>
