07.16.2008

Creating a Form that will Search a MySQL Database

Categories: PHP Tutorials
Author: John Ward
Comments: 68 responses
Tags: form, mysql, php, search, sql
Share This

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.

Comments

kabin on July 23, 2008 at 9:32 am

wow! its amazing.

[Reply]
Zippo on August 19, 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

[Reply]
Daniel on August 22, 2008 at 8:24 am

Really, the form works how writes this tutorial. Many thanks to the author.

[Reply]
Shilpam on August 25, 2008 at 5:48 am

Ohhhhh!
This is really working.

[Reply]
Shufi on August 27, 2008 at 4:36 am

yeahh..it really helped me a lot, thanks to the author.

[Reply]
C on August 31, 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.

[Reply]
Franky on September 6, 2008 at 9:09 pm

Damn this works like a charm! Many thnx for the creator of this code

[Reply]
Amit on September 9, 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”

[Reply]
Akshaye on October 25, 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.

[Reply]
Жорка on October 27, 2008 at 5:47 pm

Спасибо, очень понравилось!

[Reply]
mesjasz on November 9, 2008 at 10:32 am

thanks!!

[Reply]
saf on November 25, 2008 at 12:36 pm

i looked everywhere and this is the only solution that worked!
THANKS!!!

[Reply]
wspanda on November 26, 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

[Reply]
Kevin on December 29, 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

[Reply]
ming on January 1, 2009 at 1:41 am

thanks a lot

[Reply]
antony on January 27, 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?

[Reply]
ciaran pender on February 13, 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

[Reply]
imran on February 25, 2009 at 1:38 am

thanx man ….very helpfull thing for guy like me who is very new to PHP ..

[Reply]
ciaran pender on February 26, 2009 at 1:38 pm

Can anyone help my above problem,I need to fix this asap

[Reply]
mike on March 22, 2009 at 10:51 pm

thank for the help….

[Reply]
Pobuk on March 31, 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.

[Reply]
Ankur on April 6, 2009 at 1:26 pm

Thank You very much John! It was really very Helpful.

[Reply]
al kan on April 6, 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

[Reply]
octavian on May 9, 2009 at 2:39 am

Great stuff you have here and not only this tutorial, all of them.

Thanks, really helpfull

[Reply]
Joan on May 11, 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.

[Reply]
John Ward Reply:
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 ‘ First Name: ‘.$row['FName'];
echo ‘ Last Name: ‘.$row['LName'];
echo ‘ Phone: ‘.$row['Phone'];
echo ‘ ’;
}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”.

[Reply]
Joan on May 11, 2009 at 1:43 pm

Thank you so much – I’ll give it a try.

[Reply]
Saeed Ahmed on May 23, 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

[Reply]
John Ward on May 23, 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.

[Reply]
Sean Googlit on June 5, 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

[Reply]
Muhammad on June 18, 2009 at 2:22 am

thanks.. its really help a lot..

[Reply]
rajesh on June 19, 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.

[Reply]
Ronnie on July 20, 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

[Reply]
jefffan24 on August 7, 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?

[Reply]
Wayne on August 23, 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

[Reply]
Tony on September 9, 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.

[Reply]
Computers by WEB » Blog Archive » Sending E-Mail to validate User Sign-up on September 12, 2009 at 10:13 am

[...] July 16, 2008 — Creating a Form that will Search a MySQL Database (37) [...]
Mike on September 20, 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.

[Reply]
Harsha on September 22, 2009 at 3:16 am

‘Thanks a lot. This is very helpful.

[Reply]
Paul on September 30, 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.

[Reply]
A Chakraborty on October 14, 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..

[Reply]
Jim Layton on October 15, 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.

[Reply]
Falanas on October 17, 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.

[Reply]
Rhian on December 26, 2009 at 10:32 am

Brilliant tutorial, thanks so much – worked first time.

[Reply]
php newbie on January 2, 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

[Reply]
Peter Jones on January 10, 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

[Reply]
Mike W on January 25, 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

[Reply]
Söve on January 27, 2010 at 11:48 am

I agree Peter.Thanks.

[Reply]
zoloo on February 3, 2010 at 11:02 am

thank’s it is very good example hope u all good thing

[Reply]
rashoood on February 6, 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

[Reply]
Lukas Berns on February 17, 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

[Reply]
Mike Maguire Reply:
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.

[Reply]
yudi on February 22, 2010 at 10:03 pm

Awesome tutorial. Thank you.

[Reply]
Rob on March 20, 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.

[Reply]
John Ward on March 20, 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.

[Reply]
Rob on March 21, 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.

[Reply]
marsahata on March 23, 2010 at 6:32 am

akh,,, Parse error: parse error in C: \ xampp \ htdocs \ ok \ search.php on line13 ….
gimana nehhh,
ngasinya jgn stgh2 dong../?
yg iklas…

[Reply]
chris on April 16, 2010 at 11:39 am

the script and search works fine. i need help with the . When the results return and it enlarges the table when it cannot fit on the entire screen.

[Reply]
collins on April 19, 2010 at 11:49 am

how can i select all thats select anything instead of just Fname

[Reply]
Syed Rizvi on June 25, 2010 at 10:21 am

This was great, I got the results right away.
Thanks a million.

[Reply]
Syed Rizvi Reply:
June 25th, 2010 at 4:31 pm
One more thing, what if the search come up blank can we place a message “sorry no suggestions”.
How can we add this code.
Thanks in advance.

[Reply]
myloginkey on June 28, 2010 at 6:01 pm

i am having this Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\wamp\www\demo\search.php on line 9
s

[Reply]
docteur² on July 3, 2010 at 2:00 pm

yessssssss, thank youuuuuuuuuuuuuu veryyyyyy Much.
this tuto is very simple and too good.
Thank you very much
Can you ceate another one which allow Admin to modify the same DataBase ? (php)

[Reply]
Jestin on July 7, 2010 at 4:45 pm

thank uuuuuuuuuuuuuu

[Reply]
salkini on July 19, 2010 at 3:07 am

I got some problems relating theme together. When I use the search the php code appears instead of the database info. What should I do?

[Reply]
salkini Reply:
July 27th, 2010 at 7:51 am
i worked it out. thanks

[Reply]
ashwani on July 21, 2010 at 4:33 pm

hii good work………………….

[Reply]
Amanda on July 22, 2010 at 6:39 pm

Hello.
How do you get this to return a url with an image?

Thanks!
Amanda

[Reply]

Creating a Form that will Search a MySQL Database

Related Posts

Comments

kabin on July 23, 2008 at 9:32 am

Zippo on August 19, 2008 at 3:45 pm

Daniel on August 22, 2008 at 8:24 am

Shilpam on August 25, 2008 at 5:48 am

Shufi on August 27, 2008 at 4:36 am

C on August 31, 2008 at 12:37 am

Franky on September 6, 2008 at 9:09 pm

Amit on September 9, 2008 at 7:16 am

Akshaye on October 25, 2008 at 10:04 am

Жорка on October 27, 2008 at 5:47 pm

mesjasz on November 9, 2008 at 10:32 am

saf on November 25, 2008 at 12:36 pm

wspanda on November 26, 2008 at 6:27 am

Kevin on December 29, 2008 at 4:55 am

ming on January 1, 2009 at 1:41 am

antony on January 27, 2009 at 11:43 am

ciaran pender on February 13, 2009 at 10:11 am

imran on February 25, 2009 at 1:38 am

ciaran pender on February 26, 2009 at 1:38 pm

mike on March 22, 2009 at 10:51 pm

Pobuk on March 31, 2009 at 2:08 pm

Ankur on April 6, 2009 at 1:26 pm

al kan on April 6, 2009 at 7:04 pm

octavian on May 9, 2009 at 2:39 am

Joan on May 11, 2009 at 12:46 pm

Joan on May 11, 2009 at 1:43 pm

Saeed Ahmed on May 23, 2009 at 8:50 am

John Ward on May 23, 2009 at 12:08 pm

Sean Googlit on June 5, 2009 at 10:10 am

Muhammad on June 18, 2009 at 2:22 am

rajesh on June 19, 2009 at 7:43 am

Ronnie on July 20, 2009 at 4:27 am

jefffan24 on August 7, 2009 at 3:56 pm

Wayne on August 23, 2009 at 11:12 am

Tony on September 9, 2009 at 3:32 pm

Computers by WEB » Blog Archive » Sending E-Mail to validate User Sign-up on September 12, 2009 at 10:13 am

Mike on September 20, 2009 at 5:10 pm

Harsha on September 22, 2009 at 3:16 am

Paul on September 30, 2009 at 11:41 am

A Chakraborty on October 14, 2009 at 6:05 am

Jim Layton on October 15, 2009 at 8:55 pm

Falanas on October 17, 2009 at 3:59 am

Rhian on December 26, 2009 at 10:32 am

php newbie on January 2, 2010 at 11:52 pm

Peter Jones on January 10, 2010 at 10:39 am

Mike W on January 25, 2010 at 4:31 pm

Söve on January 27, 2010 at 11:48 am

zoloo on February 3, 2010 at 11:02 am

rashoood on February 6, 2010 at 11:55 am

Lukas Berns on February 17, 2010 at 11:01 am

yudi on February 22, 2010 at 10:03 pm

Rob on March 20, 2010 at 10:40 pm

John Ward on March 20, 2010 at 10:47 pm

Rob on March 21, 2010 at 1:38 am

marsahata on March 23, 2010 at 6:32 am

chris on April 16, 2010 at 11:39 am

collins on April 19, 2010 at 11:49 am

Syed Rizvi on June 25, 2010 at 10:21 am

myloginkey on June 28, 2010 at 6:01 pm

docteur² on July 3, 2010 at 2:00 pm

Jestin on July 7, 2010 at 4:45 pm

salkini on July 19, 2010 at 3:07 am

ashwani on July 21, 2010 at 4:33 pm

Amanda on July 22, 2010 at 6:39 pm

Categories

Tags

Latest from Tutorial Grad

About This Site

Links

Pages

Meta