Recipe 14.16 Viewing Data One Page at a Time

14.16.1 Problem

You want to display the contents of a table or the results of a query one page at a time.

14.16.2 Solution

Keep track of which record you're starting with, then use that value to decide how many records to skip before you display a page worth. If your database supports a LIMIT clause that takes a range, use this to avoid transferring unnecessary rows into your program.

14.16.3 Discussion

The example code in this recipe pages through the contents of a table. To page through the results of a query, select the data into a temporary table and page through that.

In desktop applications (e.g., Tk), you can keep track of the current page number yourself. With web applications, the easiest thing to do is to use query parameters in the URL to indicate where you are. For example:

/users-report/view?start=1

Begin by finding out how many records there are in total:

$row = $Dbh->selectrow_arrayref("SELECT COUNT(*) FROM Users");
$count = $row->[0];

Find the first record to display by looking at the start parameter, then calculate the last record from that. You need to know the number of records per page, which here we assume is in the $Page_Size variable:

$first = param('start') || 1;
$last  = $first + $Page_Size - 1;
$last = $count if $last > $count;   # don't go past the end

Now fetch the data into an array and display the records you're interested in:

$results = $Dbh->selectall_arrayref('SELECT id,lastname,firstname FROM Users
                                     ORDER BY lastname,firstname,id');

for (my $i=$first; $i <= $last; $i++) {
  my $user = $results->[$i-1];        # result 1 is in row 0
  printf("%d.  %s, %s.<br>\n", $i, $user->[1], $user->[2]);
}

That will produce output like:

1.  Brocard, Leon.<br>
2.  Cawley, Piers.<br>
3.  Christiansen, Tom.<br>

The last step is adding next and previous links to move to the next and previous pages (if available):

$prev_rec = $first - $Page_Size;
$prev_rec = 1 if $prev_rec < 1;
$prev_link = sprintf('%s/%d', url(-full => 1), $prev_rec);
$next_rec = $last + 1;
$next_link = sprintf('%s/%d', url(-full => 1), $next_rec);

if ($first =  = 1) {
  print 'Previous';
} else {
  printf('<a href="%s">Previous</a>', $prev_link);
}
print " | ";   # separate "Previous" and "Next"
if ($next_rec < $count) {
  printf('<a href="%s">Next</a>', $next_link);
} else {
  print 'Next';
}

This becomes easier if your database lets you specify an offset as part of the LIMIT clause (MySQL and PostgreSQL both do). Instead of transferring all records in the database back to your program, you need transfer only the records you're interested in:

$results = $dbh->selectall_arrayref("SELECT id,lastname,firstname FROM Users
                                     ORDER BY lastname,firstname,id
                                     LIMIT " . ($first-1) . ", $Page_Size");

for ($i=0; $i < @$results; $i++) {
  my $user = $results->[$i];
  printf("%d.  %s, %s.<br>", $i+$first, $user->[1], $user->[2]);
}

MySQL's LIMIT m,n is written LIMIT n OFFSET m in PostgreSQL.

14.16.4 See Also

The documentation with the DBI and DBIx::Pager modules from CPAN; http://dbi.perl.org and http://www.mysql.com; Programming the Perl DBI; MySQL Reference Manual, by Michael "Monty" Widenius, David Axmark, and MySQL AB (O'Reilly)