Using the PEAR Pager Package to Paginate MySQL Results

With a bit of patience, even the greenest Web developer usually scores a quick victory in terms of learning how to use PHP to query and manipulate a MySQL database. This is a testament to the hard work put into the MySQLi and PDO extensions, as both are quite easy to use. Yet many newcomers tend to quickly run into a few notable obstacles which lay just beyond this familiar ground, including notably how to best go about splitting a set of records across multiple pages. Known as paginating a result set, the process is trickier than it may first seem because you’ll need to keep tabs on not only how many records you want to display per page, but also your current result set pointer location as you proceed from one page to the next.

Fortunately a great solution is at your disposal which has been created expressly for this purchase. The PEAR Pager package will not only handle all of the gory tracking details for you, but it can also create a linked navigation list which you can embed into the page as a navigational aide for the user. In this article I’ll show you how to use Pager to easily paginate your database results in a structured and coherent way.

Installing the Pager Package

Installing Pager is easy thanks to the convenient PEAR installer which is in all likelihood already installed on any computer presuming PHP is also installed. Just open a terminal window and execute the following command:

$ pear install Pager
downloading Pager-2.4.8.tgz ...
Starting to download Pager-2.4.8.tgz (36,122 bytes)
..........done: 36,122 bytes
install ok: channel://pear.php.net/Pager-2.4.8

Once installed it’s just a matter of including the Pager class into the desired PHP script:

include 'Pager/Pager.php';

Paginating an Array

The Pager package can paginate any array of data, so let’s start with an example which paginates an array consisting of the teams comprising the Big Ten Conference. The script begins by including the Pager class and defining the array:

include 'Pager/Pager.php';

$teams = array(
  'University of Illinois', 
  'Indiana University', 
  'University of Iowa', 
  'University of Michigan', 
  'Michigan State University', 
  'University of Minnesota', 
  'University of Nebraska', 
  'Northwestern University', 
  'Ohio State University', 
  'Penn State University', 
  'Purdue University',
  'University of Wisconsin'
);

Next you’ll define Pager’s pagination options using an associative array which at minimum consists of the following four items:

$options = array(
  'delta'    => 5,
  'itemData' => $teams,
  'mode'     => 'Jumping',
  'perPage'  => 3
);

The delta parameter defines the number of page numbers which will appear in the navigation menu. See Figure 1 for an example. The itemData parameter defines the array we’d like to paginate. The mode parameter defines the type of navigation menu you’d like to use. Two are supported, including Jumping and Sliding. Finally, the perPage parameter defines the number of array items which should be presented per page.

Next you’ll create an instance of the Pager class, passing the pagination options into the class factory:

$pager =& Pager::factory($options);

The following snippet determines the current result set position and outputs the appropriate number of elements to the page:

$set = $pager->getPageData();

foreach ($set as $item) {
  printf("%s
  ", $item);    
}

Finally the Pager’s getLinks() method retrieves the navigation menu and saves pertinent information about it to an associative array. The value associated with the array’s all key can be used to output the menu:

$menu = $pager->getLinks();

echo $menu['all'];

Assembling all of this code produces the page found in Figure 1.

Figure 1. Creating a Paginated Array

Try swapping out the Jumping navigation menu mode type with Sliding to understand the visual differences between the two.