[Resolved] curdate problem

Dolemite50

Hi, I am trying to select data from a mysql table and I want to target songs with today's date.
Here is my code:

 "SELECT songid, stitle FROM songs WHERE sdate = CURDATE() ORDER BY srating DESC LIMIT 3";

This didnt work. If I take out this line:

WHERE sdate = CURDATE()

It runs fine selecting from all songs, not just todays.

'sdate' is a datetime field. I uploaded to songs today and they are listed in mysql as "2004-10-28 18:31:56" in my sdate column. Will curdate() work with datetime or does the field need to be date?

This also didnt work:

 "SELECT songid, stitle, DATE_FORMAT(sdate, '%Y-%m-%d') AS sdate FROM songs WHERE sdate = CURDATE() ORDER BY srating DESC LIMIT 3";

Help!

Thanks

rachel2004

This will select songs with a datetime value within the past 24 hours:

"SELECT songid, stitle, DATE_FORMAT(sdate, '%Y-%m-%d') AS sdate FROM songs WHERE (UNIX_TIMESTAMP(sdate) >= UNIX_TIMESTAMP(NOW() - 86400)) ORDER BY srating DESC LIMIT 3";

Dolemite50

Thanks alot rachel, you have been helping me alot lately and I appreciate it.

I was just coming to mark this resolved because I just got this to work:

$getInfo = "SELECT songid, stitle, bandid FROM songs WHERE DATE_FORMAT(sdate,'%Y-%m-%d')= DATE_FORMAT(CURDATE(), '%Y-%m-%d') ORDER BY srating DESC LIMIT 3";

Is this a good way yo go or would yours be better? I plan on running this script as a cron job at 11:59 PM every night.

Thanks again. 🙂

rachel2004

In all honesty, since you are not pulling tons of records from the database, there wouldn't be any noticeable difference if your way works as well. As a general rule, use a timestamp to do comparisons between dates or times. The MySQL function UNIX_TIMESTAMP is ideal for this if those dates are stored in the database. The more you end up doing comparisons between two dates, the more you will appreciate it being there when you need it.

Your way is closer to what you had in mind, though, since it deals with the day's current date, whereas mine will pull records from the past 24 hours, which could include the previous date if someone runs the query at say, 2:33 a.m.