ProblemA statement matches so many rows that displaying them all in a single web page produces an unwieldy result. SolutionSplit the statement output across several pages and include links that enable the user to navigate among pages. DiscussionIf 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?
Paged displays with previous-page and next-page linksThe 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:
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 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; Paged displays with links to each pageThe 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 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] "; } |