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:

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   if null/empty @cells = map { defined ($_) && $_ ne "" ? escapeHTML ($_) : " " } @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   if null/empty @cells = map { defined ($_) && $_ ne "" ? escapeHTML ($_) : " " } @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] "; }

Related

Категории