SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot
    Join Date
    Jun 2000
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello again,



    Does someone know how to select the latest record from a table as specified by a UNIX time stamp please (e.g. 2001-02-22 09:36:22)?

    Basically, there may be say 50 records in a table all with timestamps, and what I'd like to do is retrieve the latest (i.e. most recent) record.

    Cheers,

    Jason

  2. #2
    SitePoint Columnist Skunk's Avatar
    Join Date
    Jan 2001
    Location
    Lawrence, Kansas
    Posts
    2,066
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT * FROM tablename ORDER BY date_field DESC limit 0,1
    If that doesn't work then change the DESC for ASC (I always get those two confused). The limit 0,1 will ensure you only get the first field from your query i.e the one with the most recent timestamp.

    Cheers,

    Skunk

  3. #3
    SitePoint Zealot
    Join Date
    Jun 2000
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cheers Skunk,



    Very strange - I was trying that line in phpMyAdmin and it crashed every time - yet from a php script it works just fine.......do you know why this is please?


    Later,

    Jason

  4. #4
    SitePoint Columnist Skunk's Avatar
    Join Date
    Jan 2001
    Location
    Lawrence, Kansas
    Posts
    2,066
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't know for sure - but at a guess...

    phpMyAdmin automatically adds it's own limit command on the the end of every query it processes to ensure no more than 40 results are returned (it then provides 'next page' links to view more). As a result when you put the query through phpMyAdmin it would actually be sent to mySQL as:
    Code:
    SELECT * FROM tablename ORDER BY date_field DESC limit 0,1 limit 0,40
    Chances are mySQL didn't like that and spat out an error message.

  5. #5
    AdSpeed.com Son Nguyen's Avatar
    Join Date
    Aug 2000
    Location
    Silicon Valley
    Posts
    2,241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think you're right Skunk, just remove the limit from your query temporarily, phpMyAdmin will automatically add, then in your script, don't forget to put it back if you need
    - Son Nguyen
    AdSpeed.com - Ad Serving and Ad Management Made Easy


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
  •