TW Blog :: Entries for MySQL

Selecting Next and Previous - MySQL and PEAR::MDB2

Also relates to PHP and 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 Oct 14, 2008 at 11:00:19.

Resequence a MySQL column

Also relates to PEAR

The other day I was asked if I knew a way to resequence a column in a MySQL table. For example if a record were removed from mid-table, how to collapse all the ids above that record to avoid gaps. Of course this would be highly undesirable and potentially destructive if relationships exist, but made an interesting little problem.

The initial solution I suggested, utilising the PEAR_DB package as abstraction layer for the PHP MySQL API, was as follows:

$id = {num}; // the id of the record that was deleted
$pr = $db->prepare('UPDATE table_name SET col_name = ! WHERE col_name = !');
do {
  $res = $db->execute($pr, array($id, ++$id));  
  if (DB::isError($res)) {
    // handle error
    break;
  }
} while ($db->affectedRows() > 0);

Errors are trapped to allow break-out from the loop, and, since $db->affectedRows() can return -1 if the query fails, look for at least one (will be only one per loop execution) affected row.

While this seemed like a nice simple solution, it doesn't account for a column that AUTO_INCREMENTs, so assumes that values for col_name are entered manually. Then, courtesy of the MySQL cookbook, I stumbled across a more efficient solution that takes into account just that by utilising the DDL component of the MySQL query language:

$query = "ALTER TABLE table_name "
       . "DROP col_name, "
       . "ADD col_name INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST, "
       . "AUTO_INCREMENT = 1";
$res = @mysql_query($query, $conn);
if (!$res) { // handle errors }

For simplicity, this just uses the standard PHP MySQL API. This will recreate col_name placing it first in the schema order (FIRST). Thus recreating the sequence starting the increment from 1 (AUTO_INCREMENT = 1 - prior to 3.23.39 only). Finally, there is no need to redefine the PRIMARY KEY if it is col_name, since MySQL does not drop the key during the execution of a single ALTER statement.

Personally I cannot think of any reason I would have to resequence a column, especially if it were the PRIMARY KEY, but nice to know it is possible…

Posted on Apr 16, 2005 at 13:51:16. [Comments for Resequence a MySQL column- 0]