MySQL Cookbook

18.11 Generating Previous-Page and Next-Page Links

18.11.1 Problem

A query matches so many records that displaying them all in a single web page produces an unwieldy result.

18.11.2 Solution

Split the query output across several pages and include links that allow the user to navigate among pages.

18.11.3 Discussion

If a query matches a large number of records, showing them all in a single web page can result in a display that's difficult to navigate. For such cases, it can be more convenient for the user if you split the result among multiple pages. Such a paged display avoids overwhelming the user with too much information, but is more difficult to implement than a single-page display.

A paged display typically is used in a search context to present records that match the search parameters supplied by the user. To simplify things, the examples in this section don't have any search interface. Instead, they implement a paged display that presents 10 rows at a time from the result of a fixed query:

SELECT name, abbrev, statehood, pop  FROM states ORDER BY name;

MySQL makes it easy to select just a portion of a result set: add a LIMIT clause that indicates which records you want. The two-argument form of LIMIT takes values indicating how many records to skip at the beginning of the result set, and how many to select. The query to select a section of the states table thus becomes:

SELECT name, abbrev, statehood, pop  FROM states ORDER BY name
LIMIT skip,select;

One issue, then, is to determine the proper values of skip and select for any given page. Another is to generate the links that point to other pages or the query result. One style of paged display presents only "previous page" and "next page" links. To do this, you need to know whether any records precede or follow those you're displaying in the current page. Another paging style displays a link for each available page. This allows the user to jump directly to any page, not just the previous or next page. To present this kind of navigation, you have to know how the total number of records in the result set and the number of records per page, so that you can determine how many pages there are.

18.11.4 Paged Displays with Previous-Page and Next-Page Links

The following script, state_pager1.pl, presents records from the states table in a paged display that includes navigation links only to the previous and next pages. For a given page, we can determine which links are needed as follows:

• A "previous page" link is needed if there are records in the result set preceding those shown in the current page. If the current page starts at record one, there are no such records.

• A "next page" link is needed if there are records in the result set following those shown in the current page. You can determine this by issuing a SELECT COUNT(*) query to see how many records the query matches in total. Another method is to select one more record than you need. For example, if you're displaying 10 records at a time, try to select 11 records. If you get 11, there is a next page. If you get 10 or less, there isn't. state_pager1.pl uses the latter approach.

To determine its current position in the result set and how many records to display, state_pager1.pl looks for start and per_page input parameters. When you first invoke the script, these parameters won't be present, so they're initialized to 1 and 10, respectively. Thereafter, the script generates "previous page" and "next page" links to itself that include the proper parameter values in the URLs for selecting the previous or next sections of the result set.

#! /usr/bin/perl -w
# state_pager1.pl - paged display of states, with prev-page/next-page links

use strict;
use lib qw(/usr/local/apache/lib/perl);
use CGI qw(:standard escape escapeHTML);
use Cookbook;

my $title = "Paged US State List";

my $page = header ( )
            . start_html (-title => $title, -bgcolor => "white")
            . h3 ($title);

my $dbh = Cookbook::connect ( );

# Collect parameters that determine where we are in the display.
# Default to beginning of result set, 10 records/page if parameters
# are missing/malformed.

my $start = param ("start");
$start = 1
    if !defined ($start) || $start !~ /^\d+$/ || $start < 1;

my $per_page = param ("per_page");
$per_page = 10
    if !defined ($per_page) || $per_page !~ /^\d+$/ || $per_page < 1;;

# If start > 1, then we'll need a live "previous page" link.
# To determine whether or not there is a next page, try to select one
# more record than we need.  If we get that many, display only the first
# $per_page records, but add a live "next page" link.

# Select the records in the current page of the result set, and
# attempt to get an extra record.  (If we get the extra one, we
# won't display it, but its presence tells us there is a next
# page.)

my $query = sprintf (
                "SELECT name, abbrev, statehood, pop
                FROM states
                ORDER BY name LIMIT %d,%d",
                $start - 1,         # number of records to skip
                $per_page + 1);     # number of records to select

my $tbl_ref = $dbh->selectall_arrayref ($query);

$dbh->disconnect ( );

# Display results as HTML table
my @rows;
push (@rows, Tr (th (["Name", "Abbrevation", "Statehood", "Population"])));
for (my $i = 0; $i < $per_page && $i < @{$tbl_ref}; $i++)
{
    # get data values in row $i
    my @cells = @{$tbl_ref->[$i]};  # get data values in row $i
    # map values to HTML-encoded values, or to &nbsp; if null/empty
    @cells = map {
                defined ($_) && $_ ne "" ? escapeHTML ($_) : "&nbsp;"
                } @cells;
    # add cells to table
    push (@rows, Tr (td (\@cells)));
}

$page .= table ({-border => 1}, @rows) . br ( );

# If we're not at the beginning of the query result, present a live
# link to the previous page.  Otherwise present static text.

if ($start > 1)                 # live link
{
    my $url = sprintf ("%s?start=%d;per_page=%d",
                        url ( ),
                        $start - $per_page,
                        $per_page);
    $page .= "[" . a ({-href => $url}, "previous page") . "] ";
}
else                            # static text
{
    $page .= "[previous page]";
}

# If we got the extra record, present a live link to the next page.
# Otherwise present static text.

if (@{$tbl_ref} > $per_page)    # live link
{
    my $url = sprintf ("%s?start=%d;per_page=%d",
                        url ( ),
                        $start + $per_page,
                        $per_page);
    $page .= "[" . a ({-href => $url}, "next page") . "]";
}
else                            # static text
{
    $page .= "[next page]";
}

$page .= end_html ( );

print $page;

exit (0);

18.11.5 Paged Displays with Links to Each Page

The next script, state_pager2.pl, is much like state_pager1.pl, but presents a paged display that includes navigation links to each page of the query result. To do this, it's necessary to know how many row there are in all. state_pager2.pl determines this by running a SELECT COUNT(*) query. Because it then knows the total row count, it's unnecessary to select an extra row when fetching the section of the result to be displayed.

Omitting the parts of state_pager2.pl that are the same as state_pager1.pl, the middle part that retrieves records and generates links is implemented as follows:

# Determine total number of records

my $total_recs = $dbh->selectrow_array ("SELECT COUNT(*) FROM states");

# Select the records in the current page of the result set

my $query = sprintf (
                "SELECT name, abbrev, statehood, pop
                FROM states
                ORDER BY name LIMIT %d,%d",
                $start - 1,         # number of records to skip
                $per_page);         # number of records to select

my $tbl_ref = $dbh->selectall_arrayref ($query);

$dbh->disconnect ( );

# Display results as HTML table
my @rows;
push (@rows, Tr (th (["Name", "Abbrevation", "Statehood", "Population"])));
for (my $i = 0; $i < @{$tbl_ref}; $i++)
{
    # get data values in row $i
    my @cells = @{$tbl_ref->[$i]};  # get data values in row $i
    # map values to HTML-encoded values, or to &nbsp; if null/empty
    @cells = map {
                defined ($_) && $_ ne "" ? escapeHTML ($_) : "&nbsp;"
                } @cells;
    # add cells to table
    push (@rows, Tr (td (\@cells)));
}

$page .= table ({-border => 1}, @rows) . br ( );

# Generate links to all pages of the result set.  All links are
# live, except the one to the current page, which is displayed as
# static text.  Link label format is "[m to n]" where m and n are
# the numbers of the first and last records displayed on the page.

for (my $first = 1; $first <= $total_recs; $first += $per_page)
{
    my $last = $first + $per_page - 1;
    $last = $total_recs if $last > $total_recs;
    my $label = "$first to $last";
    my $link;

    if ($first != $start)   # live link
    {
        my $url = sprintf ("%s?start=%d;per_page=%d",
                            url ( ),
                            $first,
                            $per_page);
        $link = a ({-href => $url}, $label);
    }
    else                    # static text
    {
        $link = $label;
    }
    $page .= "[$link] ";
}