hey, I'm wondering how to retrieve data from my database using php to get the top songs today.
Right now I'm just getting the top songs using
$result = mysql_query("SELECT tag, COUNT(*) AS the_tags FROM tags GROUP BY tag ORDER BY the_tags DESC LIMIT 16");
I am also storing the date in the tags table aswell, in the format
07-25-2010
so Month - Day - Year
How can i have it limit the results to tags with the date of today?
Thanks :)
Another way to do it:
"SELECT tag, COUNT(*) AS the_tags FROM tags WHERE `date` = '" . date('m-d-Y') . "' GROUP BY tag ORDER BY the_tags DESC LIMIT 16"But I prefer to use MySQL's date (so I would say see artefacto's post)
...FROM tags WHERE date_field = DATE_FORMAT(CURDATE(), '%m-%d-%Y')...
I am also storing the date in the tags table as well, in the format 07-25-2010
The only solution is to change this ridiculous format to a proper one - YYYY-MM-DD and store it in the proper field of DATE type. Sooner you change it, less headache you've get in the future. You just have no other way. It's ABC of database architecture. There will be tons of cases where your own format will just not work. And database will have to convert it every time for the every row in the table. This is most efficient way to kill your database.
After change your code become
$sql = "SELECT tag, COUNT(*) AS the_tags FROM tags WHERE tag_date = curdate()
GROUP BY tag ORDER BY the_tags DESC LIMIT 16"
$result = mysql_query($sql) or trigger_error(mysql_error().$sql);
MM-DD-YYY is a terrible idea though.