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.
A paginated array
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.

Paginating Database Results

Because the Pager package is indifferent to the type of array it is paginating, paginating database results is done using a process almost identical to that presented in the previous example. Suppose the universities presented in the $teams array found in the previous example were instead stored in the following table:
CREATE TABLE teams (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  established CHAR(4) NOT NULL
);
The only significant difference between this and the previous example (other than the need to query the database) is the fact we'll want to be prudent when querying the database in order to ensure that we're only retrieving the slice of the records that we want to display on the page. Doing so will prevent the script from having to query for the entire result set every time a page comprising the paginated set is loaded.
Begin by connecting to the database:
<!--p

include 'Pager/Pager.php';

$perPage = 4;

$db = new mysqli('localhost', 'USERNAME', 'PASSWORD', 'teams');

$teams = array();
Next we need to determine how many total records are found in the table:
// How many total records?
$result = $db->query("SELECT count(*) FROM teams");
$row = $result->fetch_row();
$count = $row[0];
Next up we'll figure out which page we're currently on, and use that information to create a dynamic SQL query using a LIMIT clause. These results are then dumped to the $teams array:
if (isset($_GET['pageID']))
{
  $page = (int)$_GET['pageID'];
} else {
  $page = 1;
}

$offset = $page - 1;

if ($result = $db->query("SELECT name, established FROM teams 
                          ORDER BY established ASC LIMIT $page, $perPage"))
{

  while ($row = $result->fetch_assoc())
  {
    $teams[] = $row;
  }

}
As was the case in the previous example, we'll create the Pager instance, and iterate over the results provided by the database query:
$options = array(
  'delta' => 5,
  'itemData' => $teams,
  'mode' => 'Jumping',
  'perPage' => $perPage,
  'totalItems' => 10
);

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

$set = $pager->getPageData();

foreach ($set as $row => $team) {
  printf("%s (%s)
  ", $team['name'], $team['established']);
}
In order to produce a navigation menu consisting of all available pages, we'll create a new $options array, passing in the total available items (via the totalItems parameter):
$options = array(
  'delta' => 5,
  'mode' => 'Jumping',
  'perPage' => $perPage,
  'totalItems' => $count
);

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

$menu = $pager->getLinks();

echo $menu['all'];

?>
Executing this script produces the output found in Figure 2.
A paginated result set
Figure 2. Paginating Database Records

About the Author

Jason Gilmore is founder of the publishing, training, and consulting firm WJGilmore.com. He is the author of several popular books, including "Easy PHP Websites with the Zend Framework", "Easy PayPal with PHP", and "Beginning PHP and MySQL, Fourth Edition". Follow him on Twitter at @wjgilmore.