10.14.1. Problem

10.14.2. Solution

require 'DB/Pager.php';

$offset = intval($_REQUEST['offset']);
$per_page = 3;

$sth = $dbh->query('SELECT * FROM zodiac ORDER BY id');
$pager = new DB_Pager($sth, $offset, $per_page);
$data = $pager->build();

// display each row on this page  
while ($v = $pager->fetchRow()) {
    print "$v->sign, $v->symbol ($v->id)<br>";
}

// a link to the previous page
printf('<a href="%s?offset=%d">&lt;&lt;Prev</a> |',
       $_SERVER['PHP_SELF'],$data['prev']);

// direct links to each page
foreach ($data['pages'] as $page => $start) {
    printf(' <a href="%s?offset=%d">%d</a> |',$_SERVER['PHP_SELF'],$start,$page);
}

// a link to the next page
printf(' <a href="%s?offset=%d">Next&gt;&gt;</a>',
           $_SERVER['PHP_SELF'],$data['next']);

// display which records are on this page
printf("<br>(Displaying %d - %d of %d)",
       $data['from'],$data['to'],$data['numrows']);

$offset = intval($_REQUEST['offset']);
if (! $offset) { $offset = 1; }
$per_page = 5;
$total = $dbh->getOne('SELECT COUNT(*) FROM zodiac');

$sql = $dbh->modifyLimitQuery('SELECT * FROM zodiac ORDER BY id',
                              $offset - 1,$per_page);
$ar = $dbh->getAll($sql);
foreach ($ar as $k => $v) {
    print "$v->sign, $v->symbol ($v->id)<br>";
}

pc_indexed_links($total,$offset,$per_page);
printf("<br>(Displaying %d - %d of %d)",$offset,$offset+$k,$total);

10.14.3. Discussion

DB_Pager is designed specifically to paginate results that come from a PEAR DB query. To use it, create a DB_Pager object and tell it what query to use, what offset into the result set to start at, and how many items belong on each page. It calculates the correct pagination.

Figure 10-3. Paginated results with DB_Pager

All the page numbers, "<<Prev" and "Next>>," are links. "<<Prev" and "1" point to the current page; the others point to their corresponding pages. On page 4, the "Next>>" link points back to page 1. (But on page 1, the "<<Prev" link doesn't point to page 4.) The numbers in the links refer to page numbers, not element numbers.

$offset = intval($_REQUEST['offset']);
if (! $offset) { $offset = 1; }
$per_page = 5;
$total = $dbh->getOne('SELECT COUNT(*) FROM zodiac');

$sql = $dbh->modifyLimitQuery('SELECT * FROM zodiac ORDER BY id',
                              $offset - 1,$per_page);
$ar = $dbh->getAll($sql);
foreach ($ar as $k => $v) {
    print "$v->sign, $v->symbol ($v->id)<br>";
}

pc_indexed_links($total,$offset,$per_page);
printf("<br>(Displaying %d - %d of %d)",$offset,$offset+$k,$total);

After connecting to the database, you need to make sure $offset has an appropriate value. $offset is the beginning record in the result set that should be displayed. To start at the beginning of the result set, $offset should be 1. The variable $per_page is set to how many records to display on each page, and $total is the total number of records in the entire result set. For this example, all the Zodiac records are displayed, so $total is set to the count of all the rows in the entire table.

The SQL query that retrieves information in the proper order is:

SELECT * FROM zodiac ORDER BY id

Use modifyLimitQuery( ) to restrict the rows being retrieved. You'll want to retrieve $per_page rows, starting at $offset - 1, because the first row is 0, not 1, to the database. The modifyLimitQuery( ) method applies the correct database-specific logic to restrict what rows are returned by the query.

Figure 10-4. Paginated results with pc_indexed_links( )

10.14.4. See Also