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.
Popularity: 17% [?]
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!!
on November 25th, 2008 at 12:36 pm
i looked everywhere and this is the only solution that worked!
THANKS!!!
on November 26th, 2008 at 6:27 am
Hello,
On the html/css page, the code calls for the search.php file and on the search.php file is where the results are displayed. Can you give an example where the result is displayed in the html/css page instead of the results staying on the search.php?
Thanks
on December 29th, 2008 at 4:55 am
Hi,
This is a great search form and has been a learning curve for me
I’ve managed to change this to work with MS Access, but i would like
to know is it possible to put this into a table with the field names
running across the top and the correct data below in rows….
Any help would be great
Kind Regards,
Kev
on January 1st, 2009 at 1:41 am
thanks a lot
on January 27th, 2009 at 11:43 am
The code works fine. But, like to have the code that also gives a “Name not found” message if the Name is not present in the database.
Can any one help?
on February 13th, 2009 at 10:11 am
Hello
This is a great tutorial and exactly what I have been looking for,although I am having a problem.At the very start of the tutorial you say to create a HTML file that links into the search.php file just to make sure that the variable entered into the search bar is displayed,well it won`t display my variable entered -it opens a new tab which remains blank.I know that it is connecting to the search.php page beacuse when I change form action=search.php in the HTML to form action = searc.php it tells me that there is an error where as when I enter the correct file name it remains blank-any ideas?
Thanks alot
on February 25th, 2009 at 1:38 am
thanx man ….very helpfull thing for guy like me who is very new to PHP ..
on February 26th, 2009 at 1:38 pm
Can anyone help my above problem,I need to fix this asap
on March 22nd, 2009 at 10:51 pm
thank for the help….
on March 31st, 2009 at 2:08 pm
Thank you so much Mr John Ward. You are doing a lovely job here. Your codes are simple and very effective.
on April 6th, 2009 at 1:26 pm
Thank You very much John! It was really very Helpful.
on April 6th, 2009 at 7:04 pm
hi.
i tried this script .simple and useful.
if u can add a code that add a picture with link.
thanks
on May 9th, 2009 at 2:39 am
Great stuff you have here and not only this tutorial, all of them.
Thanks, really helpfull
on May 11th, 2009 at 12:46 pm
Thank you so much for a wonderful tutorial. It really helps beginners like me.
Is it possible to add or revise the script so if the name entered into the form is not available a message like “Sorry, no records were found” would appear?
Thank you again.
on May 11th, 2009 at 12:54 pm
I didn’t test this, but you would modify the while loop to something like this:
<?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)){
if (isset($row) or $row is null or $row == ”){
echo ‘ID: ‘.$row['ID'];
echo ‘<br/> First Name: ‘.$row['FName'];
echo ‘<br/> Last Name: ‘.$row['LName'];
echo ‘<br/> Phone: ‘.$row['Phone'];
echo ‘<br/><br/>’;
}else{
echo “term: $_POST['term'] not found”;
}
?>
What you want to do is check to see if the array is returned. If it has been echo the results, if not echo “no results found”.
on May 11th, 2009 at 1:43 pm
Thank you so much – I’ll give it a try.
on May 23rd, 2009 at 8:50 am
i am having this Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\wamp\www\search demo\search.php on line 9
s
on May 23rd, 2009 at 12:08 pm
That means you have some kind of error with your query. Most likely you are not using the same variable that you stored the results to.
on June 5th, 2009 at 10:10 am
i am gettin gthe following when trying to run:
Parse error: syntax error, unexpected ‘)’, expecting T_PAAMAYIM_NEKUDOTAYIM in C:\Inetpub\vhosts\seanhaymer.co.uk\httpdocs\tyh\search.php on line 9
any ideas??
Thanks
on June 18th, 2009 at 2:22 am
thanks.. its really help a lot..
on June 19th, 2009 at 7:43 am
simply superb::::::::::::::::::::::::::
try giving more examples like these, so that we can refer easily!
And many many thank’s for you solution.