Recipe 10.13. Making Paginated Links for a Series of Records


10.13.1. Problem

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

10.13.2. Solution

Use database-appropriate syntax to grab just a section of all the rows that match your query. Example 10-35 shows how this works with SQLite.

Paging with SQLite

<?php // Select 5 rows, starting after the first 3 foreach ($db->query('SELECT * FROM zodiac ' .                     'ORDER BY sign LIMIT 5 ' .                     'OFFSET 3') as $row) {      // Do something with each row } ?>

The pc_indexed_links( ) and pc_print_link( ) functions in this recipe assist with printing paging information. Example 10-36 shows them in action.

Displaying paginated results

<?php $offset = isset($_GET['offset']) ? intval($_GET['offset']) : 1; if (! $offset) { $offset = 1; } $per_page = 5; $total = $db->query('SELECT COUNT(*) FROM zodiac')->fetchColumn(0); $limitedSQL = 'SELECT * FROM zodiac ORDER BY id ' .               "LIMIT $per_page OFFSET " . ($offset-1); $lastRowNumber = $offset - 1; foreach ($db->query($limitedSQL) as $row) {     $lastRowNumber++;     print "{$row['sign']}, {$row['symbol']} ({$row['id']}) <br/>\n"; } pc_indexed_links($total,$offset,$per_page); print "<br/>"; print "(Displaying $offset - $lastRowNumber of $total)"; ?>

10.13.3. Discussion

pc_print_link( ) is shown in Example 10-37 and pc_indexed_links( ) in Example 10-38.

pc_print_link( )

<?php function pc_print_link($inactive,$text,$offset='') {     if ($inactive) {         print "<span class='inactive'>$text</span>";     } else {         print "<span class='active'>".               "<a href='" . htmlentities($_SERVER['PHP_SELF']) .               "?offset=$offset'>$text</a></span>";     } } ?>

pc_indexed_links( )

<?php function pc_indexed_links($total,$offset,$per_page) {     $separator = ' | ';     // print "<<Prev" link     pc_print_link($offset == 1, '<< 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 >>',$offset + $per_page); } ?>

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

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:

<?php $limitedSQL = 'SELECT * FROM zodiac ORDER BY id ' . "LIMIT $per_page OFFSET " . ($offset-1); ?>

The LIMIT and OFFSET keywords are how you tell SQLite to return just a subset of all matching rows.

The relevant rows are retrieved by $db->query($limitedSQL), 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-3.

Paginated results with pc_indexed_links( )


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

10.13.4. See Also

A discussion of paging in the Solar framework at http://paul-m-jones.com/blog/?p=185 and information on different database paging syntaxes at http://troels.arvin.dk/db/rdbms/#select-limit-offset.




PHP Cookbook, 2nd Edition
PHP Cookbook: Solutions and Examples for PHP Programmers
ISBN: 0596101015
EAN: 2147483647
Year: 2006
Pages: 445

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net