[RESOLVED] Sorting by time closest to now
Results 1 to 3 of 3

Thread: [RESOLVED] Sorting by time closest to now

  1. #1
    Member
    Join Date
    Dec 2008
    Posts
    56

    resolved [RESOLVED] Sorting by time closest to now

    Hi there, Id love some advice on this as am really stuck. Basically, I have a simple database of songs that Im creating a playlist from for a certain time of the day. Each song has a start time in the table - defined by mysql TIME format field of hh:mm:ss.

    When my playlist.php file is queried, I simply want to return all the songs in the table where the start time>=now() appears first, followed by the rest of the songs after that in order from the next earliest start time again. I was trying:

    SELECT songid,title, time FROM songs ORDER BY time >now(),time ASC

    but its not giving me the results I desire. e.g. If I had 4 songs, with start times as follows:12:45,13:59,15:19, 16:00 and the time now was 15:45:00, I need to return 16:00:00, 12:45:00,13:59:00,15:19:00 and so on

    Is this possible or am I going about this in totally the wrong way

  2. #2
    Senior Member Derokorian's Avatar
    Join Date
    Apr 2011
    Location
    Denver
    Posts
    1,774
    This works for me:
    Code:
    SELECT * FROM timetest 
    ORDER BY
      CASE
        WHEN `time` > CURTIME() THEN 1
        ELSE 0
      END DESC,
    `time` ASC
    My table looked like: CREATE TIME timetest (`time` TIME) with just 4 rows but it ordered up what appears to be the way you want.
    Sadly, nobody codes for anyone on this forum. People taste your dishes and tell you what is missing, but they don't cook for you. ~anoopmail
    I'd rather be a comma, then a full stop.
    User Authentication in PHP with MySQLi - Don't forget to mark threads resolved - MySQL(i) warning

  3. #3
    Member
    Join Date
    Dec 2008
    Posts
    56
    Thank you so much Derokorian, that works perfectly

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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