Perl Cd Bookshelf [Electronic resources] نسخه متنی

14.16.1. Problem

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.

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. , .<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('/%d', url(-full => 1), $prev_rec);
$next_rec = $last + 1;
$next_link = sprintf('/%d', url(-full => 1), $next_rec);
if ($first = = 1) {
print 'Previous';
} else {
printf('<a href=">Previous</a>', $prev_link);
}
print " | "; # separate "Previous" and "Next"
if ($next_rec < $count) {
printf('<a href=">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. , .<br>", $i+$first, $user->[1], $user->[2]);
}

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