Using the PEAR Pager Package to Paginate MySQL Results Page 2

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.

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.