PHP Database Access Making Paginated Links for a Series of Records

PHP Database Access

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

Example  Paging with SQLite

        // 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

        }

Example  Displaying paginated results

        $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";

        }

        indexed_links($total,$offset,$per_page);

         print "<br/>";

         print "(Displaying $offset - $lastRowNumber of $total)";

Discussion

Example  print_link()

        function 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>";

                 }

        }

Example  indexed_links()

        function indexed_links($total,$offset,$per_page) {

                 $separator = ' | ';

                 // print "<<Prev" link

                 print_link($offset == 1, '<< Prev', max(1, $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;

                                  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;

                  print_link($offset == $start, "$start$end", $start);

      
                  // print "Next>>" link

                  print $separator;

                  print_link($offset == $start, 'Next >>',$offset + $per_page);

        }

To use these functions, retrieve the correct subset of the data using appropriate PDO functions and then print it out. Call 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:

       $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, indexed_links() provides navigation links.

The output when $offset is not set (or is 1) is shown in Figure.

Figure  Paginated result with indexed_links()

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