[PHP] Re: displaying database results with forward and back buttons

[John Taylor-Johnston]

Finally something I can give back, made by my little lonesome with no help :)

> I have a query that returns lots of rows so I want to display the results in blocks 
> of 25 or so on my web page and have forward and back buttons to navigate the results.

include it first:

include("settings_limit.inc");

then set $offset and $limit in your sql

$sql = 'SELECT *,MATCH (field1,field2)
AGAINST (\''.$searchenquiry.'\' IN BOOLEAN MODE)
AS relevancy FROM '.$table.'
WHERE MATCH (field1,field2)
AGAINST (\''.$searchenquiry.'\' IN BOOLEAN MODE)
ORDER BY relevancy DESC
LIMIT '.$offset.','.$limit.';';

------settings_limit.inc--------
<?php

#####################################################
## $mycounter is set by script calling settings_limit.inc ####
#####################################################

#####################################################
############### Set $offset & $limit ################
#####################################################

if((!$offset) || ($offset < 0))
{
$offset = 0;
}
$limit = 25;

#####################################################
############### $nextinsert #########################
#####################################################
$new_offset = $offset + $limit;
$disp = $limit;
if ($new_offset + $limit > $mycounter)
{
  $disp = $mycounter - $new_offset;
}
if ($disp > 0)
{
$nextinsert  = "<form ACTION=\"".$SCRIPT_NAME."\" TARGET=\"_top\" 
METHOD=\"POST\"><td>Next ".$disp." Requests <input type=\"submit\" name=\"submit\" 
value=\">>\"><input type=\"hidden\" name=\"offset\" value=\"".$new_offset."\"><input 
type=\"hidden\" name=\"table\" value=\"".$table."\"><input type=\"hidden\" name=\"db\" 
value=\"".$db."\">";
 if($searchenquiry)
  $nextinsert  .= "<input type=\"hidden\" name=\"searchenquiry\" 
value=\"".stripslashes(htmlspecialchars($searchenquiry))."\">";
 if($scholarsenquiry)
  $nextinsert  .= "<input type=\"hidden\" name=\"scholarsenquiry\" 
value=\"".$scholarsenquiry."\">";
 if($titlesenquiry)
  $nextinsert  .= "<input type=\"hidden\" name=\"titlesenquiry\" 
value=\"".$titlesenquiry."\">";
 $nextinsert  .= "</td></form>";
}
#####################################################
############### $previousinsert #####################
#####################################################

$new_offset2 = $offset - $limit;
if ($offset > 0)  // can display previous msg
{
 $disp = $limit;
 $previousinsert  = " <font face=\"arial\" size=2><A 
HREF=\"index.html?submit=submit&offset=".$new_offset2."&table=$table&db=$db\">&lt;&lt; 
Previous ".$disp." Requests</a></font>";

$previousinsert  = "<form ACTION=\"".$SCRIPT_NAME."\" TARGET=\"_top\" 
METHOD=\"POST\"><td><input type=\"submit\"  name=\"submit\" value=\"<<\"> Previous 
".$disp." Requests<input type=\"hidden\" name=\"offset\" 
value=\"".$new_offset2."\"><input type=\"hidden\" name=\"table\" 
value=\"".$table."\"><input type=\"hidden\" name=\"db\" value=\"".$db."\">";
 if($searchenquiry)
  $previousinsert  .= "<input type=\"hidden\" name=\"searchenquiry\" 
value=\"".stripslashes(htmlspecialchars($searchenquiry))."\">";
 if($scholarsenquiry)
  $previousinsert  .= "<input type=\"hidden\" name=\"scholarsenquiry\" 
value=\"".$scholarsenquiry."\">";
 if($titlesenquiry)
  $previousinsert  .= "<input type=\"hidden\" name=\"titlesenquiry\" 
value=\"".$titlesenquiry."\">";
 $previousinsert  .= "</td></form>";
}
#####################################################
############### $lastinsert #########################
#####################################################

$new_offset3 = $mycounter - $limit;

if (($new_offset3 > $limit) and ($offset != $mycounter - $limit))  // can display goto 
end msg
{
$lastinsert  = "<form ACTION=\"".$SCRIPT_NAME."\" TARGET=\"_top\" 
METHOD=\"POST\"><td>Go To End <input type=\"submit\"  name=\"submit\" 
value=\">>|\"><input type=\"hidden\" name=\"offset\" value=\"".$new_offset3."\"><input 
type=\"hidden\" name=\"table\" value=\"".$table."\"><input type=\"hidden\" name=\"db\" 
value=\"".$db."\">";
 if($searchenquiry)
  $lastinsert  .= "<input type=\"hidden\" name=\"searchenquiry\" 
value=\"".stripslashes(htmlspecialchars($searchenquiry))."\">";
 if($scholarsenquiry)
  $lastinsert  .= "<input type=\"hidden\" name=\"scholarsenquiry\" 
value=\"".$scholarsenquiry."\">";
 if($titlesenquiry)
  $lastinsert  .= "<input type=\"hidden\" name=\"titlesenquiry\" 
value=\"".$titlesenquiry."\">";
 $lastinsert  .= "</td></form>";
}
#####################################################
############### $firstinsert ########################
#####################################################

$new_offset4 = $mycounter - $limit;
if ($new_offset4 > $limit)  // can display goto beginning msg
{
$new_offset4 = 0;
$firstinsert  = "<form ACTION=\"".$SCRIPT_NAME."\" TARGET=\"_top\" 
METHOD=\"POST\"><td><input type=\"submit\" name=\"submit\" value=\"|<<\"> Go To 
Beginning<input type=\"hidden\" name=\"offset\" value=\"".$new_offset4."\"><input 
type=\"hidden\" name=\"table\" value=\"".$table."\"><input type=\"hidden\" name=\"db\" 
value=\"".$db."\">";
 if($searchenquiry)
  $firstinsert .= "<input type=\"hidden\" name=\"searchenquiry\" 
value=\"".stripslashes(htmlspecialchars($searchenquiry))."\">";
 if($scholarsenquiry)
  $firstinsert .= "<input type=\"hidden\" name=\"scholarsenquiry\" 
value=\"".$scholarsenquiry."\">";
 if($titlesenquiry)
  $firstinsert .= "<input type=\"hidden\" name=\"titlesenquiry\" 
value=\"".$titlesenquiry."\">";
 $firstinsert .= "</td></form>";
}


#####################################################
############### Display Inserts #####################
#####################################################

#####################################################
if (($previousinsert) or ($nextinsert))
#echo "<center><table border=0 cellspacing=\"5\" cellpadding=\"0\">";
echo "<table border=0 cellspacing=\"5\" cellpadding=\"0\">";

#if (($previousinsert) || ($nextinsert))
#echo "<caption ALIGN=BOTTOM><small>(Sorted by id - $mycounter records found 
total)</small></caption>";

if (($previousinsert) or ($nextinsert))
echo "<tr>";

if (($firstinsert) and ($offset != 0)) echo "$firstinsert";

if ($previousinsert) echo "$previousinsert";

if (($previousinsert) && ($nextinsert)) echo "<td> | </td>";

if ($nextinsert) echo "$nextinsert";

if ($lastinsert) echo "$lastinsert";

if (($previousinsert) or ($nextinsert))
#echo "</tr></table></center>";
echo "</tr></table>";

?>

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php