|
Creating Next-Prev link in Perl/MySQL |
I will show you an example how we can display limited results with "Next|Previuos" links or Paging in Perl/Mysql.
you can use the same logic for your script.
####navigate.cgi#######
#!/usr/bin/perl
use DBI;
use CGI;
$q=new CGI;
print $q->header;
$dbh=DBI->connect('dbi:mysql:database','usr','pwd');
$sql="select * from login";
# first get the total number of records from the database.
$sth = $dbh->prepare($sql);
$numrows = $sth->execute;
$offset=$q->param('offset');
$limit=5;
#total records 5
#next determine if offset has been passed to script, if not use 0
if (length($offset)==0) {
$offset=1;
}
$query="select * from login order by usr limit $offset,$limit";
$sth= $dbh->prepare($query);
$rv= $sth->execute;
print "<table>n";
print "<tr><td>Username</td><td>Password</td></tr>n";
while(@row = $sth->fetchrow_array) {
#print your result here
print "<tr><td>n";
print $row[0]."</td><td>n";
print $row[1];
print "</td></tr>n";
}
print "</table>n";
# calculate number of pages needing links
$pages=int($numrows/$limit);
if ($numrows%$limit) {
#has remainder so add one page
$pages++;
}
for ($i=1;$i<=$pages;$i++) {
#loop thru
$newoffset=$limit*($i-1);
print "<a href="navigate.cgi?offset=$newoffset">$i</a> n";
}
#check to see if last page
if (!(($offset/$limit)==$pages) && $pages!=1) {
# not last page so give NEXT link
$newoffset=$offset+$limit;
print "<a href="navigate.cgi?offset=$newoffset">NEXT</a><p>n";
}
if ($offset>0) {
# bypass PREV link if offset is 0
$prevoffset=$offset-5;
print "<a href="navigate.cgi?offset=$prevoffset">PREV</a>n";
}
Happy Programming!
|
|
Author: S Rajan |
|
Send any comments at: info@lizratechnologies.com
|
