PHP Cookbook

10.13. Making Paginated Links for a Series of Records

Problem

You want to display a large dataset a page at a time and provide links that move through the dataset.

Solution

Use the PEAR DB_Pager class:

require 'DB/Pager.php';

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

$sth = $dbh->limitQuery('SELECT * FROM zodiac ORDER BY id',$offset,
$per_page);
// display each row on this page  
while ($v = $sth->fetchRow()) {
    print "$v->sign, $v->symbol ($v->id)<br>";
}
$data = DB_Pager::getData($offset, $per_page, $sth->numRows());

// 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']);

If you don’t have DB_Pager or you do but don’t want to use it, you can roll your own indexed link display using the pc_indexed_links( ) and pc_print_link( ) functions shown in the Discussion in Examples 10-2 and 10-3.

$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); ...