PHP/MySQL datetime before after question

[Leszek]

Hi,

I've written a simple photo gallery and now am in need of making a simple "before" and "after" links to let people view previous and next photos sorted by date.
My MySQL gallery table contains: id, name, description, category_id, date, filename, views.

The date field is a datetime. It contains date and time in this format: 2010-01-09 18:11:46

I need to read one previous and one next id of the photo using a given date (correct me if I'm wrong):

$query = "SELECT * FROM `gallery` WHERE `date` < `".$actual_photo_date."` AND `category_id`=".$category." LIMIT 1;";
$prev = mysql_query(mysql_real_escape_string($query));
$prevrecord = mysql_fetch_assoc($prev);
$prev = $prevrecord['id'];

It returns an error: Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in ....

What am I doing wrong ? What's wrong with the query ? Should I surround $actual_photo_date in ' or " or maybe ` signs ?

Thanks for the help.

[Leszek]

I've solved the problem.
I've converted the "date" field from "datetime" to "int" and also converted the dates to php Unix timestamp. The rest is:

$previous = mysql_query(mysql_real_escape_string("SELECT * FROM `gallery` WHERE `date` > ".$date." AND `category`=".$category." ORDER BY `date` ASC LIMIT 1;"));
$prevrecord = mysql_fetch_assoc($previous);
$previous = $prevrecord['id'];
$next = mysql_query(mysql_real_escape_string("SELECT * FROM `gallery` WHERE `date` < ".date." AND `category`=".$category." ORDER BY `date` DESC LIMIT 1;"));
$nextrecord = mysql_fetch_assoc($next);
$next = $nextrecord['id'];

Now everything is working as expected.