Advanced Search using PHP

Advanced search provides more options to the user to filter and narrow down the search result. Advanced search will be convenient for the end users and a search implementation without advanced option will be useless in many cases. Even Google search has ‘advanced search’ option.

In a previous tutorial, we have seen simple PHP search to filter MySQL records with just one field to enter the search keyword and get results based on it. In this PHP article, let us enrich by adding more fields and options and implement the PHP advanced search by filtering the MySQL data.

In the advanced search form, we have inputs to search with respect to the exact or any one of a word from given phrase, to exclude given string and to search results that start with given word.

And also we can also choose the database column in which the search should be happening. I have also added PHP search example for highlighting keywords in the search results.

HTML Advanced Search Form

This code contains HTML form inputs for the advanced search option. On page load, it will show a single input to type the search keyword.

If you want to filter the results with more criteria, then the Advanced Search link will show more inputs to do the database search.

<form name="frmSearch" method="post" action="index.php">
	<input type="hidden" id="advance_search_submit" name="advance_search_submit" value="<?php echo $advance_search_submit; ?>">
	<div class="search-box">
		<label class="search-label">With Any One of the Words:</label>
		<div>
			<input type="text" name="search[with_any_one_of]" class="demoInputBox" value="<?php echo $with_any_one_of; ?>"	/>
			<span id="advance_search_link" onClick="showHideAdvanceSearch()">Advance Search</span>
		</div>				
		<div id="advanced-search-box" <?php if(empty($advance_search_submit)) { ?>style="display:none;"<?php } ?>>
			<label class="search-label">With the Exact String:</label>
			<div>
				<input type="text" name="search[with_the_exact_of]" id="with_the_exact_of" class="demoInputBox" value="<?php echo $with_the_exact_of; ?>"	/>
			</div>
			<label class="search-label">Without:</label>
			<div>
				<input type="text" name="search[without]" id="without" class="demoInputBox" value="<?php echo $without; ?>"	/>
			</div>
			<label class="search-label">Starts With:</label>
			<div>
				<input type="text" name="search[starts_with]" id="starts_with" class="demoInputBox" value="<?php echo $starts_with; ?>"	/>
			</div>
			<label class="search-label">Search Keywords in:</label>
			<div>
				<select name="search[search_in]" id="search_in" class="demoInputBox">
					<option value="">Select Column</option>
					<option value="title" <?php if($search_in=="title") { echo "selected"; } ?>>Title</option>
					<option value="description" <?php if($search_in=="description") { echo "selected"; } ?>>Description</option>
				</select>
			</div>
		</div>
		
		<div>
			<input type="submit" name="go" class="btnSearch" value="Search">
		</div>
	</div>
</form>

Creating Advance Search Condition in PHP

This code receives form inputs and forms MySQL query with the advanced search condition. If the user prefers database columns to search then the search will apply on that column. Otherwise, we will search in all the columns. The code is,

<php
	$conn = mysqli_connect("localhost", "root", "", "blog_samples");	
	$with_any_one_of = "";
	$with_the_exact_of = "";
	$without = "";
	$starts_with = "";
	$search_in = "";
	$advance_search_submit = "";
	
	$queryCondition = "";
	if(!empty($_POST["search"])) {
		$advance_search_submit = $_POST["advance_search_submit"];
		foreach($_POST["search"] as $k=>$v){
			if(!empty($v)) {

				$queryCases = array("with_any_one_of","with_the_exact_of","without","starts_with");
				if(in_array($k,$queryCases)) {
					if(!empty($queryCondition)) {
						$queryCondition .= " AND ";
					} else {
						$queryCondition .= " WHERE ";
					}
				}
				switch($k) {
					case "with_any_one_of":
						$with_any_one_of = $v;
						$wordsAry = explode(" ", $v);
						$wordsCount = count($wordsAry);
						for($i=0;$i<$wordsCount;$i++) {
							if(!empty($_POST["search"]["search_in"])) {
								$queryCondition .= $_POST["search"]["search_in"] . " LIKE '%" . $wordsAry[$i] . "%'";
							} else {
								$queryCondition .= "title LIKE '" . $wordsAry[$i] . "%' OR description LIKE '" . $wordsAry[$i] . "%'";
							}
							if($i!=$wordsCount-1) {
								$queryCondition .= " OR ";
							}
						}
						break;
					case "with_the_exact_of":
						$with_the_exact_of = $v;
						if(!empty($_POST["search"]["search_in"])) {
							$queryCondition .= $_POST["search"]["search_in"] . " LIKE '%" . $v . "%'";
						} else {
							$queryCondition .= "title LIKE '%" . $v . "%' OR description LIKE '%" . $v . "%'";
						}
						break;
					case "without":
						$without = $v;
						if(!empty($_POST["search"]["search_in"])) {
							$queryCondition .= $_POST["search"]["search_in"] . " NOT LIKE '%" . $v . "%'";
						} else {
							$queryCondition .= "title NOT LIKE '%" . $v . "%' AND description NOT LIKE '%" . $v . "%'";
						}
						break;
					case "starts_with":
						$starts_with = $v;
						if(!empty($_POST["search"]["search_in"])) {
							$queryCondition .= $_POST["search"]["search_in"] . " LIKE '" . $v . "%'";
						} else {
							$queryCondition .= "title LIKE '" . $v . "%' OR description LIKE '" . $v . "%'";
						}
						break;
					case "search_in":
						$search_in = $_POST["search"]["search_in"];
						break;
				}
			}
		}
	}
	$orderby = " ORDER BY id desc"; 
	$sql = "SELECT * FROM links " . $queryCondition;
	$result = mysqli_query($conn,$sql);
?>

Database Script with Structure and Data of the MySQL table

This database script is used to import the links table structure and the data to run this example PHP code.

-- Table structure for table `links`

CREATE TABLE `links` (
  `id` int(8) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `votes` tinyint(2) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping data for table `links`

INSERT INTO `links` (`id`, `title`, `description`, `votes`) VALUES
(1, 'Favorite Star Rating with jQuery', 'This tutorial is for doing favorite star rating using jQuery. It displays list of HTML stars by using li tags. These stars are highlighted by using CSS and jQuery based on the favorite rating selected by the user.', 1),
(2, 'PHP RSS Feed Read and List', 'PHP\'s simplexml_load_file() function is used for reading data from xml file. Using this function, we can parse RSS feed to get item object array.', 0),
(3, 'jQuery AJAX Autocomplete - Country Example', 'Autocomplete feature is used to provide auto suggestion for users while entering input. It suggests country names for the users based on the keyword they entered into the input field by using jQuery AJAX.', 0),
(4, 'PHP CRUD with Search and Pagination', 'We have search options for searching the Name and Code columns by the given keywords posted via the search form. The search keyword is used to find match with the values of corresponding columns by using MySQL LIKE clause.', 0),
(5, 'DropDown with Search using jQuery', 'Search is an useful feature for a HTML dropdown list. Especially it will increase user convenience to select items from the dropdown having long list. In this tutorial, we are going to list country dropdown with a search option.', 0),
(6, 'PHP MySQL Date Range Search with jQuery DatePicker', 'how to search database records date between two given ranges. It will return the filtered results from database based on these dates input.', 0);

-- Indexes for table `links`
ALTER TABLE `links`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `links`
  MODIFY `id` int(8) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
COMMIT;

Download

Written by Vincy, a web developer with 15+ years of experience and a Masters degree in Computer Science. She specializes in building modern, lightweight websites using PHP, JavaScript, React, and related technologies. Phppot helps you in mastering web development through over a decade of publishing quality tutorials.

↑ Back to Top

• PHP
  • Learn PHP
  • PHP Introduction
  • PHP Basics
  • PHP Shopping Cart
  • Payment Gateway
  • Contact Form
  • PHP Login Registration
  • Comments System
  • Types, Variables & Operators
  • PHP Strings
  • PHP Arrays
  • PHP Functions
  • PHP OOPS
  • Event Management System
  • PHP Mail
  • PHP Forms
  • Advanced
  • PHP AJAX
  • RESTful API
  • PHP Databases
  • PHP Sessions and Cookies
  • Error and Exception Handling
  • File Upload
  • File Import Export
  • Files and Directories
  • PHP Date Time
  • PHP XML
  • PHP CSV
  • PHP JSON
  • PHP Code Samples
    • URI Shortener PHP Script
    • Create Shortener to Convert Long URL to Short in JavaScript
    • How to Add CSRF Token to AJAX Request
    • PHP MySQL Date Range Search with jQuery DatePicker
    • Advanced Search using PHP
    • PHP CRUD with Search and Pagination
    • Highlighting Keywords in Search Results with PHP
    • PHP Pagination MySQL Database Example Script with Previous Next like Google
    • Command Line PHP
    • How to Create Custom Image File Explorer in PHP
    • Web Scraping with PHP - Tutorial to Scrape Web Pages
    • User Login Session Timeout Logout in PHP
    • PHP Change Password Script
    • Object-Oriented Poll System using PHP
    • PHP cURL
    • Extract Content using PHP and Preview like Facebook
    • Show Image Thumbnail by YouTube Video URL using PHP
    • Star Rating with PHP and jQuery AJAX
    • Simple HTML 5 star rating code
    • How to Crop Image using jQuery and PHP
    • How to Generate Initial Avatar Image from Username using PHP Dynamically
    • Live search in PHP and MySQL with AJAX
  • Library
  • PHP Freelancer
• jQuery
• JavaScript
• WordPress
• MySQL
• CSS

• Share
• Tweet
• Share

1. Simple PHP Shopping Cart
2. Stripe Payment Gateway Integration using PHP
3. User Registration in PHP with Login: Form with MySQL and Code Download
4. PHP Contact Form
5. How to Create Dynamic Stacked Bar, Doughnut and Pie charts in PHP with Chart.js