Last updated June 24, 2009. Created by aj045 on June 15, 2009.
Log in to edit this page.

When you read a text book, magazine, or newspaper, the information is sliced up into equal sized pieces called pages. Drupal has a way to take information from database queries and slice it up into little pieces called "pages". The process of doing this is called "paging".

Unfortunately, paging is a code-level concept. You can only create pagers via code, although there are some third-party modules out there that provide front-end interfaces. There's also the Views module which can be used to query out lists of data and page it. Use Views if you need the convenience of a tool as well as the front-end it provides. Use paging from Drupal if you need a simple list and can write code.

A simple example

For this example, we'll pull data from the node table to create our pager. Our pager will use tables for the final output so that the information is easier to read. You can use tables in your pager (probably the quickest and easiest) but it's also possible to use other methods of representing the final output, such as lists.

<?php
// The whole process is the same as creating a regular table in Drupal, but with a few
// minor changes which we'll make note of when we get to them.
// We start with headers for the table columns.
$headers = array(
 
t('Node ID'),
 
t('Type'),
 
t('Title'),
);
// Create a place to store the rows when we process the query in a few moments.
// This is just like making a table in Drupal.
$rows = array();
// SQL to get all the nodes in the {node} table.  Nothing special needs to be done
// to this query in this example.  You can even throw in an ORDER BY clause to
// pre-sort the data.
$sql = "SELECT nid, type, title FROM {node}";
// Number of records (or nodes, in our case) to show per page.
$count = 12;
// Now, this is where paging will differ from a regular table.  We use pager_query() instead of db_query().
// Note that there are serious differences between pager_query() and db_query().  Please make sure you
// are aware of them.
$query = pager_query($sql, $count);
// Process the query...
while ($record = db_fetch_object($query)) {
 
$rows[] = array(
   
$record->nid,
   
$record->type,
   
$record->title,
  );
}
// Make the table...
$output = theme('table', $headers, $rows);
// Return the output with the pager attached to it.  The pager
// is the box underneath the table that allows people to jump
// to the next page.
return $output . theme('pager', $count);
?>

This simple example is enough for very basic pagers. It can fit nicely into nodes, administrative pages, and even blocks.

More about paging

  • Paging does not require specially modified SQL query code. Just use regular SELECT statements to get your data out of the database. The only exception is when you want to have sorting functionality on table columns. If you are creating a table to output your pager on to and you want sort-by-column functionality, you MUST NOT include a trailing ORDER BY clause on your SQL query. The ORDER BY clause will be added automatically.
  • theme('pager') requires no additional arguments other than the count of the number of records to display per page. And even that is optional. Drupal will handle all the details behind the scenes for you. You don't have to tell theme('pager') what page it is on. That data is provided to the function automatically.

Looking for support? Visit the Drupal.org forums, or join #drupal-support in IRC.

Comments

I was unable to get sorting to work with just the code above. Here is how I got sorting to work:

<?php
$header
= array(
  array(
'data' => t("Title"), 'field' => 'title', 'sort' => 'asc', ),
  ...
);
// create your sql query
$sql = "SELECT * from {node}";
$query = pager_query($sql . tablesort_sql($header), 20);
?>

The key is to use tablesort_sql to tell drupal to add the appropriate ORDER BY clauses.

The pager_query function generates a select statement to count the number of rows returned by your query. To create this select statement, pager_query uses a regular expression. In order for this regular expression to be generated, the SELECT and FROM keywords must be in upper case in your query.

If the SELECT and FROM keywords in your query are lower case, pager_query cannot generate the select query to count the rows correctly. This was causing my pager to display WAY too many pages.

See the comment below...
https://api.drupal.org/comment/290#comment-290

One at the top and one at the bottom? Cheers!

A Young
Vancouver, BC
http://www.adamtyoung.ca

Hi there,

The theme('pager', $count); doesnt work if there is a direct array of data which is filled in the $rows[] using for loop.
This only works when $query = pager_query($sql, $count); is used with it.

How to get pager for the below code?

$uid = $xml->Product;
for ($i = 0; $i < $xml_count; $i++) {
$book_id = $uid[$i]->ProductIdentifier[1]->IDValue;
$title_name = $uid[$i]->DescriptiveDetail->TitleDetail->TitleElement->TitleText;
$image_path = $uid[$i]->CollateralDetail->SupportingResource->ResourceVersion->ResourceLink;
$data[] = array($title_name, $book_id);
}
$table_attributes = array('width' => '100%', 'border' => 2, 'cellspacing' => 5, cellpadding => 5);
$header = array('Book Title', 'BookId');
$output .= theme('table', $header, $data, $table_attributes);
$output .= theme('pager',10);
}
return $output;