Retrieve data Mysql / Php where date

Question

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 :)

Answer 1

...FROM tags WHERE date_field = DATE_FORMAT(CURDATE(), '%m-%d-%Y')...

Answer 2

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)

Answer 3

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);