TW Blog :: Archives for October 2008

Selecting Next and Previous - MySQL and PEAR::MDB2

Relates to PHP and MySQL, PEAR

Quite a considerable time ago I discussed methods for selecting the next and previous records from a MySQL database using MySQL variables. This method required sending multiple requests to the database via the abstraction layer - at the time I was using PEAR::DB.

Things have progressed a bit since early 2004 with more recent versions of MySQL introducing sub-select queries and PHP5's PEAR::MDB2 abstraction layer offering better handling of parameters in SQL queries. Such that the entire process of selecting the next and previous record can be simplified into a single query. Let us dive straight into the code and then I will break it down:

$query = "SELECT IF(publish_date < :publish_date, 'prev', 'next') as position, title, CONCAT(:archive_dir, permalink) as link FROM news WHERE id IN
          (
            SELECT id FROM news WHERE 
              id = ( SELECT id FROM news WHERE publish_date < :publish_date AND post_status = 'published' ORDER by publish_date DESC LIMIT 0,1 ) 
                OR
              id = ( SELECT id FROM news WHERE publish_date > :publish_date AND post_status = 'published' ORDER by publish_date ASC LIMIT 0,1 )
          )";

$parameters = array(
  'publish_date' => $this->dataset->publish_date,
  'archive_dir' => self::getDirective('ss_news_archive_dir')
);
        
$links = $this->db->getAssoc($query, null, $parameters);

The objective of the above code is to get the next and previous news entries published. So comparison is done on the publish dates relative to the current record.

Preparing to execute the query establishes two parameters (in $parameters) which will be passed to the MDB2 query. The publish date of the current record ($this->dataset->publish_date) is the first parameter while the second parameter is a configuration directive for the current News module.

Note that the actual call to the MDB2 layer ($this->db) requires the use of the Extended module so establishing a connection to the database may be as follows:

$this->db = MDB2::factory($dsn);
$this->db->loadModule('Extended');
$this->db->setFetchMode(MDB2_FETCHMODE_OBJECT);

Now to the actual SQL query. Working backwards from the inside of the query the following three processes are taking place:

1. The ID of the previous and next records are retrieved by comparing the publish dates with the current record. This is analogous to the engineering described in the original post back in 2004.
2. These two results are then fed into a Select query for a single column
3. Which then allows this column to become the search parameters for the actual SQL query which returns the complete set of data for the next and previous records (using the IN operator)

It is the second phase in the process above which essentially acts as a glue and allows what would otherwise have to be a set of unique queries to be amalgamated into a single query. A typical result from the above code might be:

Array
(
  [prev] => stdClass Object
  (
    [position] => prev
    [title] => The previous post
    [link] => /newsdesk/previous_post.html
  ),
  [next] => stdClass Object
  (
    [position] => next
    [title] => The next post
    [link] => /newsdesk/next_post.html
  )
)

I forgot to mention from a performance perspective this is very efficient and a few rough benchmark tests showed that execution time was similar to running one of the three (or four) queries required to achieve the same data without sub-selects. Performance could possibly be improved even further, if required, by using an inner join instead of sub-select (but I am yet to try this method).

Posted on Tuesday, Oct 14, 2008 at 12:00:19.