PHP MySQL Select Query

[The Mog]

Hi All,

I have a quick brain teaser thats had me going for a few days and i can't seem ti figure out the best way of achiving my desired result...

Basically i have a DB holding some records (Quite a few actually)

one of the fields is a unix epoch timestamp,

what i would like to do is the following.....

scan through the records between 2 dates (start_date ; End date)

then (heres the biggie) if the timestamp difference between 2 records is more that say 900 seconds it should start a new trip ie:

Trip 1 = start_date -> end_date;
Trip 2 = start_date2 -> end_date2;
etc

I know this is pretty unclear but i am sure you will understand what i am trying to say

if the difference between end_date and Start_date2 is More than 900 then it should be on its own, if it is less than 900 then it will go in the first group ie. Trip 1

so if i run a query for 15/06/07 then it might show 7 different trips.

trip1 = 12h00 -> 12h55
trip2 = 14h23 -> 15h12
etc.

I hope someone can point me in the right direction

Thanks

Kenny

[wheeler]

to be honest, no I don't really follow what you mean... do you have some code you can paste? Also, are you trying to achieve everything in the mysql query, or can you generalise the mysql and then filter it with php?

[The Mog]

Hi wheeler,

Thanks for your reply.....

ok here goes let me try to explain this better.....

I have a Db that holds lots of records.

each record has a unix epoch timestamp.
the records are usually 60 seconds different

so i would like to run a query say somthinge like:

SELECT * FROM table WHERE date_logged BETWEEN 12/06/07 00:00:00 AND 12/06/07 23:59:59 ORDER BY date_logged ASC
 
//the timestamp will be in unix format i have done it this way just for demonstration purposes // 

this might produce 200 results, then i would somehow, either PHP / MySQL query (whatever would be the easiest) to do the following...

display the following links:

Trip 1 = 12/06/07 00:00:00 to 12/06/07 01:23:50
Trip 2 = 12/06/07 03:43:30 to 12/06/07 06:55:10

if i run then a query between the above times it might then only return 20 results.

but somehow i need to be able to distinguish between the multiple trips, so if there is a gap of more than 900 seconds, between any 2 records i know that is the end of trip 1 and the start of trip 2.

Please let me know if this is any clearer

Thanks again
Kenny

[wheeler]

yes it is a bit clearer but I don't have any solutions unfortunately... all I can really suggest is:
a) loop through the results in php and test each one to see if the gap is > 900 seconds
b) ultimately, its best to get the right results to start with... head to the mysql forums and put it to those guys - some of them are absolute freaks at solving problems just like this. you may not understand the query they provide for a long time, but hey, it works

[The Mog]

thanks

[The Mog]

Hi,Do you have any Idea on how to do this with PHP?ThanksK-