PHP/MySQL datetime before after question

Discussion in 'Programming/Scripts' started by Leszek, Jan 13, 2010.

  1. Leszek

    Leszek Member


    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.
    Last edited: Jan 15, 2010
  2. Leszek

    Leszek Member

    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.

Share This Page