Updated date:

Simple Search Using PHP and MySQL

Author:

I'm going to show you how to create simple search using PHP and MySQL. You'll learn:

  • How to use GET and POST methods
  • Connect to database
  • Communicate with database
  • Find matching database entries with given word or phrase
  • Display results

Preparation

You should have Apache, MySQL and PHP installed and running of course (you can use XAMPP for different platforms  or  WAMP for windows, MAMP for mac) or a web server/hosting that supports PHP and MySQL databases.

Let's create database, table and fill it with some entries we can use for search:

  • Go to phpMyAdmin, if you have server on your computer you can access it at http://localhost/phpmyadmin/
  • Create database, I called mine tutorial_search
  • Create table I used 3 fields, I called mine articles.
  • Configuration for 1st field. Name: id, type: INT, check AUTO_INCREMENT, index: primary

INT means it's integer
AUTO_INCREMENT means that new entries will have other(higher) number than previous
Index: primary means that it's unique key used to identify row

  • 2nd field: Name: title, type: VARCHAR, length: 225

VARCHAR means it string of text, maximum 225 characters(it is required to specify maximum length), use it for titles, names, addresses
length means it can't be longer than 225 characters(you can set it to lower number if you want)

  • 3rd field: Name: text, type: TEXT

TEXT means it's long string, it's not necessary to specify length, use it for long text.

simple-search-php-mysql
simple-search-php-mysql
simple-search-php-mysql
  • Fill the table with some random articles(you can find them on news websites, for example: CNN, BBC, etc.). Click insert on the top menu and copy text to a specific fields. Leave "id" field empty. Insert at least three.

It should look something like this:


simple-search-php-mysql
  • Create a folder in your server directory and two files: index.php and search.php (actually we can do all this just with one file, but let's use two, it will be easier)
  • Fill them with default html markup, doctype, head, etc.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
	<title>Search</title>
	<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
</head>
<body>

</body>
</html>
  • Create a form with search field and submit button in index.php, you can use GET or POST method, set action to search.php. I used "query" as name for text field

GET - means your information will be stored in url (http://localhost/tutorial_search/search.php?query=yourQuery)
POST - means your information won't be displayed it is used for passwords, private information, much more secure than GET

<form action="search.php" method="GET">
	<input type="text" name="query" />
	<input type="submit" value="Search" />
</form>

Ok, let's get started with php.

  • Open search.php
  • Start php (<?php ?>)
  • Connect to a database(read comments in following code)
<?php
	mysql_connect("localhost", "root", "") or die("Error connecting to database: ".mysql_error());
	/*
		localhost - it's location of the mysql server, usually localhost
		root - your username
		third is your password
		
		if connection fails it will stop loading the page and display an error
	*/
	
	mysql_select_db("tutorial_search") or die(mysql_error());
	/* tutorial_search is the name of database we've created */
	
	
	
?>

You can go and check if there is no errors.

  • Now go to the <body></body> part of the page
  • I'm using GET method, if you want to use POST, just use $_POST instead of $_GET
  • Also some functions to make it more secure. Read comments in the code
  • Send query to database
  • Check if there is any results
  • If there is any, post them using while loop
<?php
	$query = $_GET['query']; 
	// gets value sent over search form
	
	$min_length = 3;
	// you can set minimum length of the query if you want
	
	if(strlen($query) >= $min_length){ // if query length is more or equal minimum length then
		
		$query = htmlspecialchars($query); 
		// changes characters used in html to their equivalents, for example: < to &gt;
		
		$query = mysql_real_escape_string($query);
		// makes sure nobody uses SQL injection
		
		$raw_results = mysql_query("SELECT * FROM articles
			WHERE (`title` LIKE '%".$query."%') OR (`text` LIKE '%".$query."%')") or die(mysql_error());
			
		// * means that it selects all fields, you can also write: `id`, `title`, `text`
		// articles is the name of our table
		
		// '%$query%' is what we're looking for, % means anything, for example if $query is Hello
		// it will match "hello", "Hello man", "gogohello", if you want exact match use `title`='$query'
		// or if you want to match just full word so "gogohello" is out use '% $query %' ...OR ... '$query %' ... OR ... '% $query'
		
		if(mysql_num_rows($raw_results) > 0){ // if one or more rows are returned do following
			
			while($results = mysql_fetch_array($raw_results)){
			// $results = mysql_fetch_array($raw_results) puts data from database into array, while it's valid it does the loop
			
				echo "<p><h3>".$results['title']."</h3>".$results['text']."</p>";
				// posts results gotten from database(title and text) you can also show id ($results['id'])
			}
			
		}
		else{ // if there is no matching rows do following
			echo "No results";
		}
		
	}
	else{ // if query length is less than minimum
		echo "Minimum length is ".$min_length;
	}
?>

Done!

Now it works. Try different words, variations, editing code, experiment. I'm adding full code of both files in case you think you've missed something. Feel free to ask questions or ask for tutorials.

index.php

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
	<title>Search</title>
	<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
	<link rel="stylesheet" type="text/css" href="style.css"/>
</head>
<body>
	<form action="search.php" method="GET">
		<input type="text" name="query" />
		<input type="submit" value="Search" />
	</form>
</body>
</html>

search.php

<?php
	mysql_connect("localhost", "root", "") or die("Error connecting to database: ".mysql_error());
	/*
		localhost - it's location of the mysql server, usually localhost
		root - your username
		third is your password
		
		if connection fails it will stop loading the page and display an error
	*/
	
	mysql_select_db("tutorial_search") or die(mysql_error());
	/* tutorial_search is the name of database we've created */
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
	<title>Search results</title>
	<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
	<link rel="stylesheet" type="text/css" href="style.css"/>
</head>
<body>
<?php
	$query = $_GET['query']; 
	// gets value sent over search form
	
	$min_length = 3;
	// you can set minimum length of the query if you want
	
	if(strlen($query) >= $min_length){ // if query length is more or equal minimum length then
		
		$query = htmlspecialchars($query); 
		// changes characters used in html to their equivalents, for example: < to &gt;
		
		$query = mysql_real_escape_string($query);
		// makes sure nobody uses SQL injection
		
		$raw_results = mysql_query("SELECT * FROM articles
			WHERE (`title` LIKE '%".$query."%') OR (`text` LIKE '%".$query."%')") or die(mysql_error());
			
		// * means that it selects all fields, you can also write: `id`, `title`, `text`
		// articles is the name of our table
		
		// '%$query%' is what we're looking for, % means anything, for example if $query is Hello
		// it will match "hello", "Hello man", "gogohello", if you want exact match use `title`='$query'
		// or if you want to match just full word so "gogohello" is out use '% $query %' ...OR ... '$query %' ... OR ... '% $query'
		
		if(mysql_num_rows($raw_results) > 0){ // if one or more rows are returned do following
			
			while($results = mysql_fetch_array($raw_results)){
			// $results = mysql_fetch_array($raw_results) puts data from database into array, while it's valid it does the loop
			
				echo "<p><h3>".$results['title']."</h3>".$results['text']."</p>";
				// posts results gotten from database(title and text) you can also show id ($results['id'])
			}
			
		}
		else{ // if there is no matching rows do following
			echo "No results";
		}
		
	}
	else{ // if query length is less than minimum
		echo "Minimum length is ".$min_length;
	}
?>
</body>
</html>

Comments

Marco on June 18, 2020:

it works fine in a php 5 but when I change to php greater than 6 nothing comes out on the results

magical friday on June 18, 2020:

this is half complete. What if I get 1000 records striaght away from database ??

where is the pagination ???

Gary on May 11, 2020:

I tried this it is not working. The page is showing blank.

kumarganeshb on April 06, 2020:

very nice thanks

balasuriya on March 15, 2020:

good

yohannis on January 02, 2020:

this is demo

Akshata A on December 18, 2019:

create a search button using bootstrap or php,,,,,but we want GUI search button with easy method, and also we content box

Jakir hussian on October 31, 2019:

If I want search like men latest shoe or shoe for women or women shoe

then which is the best way to make search ..

bruno on October 17, 2019:

legal

Ghassen on September 15, 2019:

I use your code.. But I have a problem. mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in(.....) on line...

anto on July 28, 2019:

this nice

migs on July 16, 2019:

Undefined index: query

Why is this happening

Laxmi on July 09, 2019:

thank you for the post

Shubham Pandit on July 05, 2019:

Its very good.....it help me....

Jimmy on June 29, 2019:

Thanks a lot and it's very easy to understand.

Nedum on May 28, 2019:

How would you phrase the structured query if you were doing PDO?

Especially as it affects the two "LIKE" terms:

Do you use one variable for the execution of the prepared statement or two variables?

Pov on May 27, 2019:

Thanks

Manish on May 26, 2019:

Thanks

ali on May 26, 2019:

great!!!

rakesh Nama on May 18, 2019:

Helpful

AsKas Jeremy on April 20, 2019:

Thank you for the great article. It helped me for my course work.

web on April 14, 2019:

nice articel

but 2 years ago you could use $bdd = new PDO('....')

instead old style mysqlconnect..

suraj on March 29, 2019:

thanks

Manzini on March 19, 2019:

Change mysql to mysqli for php greater than 5.6

mufaddal on March 06, 2019:

where is search.html file

Sunil Mathur on January 18, 2019:

Hello sir

Kamana Girls Hostel on January 06, 2019:

yeah completely helpful

unknown on January 05, 2019:

This doesn't work, the functions that have been called have been not been defined.

sitender on January 04, 2019:

good work

Aria on December 30, 2018:

great content helped me very much :)

unknowm on December 21, 2018:

how add many text..means many pages for full website

Abu Zeenat on December 14, 2018:

Please after editing the code it is showing No Database selected

alex on December 03, 2018:

when i search i only get connected successfully no results are displaying

vinoth on November 19, 2018:

Works with few modifications

dennis on November 11, 2018:

its awesome

hi mador on November 04, 2018:

php updeate query

Ravin on October 04, 2018:

It worked thank you very much

Redirect on September 29, 2018:

How can I redirect this search to another page and show the results in another page?

Youngfox on September 04, 2018:

@BrainzJnr you need to create the database in mysql or if the case is different check your permission settings but if that doesn't work just make a database with another name of your choice and modify the mysqli codes in order to fit your database. by just changing any where you see brainztech to the name of the database you created. if that doesnt work then go on some research on youtube on connecting mysql and php majorly "How to use xampp control panel"

BrainzJnr on August 17, 2018:

Please its not working for me.

I am getting this error

" Access denied for user ''@'localhost' to database 'brainztech' "

But I have connected to my database.

Please help me out.

Thanks

Brainz on August 17, 2018:

Please its not working for me, I get this :

Access denied for user

''@'localhost' to database 'brainztech'

Marco on August 14, 2018:

Thanks for the code! it works fine in Godaddy but it doen't work in bluehost

Connor Brereton on August 08, 2018:

you need to update this tutorial since mysql_X functions are depreciated and everyone uses mysqli_X or PDO now

Samuel on July 11, 2018:

It is really working. thank you

Anon on July 05, 2018:

A lot of this code is deprecated. Should probably note this at the beginning of this tutorial or update it.

benten on July 03, 2018:

where is your code

Ayo Celestine on June 25, 2018:

Wow this is really great! Thanks so so much!!!!!!!!!!!!!!!

pratik on June 19, 2018:

do u have any video tutorial of the following project?

John Gachena on June 13, 2018:

It Goood very thanks......well done

verolla on June 12, 2018:

Working really well. Thank you!

Ross on June 09, 2018:

Many Thanks. It was easy to understand. God bless you

lg on June 09, 2018:

good but not working

kouassi on May 30, 2018:

nice an simple code . thank

dean on May 30, 2018:

hello how are u

calpizsoftware on May 28, 2018:

Error Error

i think there before should if(isset($_POST['query'])){

}

also $min_length = 3; this causes an error

mbonisi tshuma on May 24, 2018:

I keep getting this "Notice: Undefined index: query"

bhanu on April 30, 2018:

awesome...

very easy

fhj on April 23, 2018:

mysql deprecated - please use mysqli or PDO

bn hjnb on April 13, 2018:

love it

kingfisher on April 04, 2018:

thank you

shamli on April 02, 2018:

Fatal error: Uncaught Error: Call to undefined function mysql_connect() in C:\xampp\htdocs\search\search.php:2 Stack trace: #0 {main} thrown in C:\xampp\htdocs\search\search.php on line 2

I have this error plz tell me about that

Sam on March 29, 2018:

Great work! Can you also run the search on multiple tables?

oka on March 27, 2018:

the code is easy but not working

Sam on March 19, 2018:

your code not working properly but easy to understand

paul on February 28, 2018:

thank you so much for a easy to understand code. You're awesome

CK Mohammed on February 23, 2018:

Very good, Thanks

SamGidolew on February 16, 2018:

Excellent frnd. thank you

DON on February 09, 2018:

please make a video on it, and i mean searching the database and displaying the search results

habtom on February 07, 2018:

I like your web sits

krishna on January 25, 2018:

how do i get the value of search box into the next page in php

scott on January 24, 2018:

This is awesome need a mysqli version

raj kishor yadav on January 19, 2018:

very very helpful.i was looking for this for weeks .thanks a lot.

keep on sharing such high valuable content.

onyy on January 16, 2018:

tahnk you so much

sjaan on December 16, 2017:

its very awsome. Thanks a lot for sharing such useful knowledge

dila on December 15, 2017:

why $query is undefined index? please help me ASAP

sly on December 15, 2017:

Thank you so much.It's just perfect for my simple website, only had to make a few adjustments and it worked.

uselesstrash on December 11, 2017:

not work, don't use this useless code

to lloyd stephenn on December 10, 2017:

mysqli is newer and you should use that cos the other will be taken out as i know.

lloyd on December 10, 2017:

thanks a lot man.

lloyd stephenn on December 10, 2017:

what's the difference when you use mysqli other than mysql??help me pls.

Zarif joya on December 09, 2017:

Thanks for this good code

sanjay on December 08, 2017:

It's not working to me.

unknown on December 04, 2017:

It's not working to me.

unknown on November 30, 2017:

where's the output?

Name on November 26, 2017:

Just testing

judith on November 21, 2017:

hy how to search from two different tables ?

CK on November 01, 2017:

Thank you - worked a treat, much appreciated.

Now looking for sqli or pdo search ;-)

joyce on October 13, 2017:

code not working please help

hassam on August 31, 2017:

can anyone help me how display me my database record on my page in table . and if i click on search button without putting anything in input box then it should display all the record ...iam new in coding

aa on August 25, 2017:

Thanks

elle on August 20, 2017:

what is Query? $query = $_GET['query'];

Ewan McQueen on August 17, 2017:

I have the search results showing but no pictures

Ewan on August 17, 2017:

I have the search showing but no pictures

Tosin on August 17, 2017:

H, this is urgent. Please I want to know if i can have the search button on my home page without having to go through a form on a different page? Or can the form be imbedded in my home page? Tnx

niv on August 17, 2017:

results?

kkds on August 14, 2017:

ya brother good

OBITEX on August 12, 2017:

Bro you have a nice code. but pls i need the sql statement.

dzaky on July 29, 2017:

thanks dude

Ibro on July 25, 2017:

This is Awesome And Perfect , Thank you!

Malik Ansar on July 23, 2017:

very helpful and easy

Related Articles