Sr. Web Developer
mediabistro.com
US-NY-New York

Justtechjobs.com Post A Job | Post A Resume

Building Next/Prev Buttons for Query Results
Rod Kreisler picture of Rod
There have been numerous requests on the Support Forums recently asking how to do "NEXT 1 2 3 4 5 PREV" type links from a search result. I hope the following script will help you add this functionality to your search result pages. This example is written for MySQL but can be adapted quite easily to other SQL engines.
Since each application is different I've used some generic statements for the MySQL queries. TABLE should be replaced with your table name. YOUR CONDITIONAL HERE should be replaced by your where conditions and WHATEVER should be replaced by the column you wish to order your results (don't forget to add DESC if applicable to your needs).

<?php

$limit=20; // rows to return
$numresults=mysql_query("select * from TABLE where YOUR CONDITIONAL HERE order by WHATEVER");
$numrows=mysql_num_rows($numresults);

// next determine if offset has been passed to script, if not use 0
if (empty($offset)) {
    $offset=1;
}

// get results
$result=mysql_query("select id,name,phone ".
    "from TABLE where YOUR CONDITIONAL HERE ".
    "order by WHATEVER limit $offset,$limit");

// now you can display the results returned
while ($data=mysql_fetch_array($result)) {
    // include code to display results as you see fit
}

// next we need to do the links to other results

if ($offset==1) { // bypass PREV link if offset is 0
    $prevoffset=$offset-20;
    print "<a href=\"$PHP_SELF?offset=$prevoffset\">PREV</a> &nbsp; \n";
}

// calculate number of pages needing links
$pages=intval($numrows/$limit);

// $pages now contains int of pages needed unless there is a remainder from division
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=\"$PHP_SELF?offset=$newoffset\">$i</a> &nbsp; \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=\"$PHP_SELF?offset=$newoffset\">NEXT</a><p>\n";
}

?>
That should do the trick for you. Of course, you'll probably want to clean up the HTML output...
Also, note that the links to $PHP_SELF only include the $offset. If you need to pass parameters for the where conditional of your query, you'll need to tack those on as well.
--Rod