Grouping results (not GROUP BY)

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
enfys
Forum Newbie
Posts: 4
Joined: Thu Nov 18, 2004 4:32 am

Grouping results (not GROUP BY)

Post by enfys »

Hi, I'm really struggling with this one so any help is greatly appreciated.

I have a database which records patient information e.g. blood pressure at various times of the day. The patient logs on and specifies the date and time the reading was taken then enters their reading. I then need to query the database and show the results for each day at the time they were taken. I have created an array which loops through the times (06:00am to 00:00 midnight at 1 hour intervals) and need to show the results against the right time. However, a patient may have take several readings a day or just one a day, so I need to group the results so that all readings for a particular day are shown under the column for that day.

Thanks
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

something like:

Code: Select all

$query = "SELECT *, DAY(datetimefield) AS day FROM foo ORDER BY datetimefiled ASC";
$result = mysql_query($query);


$current_day = '';
while ($row = mysql_fetch_assoc($result)) {
  if ($current_day != $row['day']) {
     echo "new day";
      $current_day = $row['day'];
  } 
   print_r($row);
}
enfys
Forum Newbie
Posts: 4
Joined: Thu Nov 18, 2004 4:32 am

Post by enfys »

Thanks but its a bit more complicated (as always!).

Here's what I've got so far:

Code: Select all

<?php 
include("includes/db_info.php");

mysql_connect($hostname,$username,$password);
mysql_select_db($database) or die( "Unable to select database");

//Get patient data
$query="SELECT date_format(period, '%d %M') as date, date_format(period, '%H:%i') as time, systolic, diastolic FROM conditions_tbl WHERE patient_id = 1 AND systolic >0 AND diastolic >0 ORDER BY period ASC";

$conditions_result=mysql_query($query) or die(mysql_error());

$condition_nums=mysql_num_rows($conditions_result);

mysql_close();

//Create an array for the times of the day 
$time_array=array('06:00', '07:00', '08:00', '09:00', '10:00', '11:00', '12:00', '13:00', '14:00', '15:00', '16:00', '17:00', '18:00', '19:00', '20:00', '21:00', '22:00', '23:00', '00:00');

while($array=mysql_fetch_array($conditions_result)) {
$date=$array['date'];
$time=$array['time'];
$systolic=$array['systolic'];
$diastolic=$array['diastolic'];


//initialise date
$date_chk = '';
$i=0;

for ($i=0; $i<count($time_array); $i++) {

if ($date_chk != $date) {
echo "<table>
		<tr>
			<td>&nbsp;</td>
			<td>$date</td>
		</tr>
	</table>";
$date_chk = $date;
}

echo "
	<table><tr>
		<td>$time_array[$i]</td>
		<td align="center">"; 
			if (in_array($time_array[$i], $time)) {
			echo "Patient BP here";
			}
			echo"
		</td>
	</tr></table>";
$i++;
}
}
?>
This just displays the times of the day for each day but as I said I need it to show all entries for a particular day in one group

Hope this helps
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

What format is the db field "period" stored in? Is it a MySQL date type or a UNIX timestamp?

For stuff like this, I usually find the earliest and latest possible times, and ask for everything between those two times. Make sense?
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply