Page 1 of 1
Grouping results (not GROUP BY)
Posted: Thu Nov 18, 2004 4:38 am
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
Posted: Thu Nov 18, 2004 4:53 am
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);
}
Posted: Thu Nov 18, 2004 5:11 am
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> </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
Posted: Thu Nov 18, 2004 2:30 pm
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?