Mega Code Archive
A template script to provide the ability to get the next or previous n records
<![CDATA[
from a MySQL database.
<?php
require( "mysqlcheck.inc"); /* Check the mysql daemon */
/*
This is a template for a script that will return the next n records from a MySQL
database query. It uses the LIMIT option in the MySQL SELECT syntax
Data Dictionary
* $fgcolour Foreground colour of document
* $bgcolour Background colour of document
* $leftarr Image to use for Previous image
* $rightarr Image to use for Next image
* $database Database to query
* $table Table to query
* $lower Lowest number of the records currently on display
* $upper Highest number of the records currently on display
* $NUM_RECS Number of records to be diplayed per page
* $offset Use for OFFSET value in 'SELECT' statement
* $searchon Field to be searched on
* $searchfor Value to be searched for
*/
function formDropDown($name,$value,$labels) {
$html = "<SELECT NAME=\"$name\">\n";
$key = key($labels);
while($key != "") {
if ($key == $value) {
$selected = "SELECTED";
} else {
$selected = "";
}
$html .= "<OPTION VALUE=\"$key\" $selected>$labels[$key]\n";
next($labels);
$key = key($labels);
}
$html .= "</SELECT>\n";
return $html;
}
/* Some attributes that can be changed */
/* Set fore and background colour values */
$fgcolour = "#009999";
$bgcolour = "#000033";
/* And the images to use for arrows. */
$leftarr = "/gifs/cyan_left.gif";
$rightarr = "/gifs/cyan_right.gif";
/* Name of the database and table to use for the query */
$database = "database";
$table = "table";
?>
<HTML>
<HEAD><TITLE>Title</TITLE></HEAD>
<BODY BGCOLOR=" <?php echo $bgcolour?>" TEXT=" <?php echo $fgcolour?>">
<H1>Search</H1>
Search for references.
<P>
<HR>
<FORM ACTION=" <?php echo $PHP_SELF?>" METHOD="POST">
<CENTER>
<TABLE ALIGN=center CELLSPACING="15" BORDER=0>
<!-- Build your input form here -->
<TR><TD ALIGN="CENTER">View the results
<?php
/* This builds a dropdown list to provide the choices for number
of records to display per page
*/
$labels = array( "5"=> "5", "10"=> "10", "15"=> "15", "20"=> "20");
$match = (empty($NUM_RECS) ? "5" : $NUM_RECS);
$box = formDropDown( "NUM_RECS",$match,$labels);
echo $box;
?>
per page
<TD ALIGN=center><INPUT TYPE=submit NAME="search" VALUE="Search">
<TD ALIGN=center><INPUT TYPE=reset></TD>
</TABLE></CENTER>
</FORM>
<HR>
<?php
/* First time through on a new search $offset should be zero,
$lower should be one and $upper should be $NUM_RECS
*/
if(empty($lower)):
$lower = 1;
endif;
if(empty($upper)):
$upper = $NUM_RECS;
endif;
if(empty($offset)):
$offset = 0;
endif;
if(!empty($next_x)): /* The Next button has been selected */
$lower += $NUM_RECS;
$upper += $NUM_RECS;
$offset += $NUM_RECS;
endif;
if(!empty($prev_x)): /* The Previous button has been selected */
$lower -= $NUM_RECS;
$upper -= $NUM_RECS;
$offset -= $NUM_RECS;
endif;
/* echo "Offset: " . $offset . ": Lower: " . $lower . ": Upper: " . $upper . "<BR>"; */
switch($search):
case "Search":
/* Build the query string from the data passed in. */
$NUM_RECS = intval($NUM_RECS);
$query = "SELECT * FROM " . $table . " WHERE " . $searchon;
$query .= " LIKE '%" . $searchfor . "%' LIMIT " . ($offset != 0 ? "$offset, " : "$NUM_RECS");
/* Countquery gets the number of records that are be retuned by the 'real' query */
$countquery = "SELECT count(*) AS numrows FROM " . $table. " WHERE " . $searchon;
$countquery .= " LIKE '%" . $searchfor . "%'";
$countresult = mysql_db_query($database, $countquery);
$countrow = mysql_fetch_array($countresult);
$result = mysql_db_query($database, $query);
$numrows = intval($countrow[0]);
?>
<IMG SRC="/gifs/searchresults.gif" ALT="Search Results" WIDTH="480" HEIGHT="15"><P>
<?php
switch($numrows):
case 0:
echo "There were no matches to your search for <FONT COLOR=\"#FF0000\">" . $searchfor;
echo "</FONT> in " . $searchon . ". You may wish to try again with a different search phrase.";
break;
default:
/* (!empty($offset) ? $lower = ($offset - $NUM_RECS) + 1 : $lower = 1);
$upper = $offset + $NUM_RECS; */
echo "<FONT FACE=\"LucidaTypewriter, Arial, Helvetica\">";
if($numrows <= $NUM_RECS):
echo "Displaying all ". $numrows;
else:
echo "Displaying matches " . $lower . " to " . ($upper > $numrows ? $numrows : $upper). "
of " . $numrows;
endif;
echo " matches for '<FONT COLOR=\"#FFFFFF\">" . $searchfor . "</FONT>'</FONT>";
while($row = mysql_fetch_array($result)):
?>
<P>
<! Lay out your returned values here however you like -->
<HR>
<?php
endwhile;
break;
endswitch; /* Numrows */
break;
default: /* No search - throw up the search entry form */
endswitch; /* Search */
if($numrows > $NUM_RECS):
?>
<CENTER>
<FORM ACTION=" <?php echo $PHP_SELF?>" METHOD="POST">
<TABLE ALIGN="CENTER"><TR VALIGN=MIDDLE CELLSPACING="10">
<TD WIDTH="31">
<?php if($lower != 1): /* Show the left arrow and text */ ?>
<INPUT NAME="prev" TYPE=image SRC=" <?php echo $leftarr?>" BORDER="0" WIDTH="31" HEIGHT="31" ALT="<<=">
<TD><FONT COLOR=" <?php echo $fgcolour?>">
<?php else:?>
<TD><FONT COLOR=" <?php echo $bgcolour?>">
<?php endif;?>
Previous <?php echo $NUM_RECS?> matches</FONT>
????????????
<TD>????????????
<?php if($upper < $numrows): /* Show the right arrow and text */ ?>
<FONT COLOR=" <?php echo $fgcolour?>">Next <?php echo $NUM_RECS?> matches</FONT><TD WIDTH="31">
<INPUT NAME="next" TYPE=image SRC=" <?php echo $rightarr?>" BORDER="0" WIDTH="31" HEIGHT="31" ALT="=>>">
<?php else:?>
<FONT COLOR=" <?php echo $bgcolour?>">Next <?php echo $NUM_RECS?> matches</FONT><TD WIDTH="31">
<?php endif;?>
</TABLE>
<!-- Use the hidden fields to pass the search criteria, current location, number
of records etc to the next form -->
<INPUT TYPE="HIDDEN" NAME="searchon" VALUE=" <?php echo $searchon?>">
<INPUT TYPE="HIDDEN" NAME="searchfor" VALUE=" <?php echo $searchfor?>">
<INPUT TYPE="HIDDEN" NAME="NUM_RECS" VALUE=" <?php echo $NUM_RECS?>">
<INPUT TYPE="HIDDEN" NAME="search" VALUE=" <?php echo $search?>">
<INPUT TYPE="HIDDEN" NAME="offset" VALUE=" <?php echo $offset?>">
<INPUT TYPE="HIDDEN" NAME="upper" VALUE=" <?php echo $upper?>">
<INPUT TYPE="HIDDEN" NAME="lower" VALUE=" <?php echo $lower?>">
</FORM>
</CENTER>
<?php
endif;
?>
</BODY>
</HTML>
]]>