SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict The Mog's Avatar
    Join Date
    Dec 2002
    Location
    Manchester UK
    Posts
    310
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PHP MySQL Select Query

    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

  2. #2
    SitePoint Wizard wheeler's Avatar
    Join Date
    Mar 2006
    Location
    Gold Coast, Australia
    Posts
    1,369
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?
    Studiotime - Time Management for Web Developers
    to-do's, messages, invoicing, reporting - 30 day free trial!
    Thomas Multimedia Web Development

  3. #3
    SitePoint Addict The Mog's Avatar
    Join Date
    Dec 2002
    Location
    Manchester UK
    Posts
    310
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    PHP Code:
    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

  4. #4
    SitePoint Wizard wheeler's Avatar
    Join Date
    Mar 2006
    Location
    Gold Coast, Australia
    Posts
    1,369
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Studiotime - Time Management for Web Developers
    to-do's, messages, invoicing, reporting - 30 day free trial!
    Thomas Multimedia Web Development

  5. #5
    SitePoint Addict The Mog's Avatar
    Join Date
    Dec 2002
    Location
    Manchester UK
    Posts
    310
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks

  6. #6
    SitePoint Addict The Mog's Avatar
    Join Date
    Dec 2002
    Location
    Manchester UK
    Posts
    310
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,Do you have any Idea on how to do this with PHP?ThanksK-


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •