Jump to content

help with queries

rsammy

By rsammy
in PHP Coding Help

 Share

Recommended Posts

rsammy Regular Member

rsammy

Posted
Posted

i am working with three different tables - scheduler, admitstatus and patient tables.

scheduler table has info on when a patient is scheduled to see a doctor(date, location, patient_id)

admitstatus table has info on patient's admission status(admit_id, patient_id, location, admit_date)

patient table has patient's info(patient_id, full name, dob, sex, and so on)

 

scenario:

 

office staff registeres a patient(new patient - in patient table) and then schedules him for an appointment same day(say, today). this info is stored in scheduler table.

 

doctor sees this patient and admits him. this info is now stored in admitstatus table.

 

office staff runs a report on all the active patients for the day(all those in the scheduler and admitstatus tables). query returns all patients(records) stored in both tables(scheduler and admitstatus). that is - it returns one record from admitstatus table for the admitted patient, as also another record for the same patient from the scheduler table(he was scheduled to see a doctor for that day).

 

here is the code for this:

 

<?PHP

     <td width="644" colspan="3" align="left" valign="top"> <table width="540" border="1" cellspacing="0" cellpadding="0" align="center" bordercolor="#006699">
        <tr>
          <td bgcolor="#006699" height="22" width="536"> <b><font color="#FFFFFF">ACTIVE
            PATIENTS LIST:</font></b> </td>
        </tr>
        <tr>
          <td height="392" width="536"> <table width="521" border="0" cellspacing="0" cellpadding="0">
              <tr>
                <td colspan="4" height="21"> <div align="center"><font color="#000000"><img src="../images/clear.gif" width="1" height="15"><b><font class="inputLbl">Enter
                    Patient Information and click on Search</font></b></font></div></td>
              </tr>
              <tr>
                <td width="15"> </td>
                <td rowspan="3" align="left" valign="top" width="498"> <form method="post" action="AdmitPatsearch.php" name="Form" onSubmit="return validate()">
                    <table width="497" border="0" cellspacing="0" cellpadding="0">
                      <tr>
                        <td height="14" width="232"> </td>
                        <td height="14" width="265"> </td>
                      </tr>
                      <tr>
                        <td height="14" width="232"> </td>
                        <td height="14" width="265"> </td>
                      </tr>
                      <tr>
                        <td height="22"> <div align="right"><font class="inputLbl">First
                            Name:    </font></div></td>
                        <td height="22" width="265"> <input class="txtboxLarge" type="text" name="patfname"  value="<? if (isset ($patfname)){ print ("$patfname"); } ?>" onBlur="javascript:changeCase(document.Form.patfname);" >
                        </td>
                      </tr>
                      <tr>
                        <td height="22"> <div align="right"><font class="inputLbl">Last
                            Name:    </font></div></td>
                        <td height="22" width="265"><font face="Verdana, Arial, Helvetica, sans-serif" size="2">
                          <input class="txtboxLarge" type="text" name="patlname" value="<? if (isset($patlname)){ print ("$patlname"); } ?>" onBlur="javascript:changeCase(document.Form.patlname);" >
                          </font></td>
                      </tr>
                      <tr>
                        <td height="22"><div align="right"><font class="inputLbl">Location:
                              </font></div></td>
                        <td colspan="2" align="left" valign="top"><select name="location" id="location">
					<?PHP

							if (isset($location))
							{
								//print($location);
								print ("<option value='$location' selected>");
							}


								$query_loc="select org_ID, org_name from orgs where org_id!=1 order by org_name";
								$result_loc = mysql_query ($query_loc);
								 if ($result_loc)
								 {
									print ("<option value=\"\"></option>\n");
									while($row_loc = mysql_fetch_array($result_loc))
									{
										if($location == $row_loc["org_name"])
										{
											print ('<option selected value="'.$row_loc["org_name"].'">'.$row_loc["org_name"].'</option>');
										}
										else
										{
										   print ('<option value="'.$row_loc["org_name"].'">'.$row_loc["org_name"].'</option>');
										}
									}
									print ("</select>");
								}

					?>
			     	</td>
			      </tr>
                      <tr>
                        <td height="22"><div align="right"><font class="inputLbl">DOB as (mm/dd/yyyy):   </font></div></td>
                        <td height="22"><input name="patdob" type="text" class="txtboxLarge" id="patdob" value="<? if (isset($patdob)) { print ("$patdob"); } ?>" ></td>
                      </tr>
                      <tr>
                        <td width="283"> <div align="right"><font class="inputLbl">
                            <input type="hidden" name="client_id" value="<? print ("1"); ?>">
                            </font></div></td>
                        <td colspan="2"> </td>
                      </tr>
                      <tr>
                        <td height="46" width="232"> </td>
                        <td height="46" width="265"><font face="Verdana, Arial, Helvetica, sans-serif" size="2">						
                          <input class="sbttn" type="submit" name="Submit" value="Search">
                          </font></td>
                      </tr>
                    </table>
                  </form></td>
                <td width="1"> </td>
                <td width="104"> </td>
              </tr>
              <tr>
                <td width="15"> </td>
                <td width="1"> </td>
                <td width="104"> </td>
              </tr>
              <tr>
                <td width="15" height="146"><img src="../images/clear.gif" width="15" height="8"></td>
                <td width="1" height="146"> </td>
                <td width="104" height="146"><img src="../images/clear.gif" width="15" height="8"></td>
              </tr>
            </table><div align="center">
            <img src="../images/clear.gif" width="15" height="1"> <table width="536" border="0" cellspacing="0" cellpadding="0">
              <tr bgcolor="006699">
                <td width="154"> <div align="center"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="2" color="#FFFFFF">
                    Patient Name </font></b></div></td>
			<td width="130"> <div align="center"><strong><font face="Verdana, Arial, Helvetica, sans-serif" size="2" color="#FFFFFF">Patient
                    #</font></strong></div></td>
                <td width="115"> <div align="center"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="2" color="#FFFFFF">DOB</font></b></div></td>
                <td width="279"> <div align="left"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="2" color="#FFFFFF">Location</font></b></div></td>
           
                <td width="130"> <div align="left"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="2" color="#FFFFFF">Pref Provider</font></b></div></td>
              </tr>
              <?

		if (isset($_POST["patlname"]) OR ($_GET["patlname"]) OR ($_POST["patfname"]) OR ($_GET["patfname"]) OR ($_POST[location]) OR ($_GET[location]) OR ($_POST["physician"]) OR ($_GET["physician"]) OR ($_POST[client_id]) OR ($_GET[client_id])){

							$patlname=trim("$patlname");
							$patfname=trim("$patfname");
							$location=trim("$location");
							$physician_name=trim("$physician_name");
							$clientid=stripslashes("$clientid");

				$today = date("Y-m-d");
				$query1 = "SELECT DISTINCT appt_pat_id, appt_loc as admit_visit_loc, '' as admit_room_no
							FROM scheduler
							WHERE appt_date ='$today'
							UNION
						  SELECT admit_pat_id as appt_pat_id, admit_visit_loc, admit_room_no
							FROM admitstat
							WHERE admit_visit_loc like '%$location%'
							ORDER BY appt_pat_id ASC
							";

		$result=mysql_db_query("$database[dbname]", $query1);

		$num_rows = mysql_num_rows($result);
		$num_results=mysql_num_rows($result);



		$i=1;
		if(!isset($rowNum))
			{
				$rowNum = 0;
			}
			$rowNumHold = $rowNum;
			$count = 1;
			$limit_results = 25;
			if ($num_rows > 0)
			{
				for($q=0;$q<$limit_results && $rowNum < $num_rows;$q++)
				{
					if(mysql_data_seek($result, $rowNum++))
					{
						if ($i++%2)
						{
						print ("<tr  bgcolor=\"#cccccc\">");
						}
						else
						{
						print ("<tr>");
						}

						if ($row = mysql_fetch_array($result))
						{

								$admit_visit_loc=$row["admit_visit_loc"];

								$admit_room_no=$row["admit_room_no"];

								$admit_pat_id=$row["admit_pat_id"];
								$appt_pat_id=$row["appt_pat_id"];
		?>
			      <td width="124"> <div align="left"><font class="plnTxtBl"> <?

			      $patnamequery="SELECT pat_ID, pat_first_name, pat_mid_init, pat_last_name, pat_ssn, DATE_FORMAT(pat_dob, '%m/%d/%Y') as dob,
							 pat_phy_id, pat_client_id
							 from patient
							 where pat_ID='$appt_pat_id'
							 AND pat_last_name like '$patlname%'
							 AND pat_first_name like '$patfname%'
							 AND pat_client_id $clientid";

			      $patnameresult = mysql_query($patnamequery);
			      $rowpatname=mysql_fetch_array($patnameresult);

			      $pat_first_name=$rowpatname["pat_first_name"];
			      $pat_last_name=$rowpatname["pat_last_name"];
			      $pat_dob=$rowpatname["dob"];
			      $pat_ssn=$rowpatname["pat_ssn"];
			      $phy_id=$rowpatname["pat_phy_id"];
			      $client_id=$rowpatname["pat_client_id"];


			      print ("$pat_last_name"); print (", ");  print("$pat_first_name"); ?>
				  </font> </div></td>
			      <?  print ("<td width=\"120\"> <div align=\"center\"><font class=\"plnTxtBl\"> "); print ("$pat_ssn"); ?></font></div></td>
			      <td width="97"> <div align="center"><font class="plnTxtBl"> <? print ("$dob"); ?></font></div></td>
			      <td width="129"><div align="left"><font class="plnTxtBl"> <? print ("$admit_visit_loc");

													   //print "<br>";
													   print (" ");

													   print"Room #: ";

													   print ("$admit_room_no");

													   ?> </font> </div></td>
			      <td width="110"> <div align="left"><font class="plnTxtBl"> <?

																	if ($phy_id != 0)
																	{
																		$phyidquery="SELECT phy_fname, phy_lname FROM phy_det WHERE phy_id='$phy_id'";
																		$resultphyidquery=mysql_db_query("$database[dbname]", $phyidquery);
																		$rowphy_id=mysql_fetch_array($resultphyidquery);

																		$phy_fname=$rowphy_id["phy_fname"];

																		$phy_lname=$rowphy_id["phy_lname"];
																	}
																	else
																	{

																		$phy_fname=" ";
																		$phy_lname=" ";

																	}
															print ("$phy_fname"); print "  "; print("$phy_lname");

															?></font></div></td>
			      </tr>
			      <?

					}

					while($row = mysql_fetch_array($result));

				}

			}

		?>

 

requirement:

 

office staff runs the same report but, does not want to see two records for the same patient coming in from the two tables.

the record from scheduler table shud not be included in this list as the doctor has already seen this patient and he has been admitted.

 

the patient shud info shud not be deleted from scheduler table for historical purposes.

 

can anyone help?

 

Link to comment
https://forums.phpfreaks.com/topic/54477-help-with-queries/
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.