Paginating Database Results

I describe how others page over results, and how I do it.

I find fascinating URIs that looks like this:

http://mysite.com/index.php?page=3&search=foo

Why do programmers use a “page”? It seems odd, because that number is going to be translated to a starting record number, and a number of records (per page). Effectively, the URL could be this:

http://mysite.com/index.php?offset=30&pagesize=10

Now, you can show any arbitrary number of rows, starting at any arbitrary point in the results. Of course, what you lose are the “pages”, because you can get at any single record in the result. (If they can type a url, that is.) To regain the page-y-ness of the interface, you need to write a loop that constructs the pager navigation. One way is this:

for( $i=0; $i < $resultcount; $i+=$pagesize )
{
    // start the link
    print '<a href="/drupal/script.php';
    print "?offset=".$i."&pagesize='.$pagesize;
    print '">'.title.'</a>';
    // and close the link
}

This way reduces the coupling between the creation of the page navigation from the display of the results. It also creates a cleaner mapping to the SQL OFFSET statement.

If you need to know what page you’re on, just divide the offset by the page size.

Leave a Reply