Recipe 19.10. Generating Previous-Page and Next-Page Links


Problem

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

Solution

Split the statement output across several pages and include links that enable the user to navigate among pages.

Discussion

If a statement matches a large number of rows, 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 rows that match the search parameters supplied by the user. To simplify things, the examples in this recipe 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 statement:

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 rows you want. The two-argument form of LIMIT takes values indicating how many rows to skip at the beginning of the result set, and how many to select. The statement 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 statement result. This second issue presents you with a choice: which paging style should you use for the links?

  • One style of paged display presents only "previous page" and "next page" links. To do this, you need to know whether any rows precede or follow those you're displaying in the current page.

  • Another paging style displays a link for each available page. This enables 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 the total number of rows in the result set and the number of rows per page, so that you can determine how many pages there are.

Paged displays with previous-page and next-page links

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

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

  • A "next page" link is needed if there are rows in the result set following those shown in the current page. You can determine this by issuing a SELECT COUNT(*) statement to see how many rows the statement matches in total. Another method is to select one more row than you need. For example, if you're displaying 10 rows at a time, try to select 11 rows. 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 rows 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 # state_pager1.pl - paged display of states, with prev-page/next-page links use strict; use warnings; use CGI qw(:standard escape escapeHTML); use Cookbook; my $title = "Paged U.S. 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 and # verify that they are integers. # 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 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 $stmt = 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 ($stmt); $dbh->disconnect (); # Display results as HTML table my @rows; push (@rows, Tr (th (["Name", "Abbreviation", "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; 

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 rows there are in all. state_pager2.pl determines this by running a SELECT COUNT(*) statement. Because the script then knows the total row count, it need not 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 rows 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 $stmt = 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 ($stmt); $dbh->disconnect (); # Display results as HTML table my @rows; push (@rows, Tr (th (["Name", "Abbreviation", "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] ";                                       } 




MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2004
Pages: 375
Authors: Paul DuBois

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