Creating a Form that will Search a MySQL Database

by

You should not use this code on a production website.

Warning: This tutorial uses old techniques. It is insecure and will leave your server vulnerable to SQL Injection attacks.This tutorials also uses mysql_ functions that are no longer support. For updated tutorials look for a PDO or MySQLi tutorial.This post will be delete or revised in the future.

In this tutorial I will show you how to add a simple search box to query a MySQL database. This tutorial is a continuation of the “How to Access a MySQL Database Using PHP” tutorial. Since writing that original tutorial I have created a local WAMP test environment. I have created the database “test” with a table “testable”. The table contains the following fields:

I have added some sample data to this table.

For our search to work we will have to create two files. One file will be the PHP script to search our form and the other will be an HTML page containing our form and passing the search variable to our PHP file.
I will start by create an search.htm file. Below you will see the basic structure of our simple HTML page.

<html>
	<head>
		<title>Search the Database</title>
	</head>
	
	<body>
	
		
	
	</body>
</html>

Next we will add the form. Notice that the action=”search.php” and the method=”post”. This is basically telling the web server to send Post variables to the search.php page. Another important thing to note is the input box name field. This field, name=”term” , this will pass whatever is in the input box on as a post variable named “term”. We also add a submit button.

<html>
	<head>
		<title>Search the Database</title>
	</head>

	<body>

	<form action="search.php" method="post">
  	 Search: <input type="text" name="term" /><br />
    <input type="submit" name="submit" value="Submit" />
	</form>


	</body>
</html>

Now on to search.php . The first thing I did was to simple echo the post variable to make sure the information is getting passed from the search form.


<?php
      echo $_POST['term'];
?>

If you variable is not being displayed then something is wrong. If your search term is displayed then you can move on. We can delete the echo code from our php file now.
First make a connection to your database


<?php

mysql_connect ("localhost", "testuser","password")  or die (mysql_error());

?>

Now select your test database


<?php

mysql_connect ("localhost", "testuser","password")  or die (mysql_error());
mysql_select_db ("test");

?>

Next we are going to store the post variable as $term and build our query. As you can see we are searching the first name field. We are searching for a first name like %$term%. The % character is a wild card for 0 or more charcters. So if we had ‘bob’ in our database and we entered the ‘bob’ in the search box it would return the results. With the % charcters around the term we could also search for ‘ob’ and it would return the results for bob, and any other name containing ‘ob’.

<?php
mysql_connect ("localhost", "testuser","password")  or die (mysql_error());
mysql_select_db ("test");

$term = $_POST['term'];

$sql = mysql_query("select * from testtable where FName like '%$term%'");

?>

The next step is to execute the query and display the results.

<?php
mysql_connect ("localhost", "testuser","password")  or die (mysql_error());
mysql_select_db ("test");

$term = $_POST['term'];

$sql = mysql_query("select * from testtable where FName like '%$term%'");

while ($row = mysql_fetch_array($sql)){
	echo 'ID: '.$row['ID'];
	echo '<br/> First Name: '.$row['FName'];
	echo '<br/> Last Name: '.$row['LName'];
	echo '<br/> Phone: '.$row['Phone'];
	echo '<br/><br/>';
	}

?>

Now I will test it out. Since I know ‘Bob’ is in the database I will search for him.

If all goes well you should have these results returned/

So we can search for First names only. If you want to give the user the ability to enter either a first name or a last name change your query to this:

$sql = mysql_query("select * from testtable where FName like '%$term%' or LName like '%$term%' ");

To test that query I did a simple search for the letter ‘o’. Which should return several first names and last names that contain the letter.

So there you have it a simple basic database search form. I hope this all makes sense, I am writing this at 4:00am and am a bit tired. As always feel free to ask questions.