What's wrong with my query? Deleting rows by date

Okay I am confused about why this does not work. Every reference I can find online including a post ofn this site says it should work.

$today = date("Y-d-m");  
echo "<h3> Today is: ". $today . "</h3>"; // gives the correct date in the proper format
mysql_query("DELETE FROM table WHERE date < '$today'")or die(mysql_error());  

PHP:

The date field is in date format in the DB and the seperators match (-).

It deletes all the entries in the database, two of which are older than today and three are in the future.

 

this should work...

$today = date("Y-d-m");  
echo "<h3> Today is: ". $today . "</h3>";
mysql_query("DELETE FROM table WHERE date < {$today}")or die(mysql_error());

Code (markup):

the mistake ur making is $today variable isnt generating any date.. it is being passed as a text "today"... to pass the value of a variable within double quote u need to use {$varaibleName} instead of using '$variableName'.. because second one is just wrong...

single quotation is used only to use quotation inside of quotation not to pass the value of variable...
hope that helps...

 

I'm not 100% sure, but I think in order to do date comparison via SQL queries in the format you want, you have to use the 'DATE' data type for storing the date, which will be in the form of YYYY-MM-DD, not YYYY-DD-MM.

Are you storing the date as a VARCHAR or TINYTEXT or something like that? If so, I think that may be causing the problem, along with the improper date structure. I'm not positive though, where's munj?

 

$today = date("Y-d-m");

you might also want to try

$today = date("Y-m-d");

 

Winner!

I screwed up the format on the date(). Y-m-d is correct as the data is stored in the database in that format.

Greenies added for the help.

 

did I miss anything?

you certainly gave a correct answer.

If deleting will always be based on date less than today then we need not even pick date from php, mysql has built in function / variables representing today's date.

query would be something like this..

mysql_query("DELETE FROM table WHERE date < current_date") or die(mysql_error());

PHP:

 

I also want the current date displayed on the page but your method is nice to know.

Thanks for the post.