Creating a Form that will Search a MySQL Database
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.
Related Posts
Make money from your website with Ads4dough. The only Team Tutorials approved Affiliate Network!
on July 23rd, 2008 at 9:32 am
wow! its amazing.
on August 19th, 2008 at 3:45 pm
Thanks mate simple yet powerful I dont know what I’d do if it wasn’t for guys like you who take time out to help others
on August 22nd, 2008 at 8:24 am
Really, the form works how writes this tutorial. Many thanks to the author.
on August 25th, 2008 at 5:48 am
Ohhhhh!
This is really working.
on August 27th, 2008 at 4:36 am
yeahh..it really helped me a lot, thanks to the author.
on August 31st, 2008 at 12:37 am
how can i change the text style of the database results? do i need to name the page with the search form as .php or .html? thanks.
on September 6th, 2008 at 9:09 pm
Damn this works like a charm! Many thnx for the creator of this code
on September 9th, 2008 at 7:16 am
Hi,
Can anyone tell me how can i search in this above mentioned table with a full name, not just by giving first name or last name..
ex: search criteria - “Bob Jhonson”
on October 25th, 2008 at 10:04 am
Hello
As others before has pointed out, this is really a great simple explanations.
I would like to design a searchable form for an Estate Agency whereby a person can look for example:
(House, bungalow, appartment, studio) with price range and locations.
I would be much grateful, if a great soul could post an explanation how to do this. Thanks.
on October 27th, 2008 at 5:47 pm
Спасибо, очень понравилось!
on November 9th, 2008 at 10:32 am
thanks!!