Advanced Search using PHP

by Vincy. Last modified on July 10th, 2022.

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.

advanced-search-using-php

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

Leave a Reply

Your email address will not be published. Required fields are marked *

↑ Back to Top

Share this page