create a search form with php and mysql

Having a search feature on your site is very handy for helping users find exactly what they are looking for.

Database Table

CREATE TABLE users (fname VARCHAR(30), lname VARCHAR(30)); 

 INSERT INTO users VALUES ( "Jim", "Jones"), ( "Peggy", "Smith" ),
( "Maggie", "Martin"),( "Tex", "Moncom" ) 

The Search Form

<h2>Search</h2> 
 <form name="search" method="post" action="<?=$PHP_SELF?>">
 Seach for: <input type="text" name="find" /> in 
 <Select NAME="field">
 <Option VALUE="fname">First Name</option>
 <Option VALUE="lname">Last Name</option>
 <Option VALUE="info">Profile</option>
 </Select>
 <input type="hidden" name="searching" value="yes" />
 <input type="submit" name="search" value="Search" />
 </form>

The above HTML code creates the form your users will use to search. It provides a space to enter what they are looking for, and a drop down menu where they can choose what field they are searching (first name, last name or profile.) The form sends the data back to itself using the PHP_SELF () function. This code does not go inside the <?php and ?> tags, but rather above or below them.

The Search Code

 <? 
 //This is only displayed if they have submitted the form 
 if ($searching =="yes") 
 { 
 echo "<h2>Results</h2><p>"; 
 
 //If they did not enter a search term we give them an error 
 if ($find == "") 
 { 
 echo "<p>You forgot to enter a search term"; 
 exit; 
 } 
 
 // Otherwise we connect to our Database 
 mysql_connect("mysql.yourhost.com", "user_name", "password") or die(mysql_error()); 
 mysql_select_db("database_name") or die(mysql_error()); 
 
 // We preform a bit of filtering 
 $find = strtoupper($find); 
 $find = strip_tags($find); 
 $find = trim ($find); 
 
 //Now we search for our search term, in the field the user specified 
 $data = mysql_query("SELECT * FROM users WHERE upper($field) LIKE'%$find%'"); 
 
 //And we display the results 
 while($result = mysql_fetch_array( $data )) 
 { 
 echo $result['fname']; 
 echo " "; 
 echo $result['lname']; 
 echo "<br>"; 
 echo $result['info']; 
 echo "<br>"; 
 echo "<br>"; 
 } 
 
 //This counts the number or results - and if there wasn't any it gives them a little message explaining that 
 $anymatches=mysql_num_rows($data); 
 if ($anymatches == 0) 
 { 
 echo "Sorry, but we can not find an entry to match your query<br><br>"; 
 } 
 
 //And we remind them what they searched for 
 echo "<b>Searched For:</b> " .$find; 
 } 
 ?> 

This is the code that actually does the searching, and can be placed either above or below the HTML form in the file depending on your preference. Although it is explained in the comments, we will explain more about how this works on the next pages.