PHP CookBook

Recipe 10.14 Making Paginated Links for a Series of Records

10.14.1 Problem

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

10.14.2 Solution

Use the PEAR DB_Pager class:

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

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

The $pager->build( ) method calculates the appropriate rows to return and other page-specific variables. DB_Pager provides a fetchRow( ) method to retrieve the results in the same way the DB class operates. (You can also use fetchInto( ) with DB_Pager). However, while it provides all the data you need to build appropriate links, it also leaves it up to you to build those links. The offset the previous page starts at is in $data['prev'], and $data['next'] is the offset of the next page. The $data['pages'] array contains page numbers and their starting offsets. The output when $offset is is shown in Figure 10-3.

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.

If DB_Pager isn't available, you can use the pc_print_link( ) and pc_indexed_links( ) functions shown in Examples 10-2 and 10-3 to produce properly formatted links.

Example 10-2. pc_print_link( )

function pc_print_link($inactive,$text,$offset='') {

    if ($inactive) {
        printf('<font color="#666666">%s</font>',$text);
    } else {
        printf('<a href="%s?offset=%d">%s</a>',$_SERVER['PHP_SELF'],$offset,$text);
    }
}

Example 10-3. pc_indexed_links( )

function pc_indexed_links($total,$offset,$per_page) {
    $separator = ' | ';
    
    // print "<<Prev" link
    pc_print_link($offset == 1, '&lt;&lt;Prev', $offset - $per_page);


    // print all groupings except last one
    for ($start = 1, $end = $per_page;
         $end < $total;
         $start += $per_page, $end += $per_page) {

        print $separator;
        pc_print_link($offset == $start, "$start-$end", $start);
    }

    /* print the last grouping -
     * at this point, $start points to the element at the beginning
     * of the last grouping
     */
    
    /* the text should only contain a range if there's more than
     * one element on the last page. For example, the last grouping
     * of 11 elements with 5 per page should just say "11", not "11-11"
     */
    $end = ($total > $start) ? "-$total" : '';

    print $separator;
    pc_print_link($offset == $start, "$start$end", $start);
    
    // print "Next>>" link
    print $separator;
    pc_print_link($offset == $start, 'Next&gt;&gt;',$offset + $per_page);
}

To use these functions, retrieve the correct subset of the data using DB::modifyLimitQuery( ) and then print it out. Call pc_indexed_links( ) to display the indexed links:

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

The relevant rows are retrieved by $dbh->getAll($sql), and then information is displayed from each row. After the rows, pc_indexed_links( ) provides navigation links. The output when $offset is not set (or is 1) is shown in Figure 10-4.

Figure 10-4. Paginated results with pc_indexed_links( )

In Figure 10-4, "6-10", "11-12", and "Next>>" are links to the same page with adjusted $offset arguments, while "<<Prev" and "1-5" are greyed out, because what they would link to is what's currently displayed.

10.14.4 See Also

Information on DB_Pager at http://pear.php.net/package-info.php?package=DB_Pager.