I was working on a site today and needed to code a pager (the query was too complex for views). The most logical thing (which I looked for) was a tutorial on coding pagers in Drupal. Unfortunately, I was unable to find one, so I’ll post a tutorial here.

A pager basically splits a result set over multiple pages. It achieves it using a LIMIT clause, with an offset (page one is LIMIT 0, 10, page two is LIMIT 11, 20, etc).
There are three steps to create a custom pager.

Step One

Write your query and rendering code normally, without any LIMIT clause. For example, I wrote the following:

<?php
$sql = "SELECT n.title FROM node n
WHERE n.type = 'blog'
ORDER BY n.created DESC";
$query = db_query($sql);
$output = '';
while ($result = db_fetch_array($query)) {
$output .= '<h2>' . check_plain($result['title']) . '</h2>';
}
?>

Step Two

Wrap your query in pager_query instead of db_query.

<?php
$sql = "SELECT n.title FROM node n
WHERE n.type = 'blog'
ORDER BY n.created DESC";
$query = pager_query($sql);
$output = '';
while ($result = db_fetch_array($query)) {
$output .= '<h2>' . check_plain($result['title']) . '</h2>';
}
?>

Step Three

Add a theme('pager') call to the end of your output, to display the pager itself.

<?php
$sql = "SELECT n.title FROM node n
WHERE n.type = 'blog'
ORDER BY n.created DESC";
$query = pager_query($sql);
$output = '';
while ($result = db_fetch_array($query)) {
$output .= '<h2>' . check_plain($result['title']) . '</h2>';
}
$output .= theme('pager').
?>

And that’s all! A basic pager

Variations

Sometimes, you need more than a simple pager. So here are some variations.

Query arguments

If you need to input query arguments, you can append them to the end of the pager_query call. However, pager query takes three other arguments, so you’ll need to fill in the default values (unless you’re setting them to something else; see below).

<?php
$query = pager_query($sql, 10, 0, NULL, $user->uid);
?>
Different number of items per page

Items per page is the second argument to pager_query (after the query). While it defaults to 10, you can set it to any number you want.

Multiple pagers

If there are multiple pagers on a single page, Drupal can get confused as to which pager you’re clicking the “Next” link on. Thus, the third argument to pager_query. is the pager ID. The pager ID defaults to 0, but for each pager, it should be increased. This produces urls that look like /foo?page=0,4 for the 1th page of the first pager and 5th page (they start from 0) of the second. If you were to set the id to 25 to avoid any collisions, your url would look like this: foo?page=,,,,,,,,,,,,,,,,,,,,,,,,x, so be careful as to which ID you choose.

GROUP BY

Queries with GROUP BY clauses need special care. Copied from the documentation on pager_query:

Unfortunately, the rewrite rule does not always work as intended for queries that already have a “COUNT(*)” or a “GROUP BY” clause, and possibly for other complex queries. In those cases, you can optionally pass a query that will be used to count the records. For example, if you want to page the query “SELECT COUNT(*), TYPE FROM node GROUP BY TYPE”, pager_query() would invoke the incorrect query “SELECT COUNT(*) FROM node GROUP BY TYPE”. So instead, you should pass “SELECT COUNT(DISTINCT(TYPE)) FROM node” as the optional $count_query [fourth] parameter.

Conclusion

People have done some pretty neat things with pagers as well. Robert Douglass’ article about non-database pagers is a prime example. However, the pager system isn’t all that flexible. Earl Miles has an article on how pagers could be improved. While it’s too late for Drupal 7, this could totally work in Drupal 8
Note: this technique works in Drupal 5 and 6, but it changed in 7, and since I’m not building websites on 7 yet, I don’t know. I will post a tutorial for 7 closer to its release date