TW Blog :: Archives for February 2004

Selecting MySQL Next And Previous Revisited

Relates to MySQL and Databases

The other day I discussed a simple technique for selecting the next and previous records from a MySQL database using variables. Here is a little tip for extending this to limit the results to a defined number of most recent entries.

Having selected the unique ID for the next and previous records, as discussed in the previous blog entry, another variable can be defined which counts the number of records posted more recently than the previous record. Then the previous entry is only returned if it is within a predefined boundary for recent entries. Here is the full SQL:

# set the boundary to the most recent five results
SET @lower_bound := 5;

# set the id of the previous entry
SELECT @prev := uid 
       FROM table 
       WHERE uid < {x} 
       ORDER by uid DESC 
       LIMIT 0, 1;

# set the id of the next entry
SELECT @next := uid 
       FROM table
       WHERE uid > {x}
       ORDER by uid ASC
       LIMIT 0, 1;
       
# get the number of records posted more 
# recently than the previous record
SELECT @records := COUNT(uid)
       FROM table
       WHERE uid > @prev
       ORDER by uid DESC;

# create a return result array, but only include
# the previous record if it falls within the boundary
# of most recent records
SELECT IF(uid = @prev,'p','n') as pos, uid, title 
       FROM table
       WHERE (uid = @prev AND @records < @lower_bound) 
              OR uid = @next

A typical application of this solution is distinguishing latest news items from the news archive in a small scale News Desk (i.e. where new items may only be added occasionally so the date is not a reliable factor for determining the latest posts). By integrating a couple of simple mod-rewrite rules in the configuration or .htaccess file, and a cache module (eg PEAR::Cache_DB) to compensate for the additional database overhead a complete News Desk with latest items and archive items can be readily simulated through a single source.

Posted on Sunday, Feb 08, 2004 at 04:30:40.