PHP-MySQl: Compare records in a table and joints matched results as a single row

[avvllvva]

Hi,

 

I need to compare records in a table based on the 'datetime' field, like if the difference between time is less than 15min.

 

need to combine the records(rows) as a single resultant row until the time difference between them  is less than 15min and when a record's time difference is >15min that should return as another row and so on. Please find following sample table and the required output format

 

Sample Table:

 

ID	NAME	DATETIME 
1   aaa    	2011-10-10 06:30:00
2   bbb    	2011-10-10 06:33:00
3   ccc    	2011-10-10 06:38:00
4   ddd    	2011-10-10 06:40:00
5   eee    	2011-10-10 07:10:00
6   ffff   	2011-10-10 07:14:00
7   sss    	2011-10-10 08:16:00
8   jjj   	2011-10-10 08:26:00
9   kkk   	2011-10-10 08:28:00
10  mm   	2011-10-10 09:46:00
11  ppp   	2011-10-10 09:49:00
12  qqq   	2011-10-10 09:52:00

 

 

Output Needed :

 

IDs			START DATETIME				END DATETIME			 
1,2,3,4 		2011-10-10 06:30:00			2011-10-10 06:40:00
5,6			2011-10-10 07:10:00                      2011-10-10 07:14:00
7,8,9		2011-10-10 08:16:00			2011-10-10 08:28:00
10,11,12		2011-10-10 09:46:00                      2011-10-10 09:52:00

 

You can see 1st row in the output table having IDs 1,2,3,4 coz the time difference between them is less than 15minutes (it doesn't means time difference between start and end; it is actually the  time difference between current record and previous one ; ie; ID-1 and ID-2 have difference less than 15min and ID-2 & ID-3 have difference less than 15min and ID-3 & ID-4 have difference less than 15min, so those 4 records combine together as a single row also shows their start time and end time. Then you can see diff between ID-4 and ID-5 is greater than 15 min so it should display as new row, and so on)

 

How can I acheive above Output with mysql query ??

 

Please help..

 

 

[Psycho]

You can't. Well, maybe you can, but I can't think of any way that makes sense. It would be much, much simpler to simply query all the records and then determine the grouping in PHP.

[asmith]

It is possible to achieve it with MySQL if I got what you want exactly.

So what if a record is in range of 15 mins with previous one AND with the next. BUT the previous and next are not in range of each other? How do you want to have that result?

 

Edit: as mjdamato mentioned, handling the data with php would be much easier though.

[avvllvva]

Thanks guys for your info. how can do it with php?

[Cagecrawler]

<?php
$query = mysql_query("SELECT * FROM table ORDER BY datetime ASC");
$ids = array();
$startTime = null;
while ($row = mysql_fetch_assoc($result)) {
if($startTime == null)
{
	$ids[] = $row['id'];
	$startTime = $row['datetime'];
}
else if(strtotime($row['datetime']) - strtotime($lastRow['datetime']) > 60*15)
{
	//Within 15 mins of the last row.
	$ids[] = $row['id'];
}
else
{
	//Not within 15 mins, so dump the info and start a new row.
	var_dump($ids,$startTime,$lastRow['datetime']);

	$ids = array();
	$startTime = null;
}
$lastRow = $row;
}

Something like this should work.