Filter Search Result with Ajax, PHP & MySQL

In our previous tutorial, we have explained how to implement star rating system with Ajax, PHP & MySQL. In this tutorial, we will explain how to implement Filter Search Result with Ajax, PHP & MySQL.

Filter search result is a common feature of eCommerce website to provide relevant search result to user. The user can find the more relevant result from huge number of search records.

We will cover this tutorial step by step to implement live example with order data to search and filter search result with sorting order like Newest, Ascending, Descending, Processed order, Pending order, Cancelled order.

Also, read:

Autocomplete Search with Bootstrap, PHP & MySQL

So let’s implement Filter Search Result functionality with Ajax, PHP & MySQL. The major files are:

Index.php: Search and sorting interface and search result.
search.js: Handle search Ajax request and return response search result.
search_data.php: File to search and sorting functionality.
Search.php: A class that hold methods related to search.

Step1: Create MySQL Database Table

First we will create MySQL database table orders to store orders records to implement search functionality.

CREATE TABLE `orders` (
  `id` int(11) NOT NULL,
  `cname` varchar(255) NOT NULL,
  `item` varchar(255) NOT NULL,
  `value` double(12,2) NOT NULL,
  `date` date NOT NULL,
  `status` enum('Processed','Pending','Cancelled') NOT NULL DEFAULT 'Pending'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Insert orders record using following insert statement.

INSERT INTO `orders` (`id`, `cname`, `item`, `value`, `date`, `status`) VALUES
(1, 'Aston', 'Footbal', 1200.00, '2019-08-14', 'Processed'),
(2, 'Steve', 'Cricket Bat', 800.00, '2019-07-08', 'Cancelled'),
(3, 'Stephen', 'Java Book', 750.00, '2019-06-29', 'Processed'),
(4, 'Anthony', 'Washing Machine', 11250.00, '2019-05-20', 'Pending'),
(5, 'Victory', 'Shopping Trolley', 200.00, '2019-06-10', 'Pending'),
(6, 'Jessica', 'Sony Camera', 1200.00, '2019-06-06', 'Processed'),
(7, 'White', 'Bike Tires', 2000.00, '2019-05-28', 'Processed'),
(8, 'Richardson', 'Bike', 1600.00, '2019-04-18', 'Cancelled'),
(9, 'Marco', 'Computer', 1500.00, '2019-05-22', 'Pending'),
(10, 'Illiana', 'Laptop', 2800.00, '2019-06-30', 'Cancelled'),
(11, 'Smith', 'Sony TV', 1880.00, '2019-05-03', 'Pending'),
(12, 'David', 'iPhone ', 2225.00, '2019-06-01', 'Processed'),
(13, 'McGraw', 'iphone 7', 15620.00, '2019-05-30', 'Pending'),
(14, 'Gary', '3D Printer', 600.00, '2019-06-02', 'Processed'),
(15, 'Sarah', 'Kitchen Appliances', 35.00, '2019-07-29', 'Processed'),
(16, 'Thomas', 'SanDisk 32GB microSDHC', 85.00, '2019-04-05', 'Pending'),
(17, 'Helena', 'Tube Light', 12.00, '2019-05-04', 'Processed'),
(18, 'Nathan', 'Bluetooth', 20.00, '2019-06-07', 'Pending'),
(19, 'Ronald', 'shampoo', 10.00, '2019-06-13', 'Cancelled'),
(20, 'kristen', 'Mobile charger', 10.00, '2019-06-18', 'Pending');

Step2: Search with Filter Design and Search Result Listing

In index.php file, we will design search interface with filter and also implement to display search results by call search method searchResult().

<div class="container">
	<h2>Example: Filter Search Result with Ajax, PHP & MySQL</h2>
	<br>
	<div class="row">		
		<div class="form-group col-md-3">
			<input type="text" class="search form-control" id="keywords" name="keywords" placeholder="By customer or item">			
		</div>
		<div class="form-group col-md-2">
			<input type="button" class="btn btn-primary" value="Search" id="search" name="search" />
		</div>
		<div class="form-group col-md-4">
			<select class="form-control" id="sortSearch">
			  <option value="">Sort By</option>
			  <option value="new">Newest</option>
			  <option value="asc">Ascending</option>
			  <option value="desc">Descending</option>
			  <option value="Processed">Processed</option>
			  <option value="Pending">Pending</option>
			  <option value="Cancelled">Cancelled</option>
			</select>
		</div>
	</div>
    <div class="loading-overlay" style="display: none;"><div class="overlay-content">Loading.....</div></div>
    <table class="table table-striped">
        <thead>
            <tr>
				<th>ID</th>
				<th>Customer Name</th>
				<th>Order Item</th>
				<th>Value</th>
				<th>Date</th>
				<th>Status</th>
            </tr>
        </thead>
        <tbody id="userData">		
			<?php			
			include 'Search.php';
			$search = new Search();
			$allOrders = $search->searchResult(array('order_by'=>'id DESC'));      
			if(!empty($allOrders)) {
				foreach($allOrders as $order) {
					$status = '';
					if($order["status"] == 'Processed') {
						$status = 'btn-success';
					} else if($order["status"] == 'Pending') {
						$status = 'btn-warning';
					} else if($order["status"] == 'Cancelled') {
						$status = 'btn-danger';
					}
					echo '
					<tr>
					<td>'.$order["id"].'</td>
					<td>'.$order["cname"].'</td>
					<td>'.$order["item"].'</td>
					<td>$'.$order["value"].'</td>
					<td>'.$order["date"].'</td>
					<td><button type="button" class="btn '.$status.' btn-xs">'.$order["status"].'</button></td>
					</tr>';
				}
			} else {
			?>            
				<tr><td colspan="5">No user(s) found...</td></tr>
			<?php } ?>
        </tbody>
    </table>	
</div>

Step3:

In search.js file, we will handle search with filter by making Ajax request search_data.php which return search result HTML as response.

$(document).ready(function(){
	$("#search").click(function() {
		var keywords = $('#keywords').val();
		getData('search', keywords, '');
	});
	$("#sortSearch").change(function() {
		var sortValue = $(this).val();
		var keywords = $('#keywords').val();
		getData('sort', keywords, sortValue);
	});
});
function getData(type, keywords, sortValue) {	
	$.ajax({
		type: 'POST',
		url: 'search_data.php',
		data: 'type='+type+'&keywords='+keywords+'&sortValue='+sortValue,
		beforeSend:function(html){
			$('.loading-overlay').show();
		},
		success:function(html){
			$('.loading-overlay').hide();
			$('#userData').html(html);
		}
	});
}

Step4: Handle Search with Filter

In search_data.php file, we will handle functionality to search and filter search data by call Search.php class method searchResult() and search result HTML.

<?php
include 'Search.php';
$search = new Search();
$sqlConditions = array();
if(!empty($_POST['type']) && (!empty($_POST['keywords']) || !empty($_POST['sortValue']))){
    if($_POST['type'] == 'search'){
        $sqlConditions['search'] = array('cname'=>$_POST['keywords'],'item'=>$_POST['keywords'], 'value'=>$_POST['keywords']);
        $sqlConditions['order_by'] = 'id DESC';
    }elseif($_POST['type'] == 'sort'){
		if($_POST['keywords']) {
			$sqlConditions['search'] = array('cname'=>$_POST['keywords'],'item'=>$_POST['keywords'],'value'=>$_POST['keywords']);
		}
        $sortValue = $_POST['sortValue'];
        $sortArribute = array(
            'new' => array(
                'order_by' => 'date DESC'
            ),
            'asc'=>array(
                'order_by'=>'cname ASC'
            ),
            'desc'=>array(
                'order_by'=>'cname DESC'
            ),
            'Processed'=>array(
                'where'=>array('status'=>'Processed')
            ),
            'Pending'=>array(
                'where'=>array('status'=>'Pending')
            ),
			'Cancelled'=>array(
                'where'=>array('status'=>'Cancelled')
            )
        );
        $sortKey = key($sortArribute[$sortValue]);
        $sqlConditions[$sortKey] = $sortArribute[$sortValue][$sortKey];
    }
}else{
    $sqlConditions['order_by'] = 'id DESC';
}
$orders = $search->searchResult($sqlConditions);
if(!empty($orders)){    
	foreach($orders as $order){
		$status = '';
		if($order["status"] == 'Processed') {
			$status = 'btn-success';
		} else if($order["status"] == 'Pending') {
			$status = 'btn-warning';
		} else if($order["status"] == 'Cancelled') {
			$status = 'btn-danger';
		}
		echo '<tr>';
		echo '<td>'.$order['id'].'</td>';
		echo '<td>'.$order['cname'].'</td>';
		echo '<td>'.$order['item'].'</td>';
		echo '<td>'.$order['value'].'</td>';
		echo '<td>'.$order['date'].'</td>';
		echo '<td><button type="button" class="btn '.$status.' btn-xs">'.$order["status"].'</button></td>';
		echo '</tr>';
	}
}else{
    echo '<tr><td colspan="5">No user(s) found...</td></tr>';
}
exit;

We will define method searchResult() in class Search.php file to get search result with filter from MySQL database table.

<?php
class Search{
    private $host     = "localhost";
    private $username = "root";
    private $password = "";
    private $database     = "coderszine_demo";    
    public function __construct(){
        if(!isset($this->db)){           
            $conn = new mysqli($this->host, $this->username, $this->password, $this->database);
            if($conn->connect_error){
                die("Failed to connect with MySQL: " . $conn->connect_error);
            }else{
                $this->db = $conn;
            }
        }
    }
    public function searchResult($sqlQueryConditions = array()){
        $sqlQuery = 'SELECT ';
        $sqlQuery .= array_key_exists("select",$sqlQueryConditions)?$sqlQueryConditions['select']:'*';
        $sqlQuery .= ' FROM orders';
        if(array_key_exists("where",$sqlQueryConditions)){
            $sqlQuery .= ' WHERE ';
            $i = 0;
            foreach($sqlQueryConditions['where'] as $key => $value){
                $pre = ($i > 0)?' AND ':'';
                $sqlQuery .= $pre.$key." = '".$value."'";
                $i++;
            }
        }        
        if(array_key_exists("search",$sqlQueryConditions)){
            $sqlQuery .= (strpos($sqlQuery, 'WHERE') !== false)?' AND (':' WHERE (';
            $i = 0;
            foreach($sqlQueryConditions['search'] as $key => $value){
                $pre = ($i > 0)?' OR ':' ';
                $sqlQuery .= $pre.$key." LIKE '%".$value."%'";
                $i++;
            }
			$sqlQuery .= ")";
        }        
        if(array_key_exists("order_by",$sqlQueryConditions)){
            $sqlQuery .= ' ORDER BY '.$sqlQueryConditions['order_by']; 
        }        
        if(array_key_exists("start",$sqlQueryConditions) && array_key_exists("limit",$sqlQueryConditions)){
            $sqlQuery .= ' LIMIT '.$sqlQueryConditions['start'].','.$sqlQueryConditions['limit']; 
        }elseif(!array_key_exists("start",$sqlQueryConditions) && array_key_exists("limit",$sqlQueryConditions)){
            $sqlQuery .= ' LIMIT '.$sqlQueryConditions['limit']; 
        }
		$searchResult = $this->db->query($sqlQuery);       
        if(array_key_exists("return_type",$sqlQueryConditions) && $sqlQueryConditions['return_type'] != 'all'){
            switch($sqlQueryConditions['return_type']){
                case 'count':
                    $searchData = $searchResult->num_rows;
                    break;
                case 'single':
                    $searchData = $searchResult->fetch_assoc();
                    break;
                default:
                    $searchData = '';
            }
        }else{
            if($searchResult && $searchResult->num_rows > 0){
                while($row = $searchResult->fetch_assoc()){
                    $searchData[] = $row;
                }
            }
        }
        return !empty($searchData)?$searchData:false;
    }
}

You may also like:

You can view the live demo from the Demo link and can download the full project from the Download link below.
Demo Download

4 thoughts on “Filter Search Result with Ajax, PHP & MySQL”

Martin says:

March 24, 2020 at 2:41 pm

Excellent resource – thanks for sharing
coderszine team says:

March 28, 2020 at 6:42 am

You’re welcome, thanks!
Gary says:

January 31, 2021 at 11:13 am

Nice tutorial, how easy would it be to add pagination to this, especially when the filter is applied?
coderszine team says:

January 31, 2021 at 5:57 pm

You will have to handle all cases, we can not say it very easy but you can try and can implement this. thanks!

Comments are closed.