query results across multiple pages

[bardley]

Hi.

I have a fairly large and expensive SELECT query that I run against an oracle 8 database. It can return anywhere from 0 to many thousands of rows. I would only like to display "n" events per page, and create a NEXT PAGE button to fetch the next "n" results.

How is this done without re-running the query on the next page?

WebServer: linux, apache, php4.06
DB: Oracle 8, Solaris 8 (?) Brad Gunsalus
Cymtec Systems, Inc.
bgunsalus@cymtec.com

 

[phpPete]

<?

//Number of records to show per page
$limit = 5;

//Set the default offset = 0
$offset = ($offset) ? $offset : 0;

//Encoded query string that needs to be passed to each page
$query = &quot;&var=&quot;.urlencode($var);


$db = mysql_connect(&quot;&quot;, &quot;&quot;, &quot;&quot or DIE(mysql_error());
mysql_select_db(&quot;&quot;

$r1 = mysql_query(&quot;SELECT COUNT(*) from <<TABLENAME>> WHERE <<FIELD>> LIKE '$var%'&quot or die(mysql_error());
$totalnum = mysql_result($r1, 0, 0);


$r2 = mysql_query(&quot;SELECT * from <<TABLENAME>> WHERE <<FIELD>> LIKE '$var%' LIMIT $offset, $limit&quot or die(mysql_error());
while($row = mysql_fetch_array($r2)) {
extract($row);
print &quot;<<FIELD>><br>&quot;;
}

print '<br>';
freddyShowNav($offset, $limit, $totalnum, $query);


function freddyShowNav($offset, $limit, $totalnum, $query) {
global $PHP_SELF;
if ($totalnum > $limit) {
// calculate number of pages needing links
$pages = intval($totalnum/$limit);

// $pages now contains int of pages needed unless there is a remainder from division
if ($totalnum%$limit) $pages++;

if (($offset + $limit) > $totalnum) {
$lastnum = $totalnum;
}
else {
$lastnum = ($offset + $limit);
}
?>
<table cellpadding=&quot;4&quot;><tr><td>Page </td>
<?
for ($i=1; $i <= $pages; $i++) { // loop thru
$newoffset=$limit*($i-1);
if ($newoffset != $offset) {
?>
<td><a href=&quot;<?=$PHP_SELF?>?offset=<?=$newoffset?><?=$query?>&quot;><?=$i?></a></td>
<?
}
else {
?>
<td><?=$i?></td>
<?
}
}
?>
</tr></table>
<?
}
return;
}