Table structure

[jeff5656]

I have two tables:

 

CREATE TABLE `dos` (
  `dos_id` int(254) NOT NULL auto_increment,
  `pt_id` int(254) NOT NULL,
  `billing_date` date NOT NULL,
  `billing_lvl` enum('hpl','hpm','hph','dc1','cc2') NOT NULL,
  `diagnosis` text NOT NULL,
  `staff` text NOT NULL,
  PRIMARY KEY  (`dos_id`)
  
  CREATE TABLE `icu` (
  `id_incr` int(254) NOT NULL auto_increment,
  `rm_loc` varchar(20) NOT NULL,
  `patient` varchar(20) NOT NULL,
  `mrn` varchar(20) NOT NULL,
  `age` varchar(20) NOT NULL,
  `gender` enum('m','f') NOT NULL,
  PRIMARY KEY  (`id_incr`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

 

The patient can have more than 1 diagnosis for each date (billing_date), but there is only one billing_lvl for each date.  The way I have it is you get a separate roww for each diagnosis and it echos the billing lvl at each row, even though it's the same.  So if I have 3 diagnoses, there will be 3 rows.

I want to have all 3 diagnoses on the same row for that date.  It sounds like I need a 3rd table called diagnosis? If so how do I do that.  Here;s the what prints out after joining the 2 tables:

 

include "connectdb.php"; 

switch ($_GET['action']) {
case "edit":
$consultsq1 = "SELECT * FROM icu INNER JOIN dos ON icu.id_incr = dos.pt_id AND icu.id_incr = '" . $_GET['id'] . "' order by dos.billing_date"; 

$result = mysql_query ($consultsq1) or die ("Invalid query: " . mysql_error ());
//$row = mysql_fetch_array ($result);
//echo "Billing data for: " . $row['patient'] . "<br>";
?><form>
<?php	
while ($row = mysql_fetch_assoc ($result)) {	
echo $row['billing_date'] . " ";
?><input name="billing" type="text" id="billing" size="10" value="<?php echo $row['billing_lvl']; ?>"/>
    <input name="billing" type="text" id="billing" size="10" value="<?php echo $row['dx']; ?>"/>
<input type="hidden" name ="dos_id" value="<?php echo $row['dos_id']; ?>"/>
    <input type="submit" value="Update billing">
<br>
<?php

}

[premiso]

You would need the third table to have a normalized database.

 

To create the table you need to find what you need in the diagnoses table. You will at least need a diagnosesid, dos_id (to link to the billing table), diagnosis and any other information you need for a diagnoses.

 

Then you just link the billing table to the diagnoses table with a query like:

 

$sql = "SELECT * FROM dos, diagnosis WHERE dos.dos_id = diagnosis.dos_id AND dos.pt_id =  '" . $_GET['id'] . "' order by dos.billing_date";

 

 

[jeff5656]

Ok I want all the diagnosis for the same date to be on one line.

But they are on a seaprate line.

 

Here are my tables now:

 

CREATE TABLE `diagnosis` (
  `dx_id` int(254) NOT NULL auto_increment,
  `dos_id` int(254) NOT NULL,
  `diagnosis` varchar(50) NOT NULL,
  PRIMARY KEY  (`dx_id`)

CREATE TABLE `dos` (
  `dos_id` int(254) NOT NULL auto_increment,
  `pt_id` int(254) NOT NULL,
  `billing_date` date NOT NULL,
  `billing_lvl` enum('hpl','hpm','hph','dc1','cc2') NOT NULL,
  `diagnosis` text NOT NULL,
  `staff` text NOT NULL,
  PRIMARY KEY  (`dos_id`)
  
  CREATE TABLE `icu` (
  `id_incr` int(254) NOT NULL auto_increment,
  `rm_loc` varchar(20) NOT NULL,
  `patient` varchar(20) NOT NULL,
  `mrn` varchar(20) NOT NULL,
  `age` varchar(20) NOT NULL,
  `gender` enum('m','f') NOT NULL,
  PRIMARY KEY  (`id_incr`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

 

Here is code:

 

	$sql = "SELECT * FROM dos, diagnosis, icu WHERE dos.dos_id = diagnosis.dos_id AND icu.id_incr = '" . $_GET['id'] . "' AND dos.pt_id =  '" . $_GET['id'] . "' order by dos.billing_date";
$result = mysql_query ($sql) or die ("Invalid query: " . mysql_error ());
echo "<table>";
while ($row = mysql_fetch_assoc ($result)) {	

	echo "<td> pt_id: " . $row['pt_id'] ."</td>";
	echo "<td>" . $row['patient'] . " " ."</td>";
	echo "<td>" . $row['billing_date'] ."</td>";
	echo "<td>diagnosis:  " . $row['diagnosis'] ."</td>";
	echo "<tr>";
	}
echo "</table>";

 

But here is output:

 

pt_id: 8 boooks, jeffrey 2008-12-09 diagnosis:  Acute Renal Failure

pt_id: 8 boooks, jeffrey 2008-12-10 diagnosis:  COPD

pt_id: 8 boooks, jeffrey 2008-12-10 diagnosis:  severe sepsis

pt_id: 8 boooks, jeffrey 2008-12-10 diagnosis:  shock

 

 

see how there are more than one diagnosis for the same date?  I want this:

 

pt_id: 8 boooks, jeffrey 2008-12-09 diagnosis:  Acute Renal Failure

pt_id: 8 boooks, jeffrey 2008-12-10 diagnosis:  COPD, severe sepsis, shock

 

 

 

[premiso]

   $sql = "SELECT * FROM dos, diagnosis, icu WHERE dos.dos_id = diagnosis.dos_id AND icu.id_incr = '" . $_GET['id'] . "' AND dos.pt_id =  '" . $_GET['id'] . "' order by dos.billing_date";
   $result = mysql_query ($sql) or die ("Invalid query: " . mysql_error ());
   echo "<table>";
$i=0;
   while ($row = mysql_fetch_assoc ($result)) {   
      if ($row['billing_date'] != $oldDate) {
           if ($i != 0) {
               echo '</tr>';
              $i++;
           }
           echo '<tr>';
      }
      echo "<td> pt_id: " . $row['pt_id'] ."</td>";
      echo "<td>" . $row['patient'] . " " ."</td>";
      echo "<td>" . $row['billing_date'] ."</td>";
      echo "<td>diagnosis:  " . $row['diagnosis'] ."</td>";
      $oldDate = $row['billing_date'];
      }
echo "</table>";

[jeff5656]

Thank you!  Now we are getting closer (and I'm *starting* to learn:)

 

Here's the output from your code:

pt_id: 8	boooks, jeffrey 	2008-12-09	diagnosis:  Acute Renal Failure
pt_id: 8	boooks, jeffrey 	2008-12-10	diagnosis:  Hypercapneic respiratory failure	pt_id: 8	boooks, jeffrey 	2008-12-10	diagnosis:  COPD	pt_id: 8	boooks, jeffrey 	2008-12-10	diagnosis:  severe sepsis	pt_id: 8	boooks, jeffrey 	2008-12-10	diagnosis:  shoc

because of the <td>'s.  How do I have it so each row only displays patient info once and then the diagnoses followed by commas:

 

pt_id: 8   boooks, jeffrey    2008-12-09   diagnosis:  Acute Renal Failure
pt_id: 8   boooks, jeffrey    2008-12-10   diagnosis:  COPD, severe sepsis, shock

 

 

[premiso]

   while ($row = mysql_fetch_assoc ($result)) {   
      if ($row['billing_date'] != $oldDate) {
           if ($i != 0) {
               echo implode(", ", $diagnos) . '</td></tr>';
              $i++;
           }
           echo '<tr>';
           echo "<td> pt_id: " . $row['pt_id'] ."</td>";
           echo "<td>" . $row['patient'] . " " ."</td>";
           echo "<td>" . $row['billing_date'] ."</td>";
           echo "<td>";
           $diagnos = array();
      }
      $diagnos[] = $row['diagnosis'];
      $oldDate = $row['billing_date'];
      }
echo implode(", ", $diagnos) . "</td></tr></table>";

 

Not the cleanest, but it will work.

[jeff5656]

I *knew* the answer had soemthing to do with using an array but I wasn't sure how.

I have another question, but will post it as new since it's a different issue, and mark this as solved.

[jeff5656]

Actually I spoke too soon - only that LAST record gets the diagnoses displayed.

 

The first records do not:

 

boooks, jeffrey 	consm 	2008-12-09	
boooks, jeffrey 	hph 	2008-12-10	
boooks, jeffrey 	consl 	2008-12-11	cardiogenic shock, emphysema, renal failure

[premiso]

<?php
   $sql = "SELECT * FROM dos, diagnosis, icu WHERE dos.dos_id = diagnosis.dos_id AND icu.id_incr = '" . $_GET['id'] . "' AND dos.pt_id =  '" . $_GET['id'] . "' order by dos_id, diagnosis.billing_date"; // changed this.
   $result = mysql_query($sql) or die ("Invalid query: " . mysql_error ());
   
   echo "<table>";
   $i=0;
   while ($row = mysql_fetch_assoc ($result)) {   
      if ($row['billing_date'] != $oldDate) {
           if ($i != 0) {
               echo implode(", ", $diagnos) . '</td></tr>';
           }
           echo '<tr>';
           echo "<td> pt_id: " . $row['pt_id'] ."</td>";
           echo "<td>" . $row['patient'] . " " ."</td>";
           echo "<td>" . $row['billing_date'] ."</td>";
           echo "<td>";
           $diagnos = array();
           $i++; // moved this down here cause it would have never incremented.
      }
      $diagnos[] = $row['diagnosis'];
      $oldDate = $row['billing_date'];	  
}
echo implode(", ", $diagnos) . "</td></tr></table>";
?>

[jeff5656]

Works!

You guys are very very smart - I get headaches just trying to get these things to work for hours and you reply in a few minutes.