AcademiaSTEMHumanitiesAgriculture & FarmingSocial Sciences

Simple Search Using PHP and MySQL

Updated on April 30, 2016

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.

  • 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:


  • 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

    0 of 8192 characters used
    Post Comment

    • profile image

      judith 24 hours ago

      hy how to search from two different tables ?

    • profile image

      CK 2 weeks ago

      Thank you - worked a treat, much appreciated.

      Now looking for sqli or pdo search ;-)

    • profile image

      joyce 5 weeks ago

      code not working please help

    • profile image

      hassam 2 months ago

      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

    • profile image

      aa 2 months ago

      Thanks

    • profile image

      elle 3 months ago

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

    • profile image

      Ewan McQueen 3 months ago

      I have the search results showing but no pictures

    • profile image

      Ewan 3 months ago

      I have the search showing but no pictures

    • profile image

      Tosin 3 months ago

      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

    • profile image

      niv 3 months ago

      results?

    • profile image

      kkds 3 months ago

      ya brother good

    • profile image

      OBITEX 3 months ago

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

    • profile image

      dzaky 3 months ago

      thanks dude

    • profile image

      Ibro 3 months ago

      This is Awesome And Perfect , Thank you!

    • profile image

      Malik Ansar 4 months ago

      very helpful and easy

    • profile image

      www.tutoralert.com 4 months ago

      very help full codding

    • profile image

      Ewan McQueen 4 months ago

      Is there a way I can use this code to display an image as a search result from my sql table? I am very new to coding and am struggling to do this.

    • profile image

      Daniel 5 months ago

      This is the best search script i have found online, Thanks a lot admin , you deserve a reward!

    • profile image

      Kedar Maharjan 5 months ago

      Hello this is the most complete code i found for php search. what if text field is empty in if else statement.

    • profile image

      Rijak 5 months ago

      wonderful code

    • profile image

      Jack Johnson 6 months ago

      Thank you this is very helpful! Please help to display the result in a table I am using bootstrap.

    • profile image

      ashton 6 months ago

      i love you man, thank you so much :D

    • profile image

      saeed 6 months ago

      simple for beginner

    • profile image

      Nipuna1 6 months ago

      Thank you!!

    • profile image

      Akash 6 months ago

      Hi, this code working fine, but how to search without refreshing the page

    • profile image

      Fidele 6 months ago

      Very helpful! Thanks an way!

    • profile image

      Akash Kumar 7 months ago

      Working fine

    • profile image

      Vishwas 7 months ago

      Thanks a lot for this code. Its easy and simple code for search.

    • profile image

      iqbal 7 months ago

      nice. this is working. thanks. i use it

    • profile image

      Iam_Fortunatus 7 months ago

      Thank for the code sir

    • profile image

      Dan 7 months ago

      Is it possible to make a search engine using php only?.

    • profile image

      ramesh giri 7 months ago

      very nice very useful thank you

    • profile image

      Afrosith 7 months ago

      Awesome... Its really did it..

    • profile image

      flamboyantandy 8 months ago

      First of all, thank you very much for such a nice coding. Can you please help me drop empty or NULL row's so that result should display only filled options and not all. I mean if in any record, text field is not filled then in result text option should not be visible. Result should display only id and title option.

      Thank You

    • profile image

      cisn 8 months ago

      Thanks for this code !!!

      But only one column shows not all fields, which i really need to.

      for example i search onne column but it just show only the first column.. even if it is not what i search. it just show because beacuse they have same row.

    • profile image

      LAMINE 8 months ago

      thanks you very much.But i have this errors :Parse error: syntax error, unexpected '$query' (T_VARIABLE) in C:\wamp\www\test\teste des class\search.php on line 24

      can you help me please thanks

    • profile image

      dev 9 months ago

      useful code

    • profile image

      DököllSocial 9 months ago

      Great use of code separation- Thank you for posting and making available to public...

    • profile image

      chen chen nue 9 months ago

      very nice coding

    • profile image

      Rads 9 months ago

      Thanks a lot for this coding. it works Perfect.

    • profile image

      aswini ravella 9 months ago

      excellent post thak u for helping me

    • profile image

      how insert search word into mysql 9 months ago

      how can i input the search word into mysql? if i search using a "word" for that word to be inserted into a table in mysql, for new search word to create new table.

    • profile image

      Joseph 9 months ago

      Nice but how can I select from multiple tables like I tried doing this = mysql_query("SELECT * FROM all_post AND p_post

      WHERE (`tittle` LIKE '%".$query."%') OR (`summary` LIKE '%".$query."%')") or die(mysql_error());

      but its not working.

    • profile image

      mmullally 9 months ago

      This was an amazingly helpful tutorial. I have one ask i am hoping you will be able to answer. I am searching a db and displaying the results in a table for the user. I want to add a checkbox and qty field to the table and make it an "order form" like system where they can select the items they want. I am having issues creating the names for the checkboxes. I want to check to only include the items checked. what function can i use to dynamically name the checkboxes ie. name="checkbox1" name="checkbox2"

      i know it will be using the mysql_num_rows somehow to tell it to use an index of somekind.

    • Bonan Conan profile image

      Bonan Conan 9 months ago

      Can someone can explain this :

      Unknown column 'text' in 'where clause'

      I got this in result page and dont know what next! I done everything the same as this guy.... so please!

    • profile image

      Simon 9 months ago

      thanks man a lot 6 :)

    • profile image

      azlan 10 months ago

      very good coding

    • profile image

      Ashfaq 10 months ago

      thanks for nice Post. And my wish is ?

      i wants search display result in Column & rows plzzzzzzzzz help me

    • profile image

      Deepak Saini 10 months ago

      Thx Man For Helping me its simple and nice codeing

    • profile image

      bakhtawar komal 10 months ago

      Notice: Undefined index: text in E:\xamp\htdocs\LETS TRY\SEARCH.php on line 53

      please guide me to solve this error

    • profile image

      vinit 10 months ago

      Hi I am getting an Error "

      Parse error: syntax error, unexpected end of file in /home/indiantravels/public_html/bus_tkt/search.php on line 57

      "

      I removed else close from the end of code.

    • profile image

      Jaffer 11 months ago

      simple bt powerful

    • profile image

      Manav Kharat 11 months ago

      Thanks

      Your article helped me a lot...

    • profile image

      Hussein Sarkar 11 months ago

      Hello, please can u guide me on how i can use the code posted above to delete a record in the mysql database using the ID? Thanking in advance.

    • profile image

      Hussein Sarkar 11 months ago

      hey, awesome coding and well explained. Thanks alot!

    • profile image

      sreylak 12 months ago

      so nice code , thanks

    • profile image

      Frazier 12 months ago

      Hi.

      I'm 'NEW' at coding.

      When I input the code, i keep getting blank results. What does this mean?

    • profile image

      peter 12 months ago

      Thank you, it works! One little thing..When I write a " ' " it converts to a " \' " e.g. " 'search' " converts to "\'search\' ". How does it happen and how can I prevent this?

    • profile image

      Passenger 12 months ago

      It works perfectly, thank you very much! I just edited with the newer MSQLi commands (it needs only littel adjustments) and it still works fine :)

    • profile image

      abhi 13 months ago

      helped alot thnx :)

    • profile image

      Sujon 13 months ago

      If I wrote

      $query = $_POST['query'];

      Showing Length :

      Minimum length is 3

    • profile image

      Mohammed Shamshid 14 months ago

      How can i highlight all the keyword given in the text field to search, in result page?

    • profile image

      Diya 14 months ago

      Access denied for user 'npdassoc'@'localhost' to database 'tutorial_search'

      Please Help Me

    • profile image

      Diya 14 months ago

      Error connecting to database: Access denied for user 'root'@'localhost' (using password: YES)

      How to Connect the cpanel database

      Please help Me

    • profile image

      Shawn 14 months ago

      Thankyou!

    • profile image

      Manolis 15 months ago

      nice code..

      thank you

    • profile image

      seng 15 months ago

      what does it mean? please help me solve.tq

      Parse error: syntax error, unexpected 'Â Â Â Â Â ' (T_STRING) in C:\xampp\htdocs\RTest1\users\sear.php on line 10

    • profile image

      shahdab 15 months ago

      i want search in complete database

    • profile image

      Maxime 16 months ago

      Why not just use PDO and HTML5 to begin with instead of outdated stuff. This is absolutely no help.

    • profile image

      MArk 17 months ago

      When i tried your code, it displayed : error .. undefined' query'.

      The query is already defined by name.

      So any idea?

    • profile image

      JimG 18 months ago

      Hi. I have keywords in a column in my database separated by a space. If I try putting spaces around my search term like, "$vidhints LIKE '% $search_each %'" the search misses some records because there's a space at the front of some of my keywords in the table. E.g., my keyword column has: "recurring services software" So, when I do a search for "recurring" it works, but if I do a search for "services" the search only works if I put a space before the word services. I guess because that's how it is in the database? How do I get around this if I only want to match the word? E.g., if I search for "recurring services" zero results display.

    • profile image

      opanga Richard 18 months ago

      Great article!!!!!!!!

    • profile image

      gids 20 months ago

      thanks again.. how can i make this work if what i'm searching for is a file say (.mp3) that i need to download?? will really like your help on these..

    • profile image

      gids 20 months ago

      this is the error i get... You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'info1 '%gistoncelebrities%')' at line 2

    • profile image

      True 20 months ago

      thank you so much :) I've been in a lot of tutorials and none of them works like the way I wanted to

    • profile image

      chimfwembe 22 months ago

      After the sql select statement the number of open curly braces does not match the closing curly braces hencing making display diffcult for me, unless there is something am missing, please help

    • profile image

      Ayesha Ch 23 months ago

      i cannot run my search engine queury

    • profile image

      Vladimaire 2 years ago

      it always show:

      "Parse error: syntax error, unexpected '$query' (T_VARIABLE) in C:\xampp\htdocs\newWebProg\search.php on line 13"

      line 13 is:

      "    $query = $_GET["query"]; "

      what might be the problem?

    • profile image

      vk 2 years ago

      error

      Notice: Undefined index: query in _____________.php on line 14

    • profile image

      arun gautam 2 years ago

      Great Code thanks and God Bless you

    • profile image

      Bwire sedrick 2 years ago

      Thanks sooooooooooo much

    • profile image

      Rajan 2 years ago

      nice coding !

    • profile image

      sino 2 years ago

      Bless u i was tired for days trying to this

    • profile image

      kumar 2 years ago

      nice coding thank you.

    • profile image

      dk45516 2 years ago

      is it possible to code for a search for numerical values, such that entering the number in the search box will return values that are less than the entered value available in the database in a particular column.

    • profile image

      pinnaclei 2 years ago

      nice coding , very useful , thank you

    • PKreturns profile image

      Pradeep PK 2 years ago from Bangalore, India

      Your only hub but very much informative particularly for web developer like me

    • profile image

      ricky 2 years ago

      its good

    • profile image

      cosmicdisco 2 years ago

      hey super cool code, i have a question. how is possible to emphasize only the search words? thanks

    • profile image

      Ben 2 years ago

      Hey, i just want to say your codes help me with my work. Thanks alot!

    • profile image

      Adam 2 years ago

      Very nice and very simple.....i love man

    • profile image

      STAN 2 years ago

      Thank You.

    • profile image

      jayanta deb 3 years ago

      thanks a lots

    • profile image

      Gurpreet Singh 3 years ago

      Great explanation. Thank you.

    • profile image

      Burhan 3 years ago

      you are amazing!

    • profile image

      dw 3 years ago

      thank u sooooooooo much, u helped me a lot and i think ur awesome for taking the time to do this

    • profile image

      Kprinja 3 years ago

      thnx a ton for coding... working properly..:)

    • profile image

      Gandalf 3 years ago

      Thnx!

    • profile image

      Nabilah 3 years ago

      Thank you for this code. Its work :) .but if i wanna put image or pdf for download also what should I do then?

    • profile image

      özkan 3 years ago

      good example