Hi,
Many days you can come around a Situation where you want to search the database for a particular string. Like you may want to list all the information from your database where user name contains “am”. Like “Ram” , “Shyam”, “Balaram” etc.
So how do you implement it? Lets see,
Pre-requisites: PHP, MySQL and a Web Server capable of parsing PHP pages, like Apache or IIS. This search script does not spider all your pages by crawling the links, so the content you want to be searchable must be within the database. Knowledge of PHP and MySQL is also necessary because this script is just the bones of a working solution. Numerous edits must take place upon this script before it is a usable solution.
Lets get started.
Just after the <body> 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. Follow the PHP comments for what the script is doing, if you get stuck, tell us in the forums!
<?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>";
exit;
}
// check for a search parameter
if (!isset($var)){
echo "<p>We dont seem to have a search parameter!</p>";
exit;
}
//connect to your database ** EDIT REQUIRED HERE **
mysql_connect("localhost","username","password"); //(host, username, password)
//specify database ** EDIT REQUIRED HERE **
mysql_select_db("database") or die("Unable to select database");
//select which database we're using
// 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 "<h4>Results</h4>";
echo "<p>Sorry, your search: "" . $trimmed . "" returned zero results</p>";
// google
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>";
}
// 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 "<p>You searched for: "" . $var . ""</p>";
// 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 "<br />";
// next we need to do the links to other results
if ($s>=1) { // bypass PREV link if s is 0
$prevs=($s-$limit);
print " <a href=\"$PHP_SELF?s=$prevs&q=$var\"><<
Prev 10</a> ";
}
// 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 " <a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 >></a>";
}
$a = $s + ($limit) ;
if ($a > $numrows) { $a = $numrows ; }
$b = $s + 1 ;
echo "<p>Showing results $b to $a of $numrows</p>";
?>
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.
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.
Lets Look at another example:
- trim() - Strip whitespace (or other characters) from the beginning and end of a string
- explode() - Split a string by string
- array_unique() - Removes duplicate values from an array
- preg_replace() - Perform a regular expression search and replace
Other functions used include common database access functions like mysql_connect, mysql_num_rows, etc. Please see code comments for more detailed explanation of the script. If you have questions or suggestions please use the comment box on the bottom of the page. Open your favorite editor and create search.php file as shown below.
| <?php //This is a working script //Make sure to go through it and edit database table filelds that you are seraching //This script assumes you are searching 3 fields $hostname_logon = "localhost" ; //store record id of every item that contains the keyword in the array we need to do this to avoid display of duplicate search result. if($row_num_links_main == 0 && $row_set_num == 0){ <form action="search.php" method="get" name="search"> <?php //now let's make the keywods bold. To do that we will use preg_replace function. |
Filed under: Scripts | Tagged: database searching, mysql, php, querying database, search, select, sql
I can’t get the first script to work. I’ve been trying to tweak this for ages but all I can get is the first page of results from my database. If I click on the next link it says s=10 and q=whatever query has been entered in the search box but it still gives me the frist page of results. Any help on this would be much appreciated. I haven’t tried the second and supposedly working script.
Hi “mrsyardbroom”
All the trick is in the limit query
$query = “select * from the_table where 1st_field like \”%$trimmed%\” order by 1st_field”;
$query .= ” limit $s,$limit”;
so the final query becomes:
$query = “select * from the_table where 1st_field like \”%$trimmed%\” order by 1st_field limit $s,$limit”;
Now, after getting $s and $limit (I think it is set to 10) you first try to execute the query in your database to see if it returns exact results are not.
If it returns result as expected then see the code for any bugs.
I hope you will find your way.