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: 18% [?]
Related Posts
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.
on July 20th, 2009 at 4:27 am
Thanks a lot, this was very helpfull.
have been looking a lot for this kind of for, to develope to something bigger, the one i found so far was to complicated and i didnt know what i did wrong.
i been working with this for just about 40 hours and i could create one to my database very simple.
Comment, i prefer to make the connection in another file so the login to the database is not writen in the same page.
this files just hold this
on August 7th, 2009 at 3:56 pm
Is there any way to make it search multiple fields in my database. Like lets say I have a database that contains the following fields:
CustomerName
HomePhone
WorkPhone
CellPhone
Is there any way I can make it so that when somebody searches for something in my database it will search all 4 of those fields?
on August 23rd, 2009 at 11:12 am
I am still struggling.
Please can I have a more basic step by step instruction to :
1/ Adding search boxes to my page. 1 Box for Location and 1 Box for Keywords
2/ Then i want to return results from mysql database table. My database table headins are :
Location,Retailer,Keywords,Price,Product name,URL LINK
Based on the search box the results will be displayed as per the above headings. Spent the whole day looking for a basic step by step guide but no joy
on September 9th, 2009 at 3:32 pm
Thank you john for this great tutorial.
It works great.
I have only one question?
How do I make my result “let’s say the FName” a link to the details page?
Since I have a details page that shows all the field of the entry and those are to many to display in my search page.
My detail page haves a record set filter by the variable “attrn”
And I tried everything I could think of to link the ” echo ‘ FName:’ .$row['attr_id'];” to the detail page but no matter how I do it gives me an error.
I will really appreciate some help.
on September 12th, 2009 at 10:13 am
[...] July 16, 2008 — Creating a Form that will Search a MySQL Database (37) [...]
on September 20th, 2009 at 5:10 pm
PERFECT!!! Finally someone that wrote a script that didnt have to be altered! My type of fellow programmer here!! Cant wait to check out the rest of the stuff you wrote.
on September 22nd, 2009 at 3:16 am
‘Thanks a lot. This is very helpful.
on September 30th, 2009 at 11:41 am
Thank you so much for this wonderful code and extremely easy to follow tutorial.
I have been searching for this type of thing for the past few weeks and have spent many hours trying and failing to use tutorials/code found elsewhere.
This does exactly what I was looking for and took minutes to set up.
So, once again, thank you greatly.
on October 14th, 2009 at 6:05 am
an endless no. of thanks 2 u man!!!gr8 job done.it helped me a lot.thnx again…hope 2 get more help from u in future..
on October 15th, 2009 at 8:55 pm
The ONLY working code on the subject I could find after days of looking….nice work.
Now the $64,000 question is how to display the results in a form that can be edited/update when required.
Cash and prizes for anyone who can help….seriously.
on October 17th, 2009 at 3:59 am
Thanks a lot I got it all. But how do we link, say Bob so that when Bob is clicked we are able to get more information about him e.g. Location, ID No., Department e.t.c.
Thank you again.
on December 26th, 2009 at 10:32 am
Brilliant tutorial, thanks so much – worked first time.
on January 2nd, 2010 at 11:52 pm
this is a good tutorial:D it’s complete though i haven’t tried it out yet.. does anyone here knows how to generate a USERNAME from getting a user’s input (input: LAST NAME,FIRST NAME,ID) in php???thanks
on January 10th, 2010 at 10:39 am
We’re in the process of a redesign and adding a search field was vital, our new UK pub directory will have a simple search query and this turorial was very helpful
thank you
Peter
on January 25th, 2010 at 4:31 pm
Thanks for the tutorial. I know that I can use that effectively. Question: How would you search for a Date.. For example, I want to find records whose date is later than say 12/25/2009
Thanks,
Mike
on January 27th, 2010 at 11:48 am
I agree Peter.Thanks.
on February 3rd, 2010 at 11:02 am
thank’s it is very good example hope u all good thing
on February 6th, 2010 at 11:55 am
Parse error: syntax error, unexpected T_WHILE in E:\wamp\www\UGHacker\search.php on line 9
i need this help fast email me if u can
samurai-hacker@live.com
this is the full thing
<?php
mysql_connect ("localhost", "test","test") or die (mysql_error());
mysql_select_db ("ughacker");
$term = $_POST['term'];
$sql = mysql_query("select * from remote where topic like '%$term%' or author like '%$term%' ")
while ($row = mysql_fetch_array($sql)){
echo 'ID: '.$row['id'];
echo ' First Name: ‘.$row['topic'];
echo ‘ Last Name: ‘.$row['date'];
echo ‘ Phone: ‘.$row['author'];
echo ”;
}
?>
i edited some stuff in the result but thats what i want it to be
on February 17th, 2010 at 11:01 am
(because no one has said it yet)
If you use this code in a real webpage, you’ll easily get hacked through SQL Injection. Don’t forget to escape the user input using mysql_real_escape_string()
See:
http://php.net/manual/en/function.mysql-real-escape-string.php
http://en.wikipedia.org/wiki/SQL_injection
Hope this saves someone from an attack
on February 22nd, 2010 at 10:03 pm
Awesome tutorial. Thank you.
on February 22nd, 2010 at 10:13 pm
Great point Lukas, After doing some of these tutorials to get peoples feet wet, we were going to do a few on preventing SQL injections but we have been busy and have not posted them yet.
on March 20th, 2010 at 10:40 pm
Here is my problem
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home3/arcprese/public_html/search.php on line
Php code:
<?php
mysql_connect("localhost", "arcprese_robert", "password") or die(mysql_error());
mysql_select_db("updateform");
$term = $_POST['term'];
$sql = mysql_query("select * from updateform where Job_number like '$term'");
while ($row = mysql_fetch_array($sql)){
echo ' Work order number: ‘.$row['Job_number'];
echo ”;
}
?>
When I create a simple echo to make sure the search is being passed to the search.php page it is. There is something wrong with the
while ($row = mysql_fetch_array($sql)){
Please help as I guess i’m blind and need a second set of eyes.
on March 20th, 2010 at 10:47 pm
There is something wrong with your query. Run the query directly against the database and see if it returns results.
on March 21st, 2010 at 1:38 am
Thank you John. I had an incorrect table name. My table name was updateforms and I was missing the “s”. I swear I stared at this for about 2 hrs and over looked it every time. Thanks again.