WHERE issues with date field

[kyle.m]

I have a table that has id and date columns.  For whatever reason, the following works:

 

$result = mysql_query("SELECT MAX(id) FROM table WHERE date > " . date("Y-m-d"));

$row = mysql_fetch_array($result);                                       

$num = $row['MAX(id)'];

 

However, the following does not:

 

$result = mysql_query("SELECT MAX(id) FROM table WHERE date < " . date("Y-m-d"));

$row = mysql_fetch_array($result);                                       

$num = $row['MAX(id)'];

 

Note that the only change is from using a greater-than operator to a less than operator.  I have three entries in the table with the values:

 

2011-01-17

2011-01-18

2011-01-19

 

And the date(...) call is returning "2011-01-18".  I can't figure out why one query would work while the other does not.  This is tested in both the php page and using the SQL tool in phpMyAdmin.  Any insight on this would be greatly appreciated.

[Pikachu2000]

What happens if you use the MySQL CURDATE() function instead of concatenating in the php date() function?

 

SELECT MAX(id) FROM comics WHERE date > CURDATE()
SELECT MAX(id) FROM comics WHERE date < CURDATE()

[kyle.m]

I figured it out, binding quotes around the date field worked.

 

$result = mysql_query("SELECT MAX(id) FROM table WHERE date > \"" . date("Y-m-d") . "\"");

 

Although I'm still not sure why this works with one operator and not the other.

[kyle.m]

What happens if you use the MySQL CURDATE() function instead of concatenating in the php date() function?

 

SELECT MAX(id) FROM comics WHERE date > CURDATE()
SELECT MAX(id) FROM comics WHERE date < CURDATE()

 

That works as well (and seems to be a more elegant solution than my own).  Thank you.