Paging MySQL database results, printing 10 records per page using Perl
published 01.Apr.2002
Setting up paging of database results is very easy to achieve by using MySQLs LIMIT command, and a little Perl script. The code snippets below demonstrate how to display 10 records at a time from a large database results set. The examples show you how to create hyperlinks to pages 1, 2, 3 etc. up to the correct number of pages required to display all the results.
The first three lines of code start all my CGI Perl scripts. The first being the path to Perl including switching on warnings -w. The second line disables buffering on STDOUT which is a good thing for CGI scripts, the third line turns on the strict pragma to restrict unsafe constructs. The next three lines use the additional modules required for this script, then we print the content type statement to the browser.
#!/usr/bin/perl -w
$|++;
use strict;
use DBI;
use CGI;
use POSIX;
print "Content-type: text/html\n\n";
Next we use the CGI.pm module to parse the form contents. Lets assume that we have an html form which has one textbox field called find.
The user enters their search criteria into the textbox and submits the form. The code below demonstrates how to parse the form fields, we are also checking for a value in a parameter called limit which will be used later in the script, if limit has not been defined then we set it equal to zero.
my $q = new CGI;
my $limit = $q->param(limit);
my $find = $q->param(find);
if (!$limit) {$limit = 0;}
Having got the users search criteria in $find we need to connect to the database and perform the search. The snippet below shows a typical DBI connection to a MySQL database. Change the settings as appropriate for your database set-up.
my $dbname = DBI:mysql:YourDatabaseName:localhost;
my $dbusername = YourDatabaseUserName;
my $dbpassword = YourDatabasePassword;
my $dbh = DBI->connect($dbname, $dbusername, $dbpassword)
or die ("Connection to database failed.\n");
We need to count the total number of database records that match the search criteria, so we can calculate the number of pages required. The sample below shows how to perform an SQL search of a table and return the total number of rows into $results.
my $sql = "select tableid from table where field like ?";
my $sth = $dbh->prepare($sql) or die("Error in SQL\n");
$sth->execute($find . %) or die ("Error in SQL\n");
my $results = $sth->rows;
Lets assume $results now has the value of 22, indicating that 22 records match $find. We have already decided that we are going to display 10 results per page, and by using the ceil function of the POSIX module we can calculate the number of pages required. In this example $pagesrequired now has the value of 3, two pages with 10 results and one page with 2 results.
my $results_per_page = 10;
my $pagesrequired = ceil($results / $results_per_page);
Next we re-run the SQL statement using the users search criteria, but this time we use the LIMIT command to restrict the results to 10, starting from the record number highlighted by $limit. The first time the script is called $limit will be equal to zero, so the first 10 results will be displayed from the database.
my $sql = "select * from table where field like ?
limit $limit, $results_per_page";
my $sth = $dbh->prepare($sql) or die("Error in SQL\n");
$sth->execute($find . %) or die ("Error in SQL\n");
... your code to display the
results to the user ...
The last part of the script generates the hyperlinks to pages 1, 2, 3 etc. The code demonstrates how you create links back to the CGI script in this example pages.cgi. So a typical hyperlink for page 2 of your results might look like this pages.cgi?limit=10&find=mysql. This would search the database for mysql from record 10 up to 20.
print "Result Page ";
for (my $i = 0; $i <= $pagesrequired -1; $i++) {
if ($i == 0) {
if ($limit != 0) {
print "<a href=\"pages.cgi?limit=0&find=$find\">";
print $i + 1;
print "</a>";
}
else {print $i + 1;}
}
if ($i > 0) {
if ($limit != ($i * $results_per_page)) {
print " | <a href=\"pages.cgi?limit=";
print ($i * $results_per_page);
print "&find=$find\">\n";
print $i + 1, "</a>";
}
else {print " | ", $i + 1;}
}
}
