[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
This works for me:
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.
SELECT * FROM timetest
WHEN `time` > CURTIME() THEN 1
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
Thank you so much Derokorian, that works perfectly
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)