can't call entries out of db based on date

[Kineas]

I've basically got a blog, and I've implemented versioning, and all of the old blog entries are stored in a seperate table to the latest version of them. I know I could have probably gone around this a different way, but the only unique field in the older versions table is the date_added field.

So when I'm trying to compare between versions, I'm calling entries out of the database based on the date_added field. The format in which the date and time is in is "yyyy-mm-dd hh:mm:ss". I'm passing the date and time through the url and set it as "compare". This is my code:

$comp = $_GET['compare'];

$id = mysql_real_escape_string($comp);

$sql ="SELECT * FROM messagesold WHERE date_added=$id";
$queryResult=mysql_query($sql);
if (mysql_num_rows($queryResult) == 1) {
$dbRecord = mysql_fetch_assoc ($queryResult);
echo "<TABLE bgcolor=\"#3366FF\" width=\"50%\">";
echo "<TR><TD><FONT face=\"Arial\" color=\"white\"><B>".$dbRecord["title"]." - ".$dbRecord['date_added']."</B></FONT></TD></TR>";
echo "<TR><TD bgcolor=\"white\" cellpadding=\"5\"><FONT face=\"Arial\"><P>".$dbRecord["message"]."</p></TD></TR>";
echo "</TABLE>";


When I run this, it gives an error, which I can get rid of by taking the space and :'s out of the time and date, and by manually changing the format in the database. But it doesn't show any results. I've even gone into phpmyadmin and copy and pasted the data saved in date_added and typed in the command line SELECT * FROM table WHERE date_added= and it says there's no results.

Any help would be great thanks.

[Old Pedant]

You need apostrophes around the date/time string:

$sql ="SELECT * FROM messagesold WHERE date_added='$id'";

Think about it a second: Without the apostrophes, the query becomes
... WHERE date_added=2009-09-22 14:23:15

So first MySQL tries to subtract 9 from 2009 and the 22 from that, which would work though not how you want it to. But then it gets to the space and the colon and barfs on your feet.
... WHERE date_added='2009-09-22 14:23:15'

and MySQL says "Oh! It's a string! And he is comparing it to a DateTime field. So I should interpret that string as a DateTime literal value. Okay, I'm happy!"

[Old Pedant]

Incidentally, the reason it works as
... WHERE date_added=20090922141523
is simply that MySQL *does* allow that as an alternative form for encoding a DateTime value. I don't recommend it, but it can be used.

See the MySQL docs under "data types".

[Kineas]

Thanks a lot, works perfectly.