Search a MySQL database and return paged results with PHP

Posted under » PHP » MySQL on 11 April 2009

Got a MySQL database with content within it? Want to be able to search it? Read on...

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. Note the method is "get" and not "post".

Now, enter the following PHP. Call it search.php

<¿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 "
Please enter a search...
";
  exit;
  }

// check for a search parameter
if (!isset($var))
  {
  echo "
We dont seem to have a search parameter!
";
  exit;
  }

include('connect.php'); // please see 041.php

// Build SQL Query  
$query = "select * from the_table where 1st_field like \"%$trimmed%\"  
  order by 1st_field"; // EDIT HERE and specify your table and field names for the SQL query

 $numresults=mysql_query($query);
 $numrows=mysql_num_rows($numresults);

// If we have no results, offer a google search as an alternative

if ($numrows == 0)
  {
  echo "
Results
";
  echo "
Sorry, your search: "" . $trimmed . "" returned zero results
";

// google
 echo "
Click here to try the 
  search on google
";
  }

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

// get results
  $query .= " limit $s,$limit";
  $result = mysql_query($query) or die("Couldn't execute query");

// display what the person searched for
echo "
You searched for: "" . $var . ""
";

// begin to show results set
echo "Results";
$count = 1 + $s ;

// now you can display the results returned
  while ($row= mysql_fetch_array($result)) {
  $title = $row["1st_field"];

  echo "$count.) $title" ;
  $count++ ;
  }

$currPage = (($s/$limit) + 1);

//break before paging
  echo "
";

  // next we need to do the links to other results
  if ($s>=1) { // bypass PREV link if s is 0
  $prevs=($s-$limit);
  print " << 
  Prev 10  ";
  }

// 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++;
  }

// check to see if last page
  if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {

  // not last page so give NEXT link
  $news=$s+$limit;

  echo " Next 10 >>";
  }

$a = $s + ($limit) ;
  if ($a > $numrows) { $a = $numrows ; }
  $b = $s + 1 ;
  echo "
Showing results $b to $a of $numrows
";
  
?>

Three major areas are covered in this script, the first is selecting data from the database which matches your entered keyword, the second is displaying the results on the web page and the last is generating the paging, which displays results in chunks of 10 with next/previous links where they are necessary.

The mysql_num_rows command is particularly important in pagination because it tells you how many rows of the record are selected. Read this updated article.

Important: This script requires numerous edits before it can be adapted for use. The SQL query in this example is only selecting 1 field from a hypothetical database which doesn't pre-exist. Further down the script, the value of that field is being displayed on the page. The major amendments that need to be performed are 1.) The SQL statement, and 2.) The PHP which displays the results.