[Next] and [Prev] links in MySQL

I have a large database of articles that need to be listed in terms of their author, title and date submitted. I can do this much already, but this list could quickly turn into one that is 50 or 200 long. So I need to sort the page by date (oldest first), display only 15 or so per page, and have "next" and "previous" links at the bottom to go to the next screen of articles.

I know you can use the LIMIT clause in a select statement, but I'm not sure how to use it in this context, to have a dynamically generated page a certain way through the list. Some kind of autoincrementation within the link perhaps? Any help would be gratefully received.

When you write your server-side script to output the returned recordset, find out the total records. This, divided by how many you wish to show per page (n), will tell you how many pages you will have (so you can provide the appropriate number of links in case the user wants to jump ahead x many pages). Then as you step through the sorted recordset, only display the first (n) records. If the user requests page 3, step through the recordset 2*(n) times, and then only display the records up to 3*(n) times, etc.

How would I find the total number of articles in the db? Considering this is a temporary pool of articles, so I can't just look at the highest article's number and use that. Is there a command to fetch the number of rows in a table?

Hi,

Here is a sample script for you .just alter the logic as per your requirment.



####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 per page5 (display per page)

#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";
}



Good Luck!!

Thanks for the help! Sometimes I am really stupid; I didn't think of loading fetchrow_array() into a scalar variable to give the number of rows. The next and prev links bits I wouldn't have come up with though. I guess that comes with experience in using Perl!

Hi!

If you replace


by



you take advantage of having mysql handle the counting and simply get the single number of total results as your result.

(This is just a performance issue, no real other improvement.)

Greetings

Atrus.

<<
you take advantage of having mysql handle the counting and simply get the single number of total results as your result.
>>



yea,It is a nice suggestion.