Search script MySQL: paging next doesn't work
Het probleem van de paging next
Heb jij ook onderstaand PHP script gebruikt voor het zoeken in jouw database en heb jij ook problemen met de knoppen 'volgende' en 'vorige'? Dan ben je niet de enige. De code is verder perfect, maar alleen de eerste 10 resultaten worden weergegeven. Het heeft mij een paar uur gekost, maar de oplossing is erg eenvoudig!
The script
Just after the
tag of your page, place the following HTML. This is for the form which will contain the textfield to enter our search string in.<form name="form" action="search.php" method="get"> <input type="text" name="q" /> <input type="submit" name="Submit" value="Search" /> </form>
Now, enter the following PHP.
// Get the search variable from URL
$var = @$_GET['q'] ;
$trimmed = trim($var); //trim whitespace from the stored variable
// rows to return
$limit=10;
// check for an empty string and display a message.
if ($trimmed=="")
{
echo "<p>Please enter a search...</p>";
<p>exit;
</p><p>}
</p><p>// check for a search parameter
</p><p>if (!isset($var))
</p><p>{
</p>
echo "<p>We dont seem to have a search parameter!</p>";
<p>exit;
</p><p>}
</p><p>//connect to your database ** EDIT REQUIRED HERE **
</p><p>mysql_connect("localhost","username","password"); //(host, username, password)
</p><p>//specify database ** EDIT REQUIRED HERE **
</p><p>mysql_select_db("database") or die("Unable to select database"); //select which database we're using
</p><p>// Build SQL Query
</p><p>$query = "select * from the_table where 1st_field like \"%$trimmed%\"
</p><p>order by 1st_field"; // EDIT HERE and specify your table and field names for the SQL query
</p><p>$numresults=mysql_query($query);
</p><p>$numrows=mysql_num_rows($numresults);
</p><p>// If we have no results, offer a google search as an alternative
</p><p>if ($numrows==0)
</p><p>{
</p>
echo "<h4>Results</h4>";
echo "<p>Sorry, your search: "" . $trimmed . "" returned zero results</p>";
<p>// google
</p>
echo "<p><a href=\"http://www.google.com/search?q="
. $trimmed . "\" target=\"_blank\" title=\"Look up
" . $trimmed . " on Google\">Click here</a> to try the
search on google</p>";
<p>}
</p><p>// next determine if s has been passed to script, if not use 0
</p><p>if (empty($s)) {
</p><p>$s=0;
</p><p>}
</p><p>// get results
</p><p>$query .= " limit $s,$limit";
</p><p>$result = mysql_query($query) or die("Couldn't execute query");
</p><p>// display what the person searched for
</p>
echo "<p>You searched for: "" . $var . ""</p>";
<p>// begin to show results set
</p><p>echo "Results";
</p><p>$count = 1 + $s ;
</p><p>// now you can display the results returned
</p><p>while ($row= mysql_fetch_array($result)) {
</p><p>$title = $row["1st_field"];
</p><p>echo "$count.) $title" ;
</p><p>$count++ ;
</p><p>}
</p><p>$currPage = (($s/$limit) + 1);
</p><p>//break before paging
</p><p>echo "
";
</p><p>// next we need to do the links to other results
</p><p>if ($s>=1) { // bypass PREV link if s is 0
</p><p>$prevs=($s-$limit);
</p><p>print " <a href=\"$PHP_SELF?s=$prevs&q=$var\"><<
</p><p>Prev 10</a>  ";
</p><p>}
</p><p>// calculate number of pages needing links
</p><p>$pages=intval($numrows/$limit);
</p><p>// $pages now contains int of pages needed unless there is a remainder from division
</p><p>if ($numrows%$limit) {
</p><p>// has remainder so add one page
</p><p>$pages++;
</p><p>}
</p><p>// check to see if last page
</p><p>if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {
</p><p>// not last page so give NEXT link
</p><p>$news=$s+$limit;
</p><p>echo " <a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 >></a>";
</p><p>}
</p><p>$a = $s + ($limit) ;
</p><p>if ($a > $numrows) { $a = $numrows ; }
</p><p>$b = $s + 1 ;
</p>
echo "<p>Showing results $b to $a of $numrows</p>";
<p>?>
</p>
De oplossing
In dit script is een bug in de $s-string. De $s is niet gedefinieerd. Dit is eenvoudig te verhelpen. Voeg de volgende code aan het script toe:
$s = $_GET['s'] ;
Dit stukje code, gaat helemaal bovenin de code, zoals:
// Get the search variable from URL
$var = @$_GET['q'] ; $s = $_GET['s'] ;
$trimmed = trim($var); //trim whitespace from the stored variable
This will do the trick! Veel plezier!