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

      pratik 33 hours ago

      do u have any video tutorial of the following project?

    • profile image

      John Gachena 7 days ago

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

    • profile image

      verolla 8 days ago

      Working really well. Thank you!

    • profile image

      Ross 11 days ago

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

    • profile image

      lg 11 days ago

      good but not working

    • profile image

      kouassi 2 weeks ago

      nice an simple code . thank

    • profile image

      dean 3 weeks ago

      hello how are u

    • profile image

      calpizsoftware 3 weeks ago

      Error Error

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

      }

      also $min_length = 3; this causes an error

    • profile image

      mbonisi tshuma 3 weeks ago

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

    • profile image

      bhanu 7 weeks ago

      awesome...

      very easy

    • profile image

      fhj 8 weeks ago

      mysql deprecated - please use mysqli or PDO

    • profile image

      bn hjnb 2 months ago

      love it

    • profile image

      kingfisher 2 months ago

      thank you

    • profile image

      shamli 2 months ago

      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

    • profile image

      Sam 2 months ago

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

    • profile image

      oka 2 months ago

      the code is easy but not working

    • profile image

      Sam 3 months ago

      your code not working properly but easy to understand

    • profile image

      paul 3 months ago

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

    • profile image

      CK Mohammed 3 months ago

      Very good, Thanks

    • profile image

      SamGidolew 4 months ago

      Excellent frnd. thank you

    • profile image

      DON 4 months ago

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

    • profile image

      habtom 4 months ago

      I like your web sits

    • profile image

      krishna 4 months ago

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

    • profile image

      scott 4 months ago

      This is awesome need a mysqli version

    • profile image

      raj kishor yadav 5 months ago

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

      keep on sharing such high valuable content.

    • profile image

      onyy 5 months ago

      tahnk you so much

    • profile image

      sjaan 6 months ago

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

    • profile image

      dila 6 months ago

      why $query is undefined index? please help me ASAP

    • profile image

      sly 6 months ago

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

    • profile image

      uselesstrash 6 months ago

      not work, don't use this useless code

    • profile image

      to lloyd stephenn 6 months ago

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

    • profile image

      lloyd 6 months ago

      thanks a lot man.

    • profile image

      lloyd stephenn 6 months ago

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

    • profile image

      Zarif joya 6 months ago

      Thanks for this good code

    • profile image

      sanjay 6 months ago

      It's not working to me.

    • profile image

      unknown 6 months ago

      It's not working to me.

    • profile image

      unknown 6 months ago

      where's the output?

    • profile image

      Name 6 months ago

      Just testing

    • profile image

      judith 7 months ago

      hy how to search from two different tables ?

    • profile image

      CK 7 months ago

      Thank you - worked a treat, much appreciated.

      Now looking for sqli or pdo search ;-)

    • profile image

      joyce 8 months ago

      code not working please help

    • profile image

      hassam 9 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 9 months ago

      Thanks

    • profile image

      elle 10 months ago

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

    • profile image

      Ewan McQueen 10 months ago

      I have the search results showing but no pictures

    • profile image

      Ewan 10 months ago

      I have the search showing but no pictures

    • profile image

      Tosin 10 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 10 months ago

      results?

    • profile image

      kkds 10 months ago

      ya brother good

    • profile image

      OBITEX 10 months ago

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

    • profile image

      dzaky 10 months ago

      thanks dude

    • profile image

      Ibro 11 months ago

      This is Awesome And Perfect , Thank you!

    • profile image

      Malik Ansar 11 months ago

      very helpful and easy

    • profile image

      www.tutoralert.com 11 months ago

      very help full codding

    • profile image

      Ewan McQueen 11 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 12 months ago

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

    • profile image

      Kedar Maharjan 12 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 12 months ago

      wonderful code

    • profile image

      Jack Johnson 13 months ago

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

    • profile image

      ashton 13 months ago

      i love you man, thank you so much :D

    • profile image

      saeed 13 months ago

      simple for beginner

    • profile image

      Nipuna1 13 months ago

      Thank you!!

    • profile image

      Akash 13 months ago

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

    • profile image

      Fidele 13 months ago

      Very helpful! Thanks an way!

    • profile image

      Akash Kumar 14 months ago

      Working fine

    • profile image

      Vishwas 14 months ago

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

    • profile image

      iqbal 14 months ago

      nice. this is working. thanks. i use it

    • profile image

      Iam_Fortunatus 14 months ago

      Thank for the code sir

    • profile image

      Dan 14 months ago

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

    • profile image

      ramesh giri 14 months ago

      very nice very useful thank you

    • profile image

      Afrosith 14 months ago

      Awesome... Its really did it..

    • profile image

      flamboyantandy 15 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 15 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 15 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 16 months ago

      useful code

    • profile image

      DököllSocial 16 months ago

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

    • profile image

      chen chen nue 16 months ago

      very nice coding

    • profile image

      Rads 16 months ago

      Thanks a lot for this coding. it works Perfect.

    • profile image

      aswini ravella 16 months ago

      excellent post thak u for helping me

    • profile image

      how insert search word into mysql 16 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 16 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 16 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 16 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 16 months ago

      thanks man a lot 6 :)

    • profile image

      azlan 17 months ago

      very good coding

    • profile image

      Ashfaq 17 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 17 months ago

      Thx Man For Helping me its simple and nice codeing

    • profile image

      bakhtawar komal 17 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 17 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 18 months ago

      simple bt powerful

    • profile image

      Manav Kharat 18 months ago

      Thanks

      Your article helped me a lot...

    • profile image

      Hussein Sarkar 18 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 18 months ago

      hey, awesome coding and well explained. Thanks alot!

    • profile image

      sreylak 19 months ago

      so nice code , thanks

    • profile image

      Frazier 19 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 19 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 19 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 20 months ago

      helped alot thnx :)

    • profile image

      Sujon 20 months ago

      If I wrote

      $query = $_POST['query'];

      Showing Length :

      Minimum length is 3

    • profile image

      Mohammed Shamshid 21 months ago

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

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, owlcation.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: "https://owlcation.com/privacy-policy#gdpr"

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)