how to create search filter in php with jQuery Ajax & MySQL

In this tutorial you will learn how to create search filter in php from a MySQL database table based on specific conditions using PHP.

Today, We want to share with you how to create search filter in php.In this post we will show you jquery search filter, hear for category filter in php we will give you demo and example for implement.In this post, we will learn about how to search and display data from database in php with an example.


Auto Product Filter Search feature is very popular in online eCommerce website to allow product search with different options like. how to create search filter in php product type, price, rating or range filter and checkbox search filter etc.

how to create search filter in php using PDO

Filter search result is a common functionality of online eCommerce website to provide relevant search result to user Like as product brand,size,material checkbox search filtering using php and jquery.

how to create search filter in php
how to create search filter in php

So let’s implement Filter Search Result functionality with Ajax, PHP & MySQL.

The major files are:

  • Index.php
  • search.js
  • search_data.php
  • Search.php

Step 1: Create MySQL Database Table

CREATE TABLE `categorizations` (
  `id` int(11) NOT NULL,
  `user_name` varchar(255) NOT NULL,
  `product` 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 categorizations record using following insert statement.

INSERT INTO `categorizations` (`id`, `user_name`, `product`, `value`, `date`, `status`) VALUES
(1, 'Ravi', 'Mobile', 1200.00, '2022-08-14', 'Processed'),
(2, 'Kajal', 'Cricket Bat', 800.00, '2022-07-08', 'Cancelled'),
(3, 'Bhoomi', 'Laptop', 750.00, '2022-06-29', 'Processed'),
(4, 'Sejal', 'Washing Machine', 11250.00, '2022-05-20', 'Pending'),
(5, 'Dahra', 'Shopping Trolley', 200.00, '2022-06-10', 'Pending'),
(6, 'Bhagyshree', 'Sony Camera', 1200.00, '2022-06-06', 'Processed'),
(7, 'Jalpa', 'Bike Tires', 2000.00, '2022-05-28', 'Processed'),
(8, 'Bhavika', 'Bike', 1600.00, '2022-04-18', 'Cancelled'),
(9, 'Monika', 'Computer', 1500.00, '2022-05-22', 'Pending'),
(10, 'Ankita', 'Laptop', 2800.00, '2022-06-30', 'Cancelled'),
(11, 'Dimple', 'Sony TV', 1880.00, '2022-05-03', 'Pending'),
(12, 'Kinara', 'iPhone ', 2225.00, '2022-06-01', 'Processed'),
(13, 'Falhuni', 'iphone 7', 15620.00, '2022-05-30', 'Pending'),
(14, 'Meera', '3D Printer', 600.00, '2022-06-02', 'Processed'),
(15, 'Jayshree', 'Kitchen Appliances', 35.00, '2022-07-29', 'Processed'),
(16, 'Astha', 'SanDisk 32GB microSDHC', 85.00, '2022-04-05', 'Pending'),
(17, 'Mithi', 'Tube Light', 12.00, '2022-05-04', 'Processed'),
(18, 'Dipti', 'Bluetooth', 20.00, '2022-06-07', 'Pending'),
(19, 'Shital', 'shampoo', 10.00, '2022-06-13', 'Cancelled'),
(20, 'Laxmi', 'Mobile charger', 10.00, '2022-06-18', 'Pending');

Step 2: Search with Filter Design and Search Result Listing

Search and sorting interface and search result.

<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 product">			
		</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>Categorization Item</th>
				<th>Value</th>
				<th>Date</th>
				<th>Status</th>
            </tr>
        </thead>
        <tbody id="userData">		
			<?php			
			include 'Search.php';
			$search = new Search();
			$allCategorizations = $search->searchResult(array('categorization_by'=>'id DESC'));      
			if(!empty($allCategorizations)) {
				foreach($allCategorizations as $categorization) {
					$status = '';
					if($categorization["status"] == 'Processed') {
						$status = 'btn-success';
					} else if($categorization["status"] == 'Pending') {
						$status = 'btn-warning';
					} else if($categorization["status"] == 'Cancelled') {
						$status = 'btn-danger';
					}
					echo '
					<tr>
					<td>'.$categorization["id"].'</td>
					<td>'.$categorization["user_name"].'</td>
					<td>'.$categorization["product"].'</td>
					<td>$'.$categorization["value"].'</td>
					<td>'.$categorization["date"].'</td>
					<td><button type="button" class="btn '.$status.' btn-xs">'.$categorization["status"].'</button></td>
					</tr>';
				}
			} else {
			?>            
				<tr><td colspan="5">No user(s) found...</td></tr>
			<?php } ?>
        </tbody>
    </table>	
</div>

Step 3: handle search with filter by making Ajax request

Handle search jquery Ajax request and return response search result.

$(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);
		}
	});
}

Step 4: Handle Search with Filter

File to search and sorting functionality.

<?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('user_name'=>$_POST['keywords'],'product'=>$_POST['keywords'], 'value'=>$_POST['keywords']);
        $sqlConditions['categorization_by'] = 'id DESC';
    }elseif($_POST['type'] == 'sort'){
		if($_POST['keywords']) {
			$sqlConditions['search'] = array('user_name'=>$_POST['keywords'],'product'=>$_POST['keywords'],'value'=>$_POST['keywords']);
		}
        $sortValue = $_POST['sortValue'];
        $sortArribute = array(
            'new' => array(
                'categorization_by' => 'date DESC'
            ),
            'asc'=>array(
                'categorization_by'=>'user_name ASC'
            ),
            'desc'=>array(
                'categorization_by'=>'user_name 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['categorization_by'] = 'id DESC';
}
$categorizations = $search->searchResult($sqlConditions);
if(!empty($categorizations)){    
	foreach($categorizations as $categorization){
		$status = '';
		if($categorization["status"] == 'Processed') {
			$status = 'btn-success';
		} else if($categorization["status"] == 'Pending') {
			$status = 'btn-warning';
		} else if($categorization["status"] == 'Cancelled') {
			$status = 'btn-danger';
		}
		echo '<tr>';
		echo '<td>'.$categorization['id'].'</td>';
		echo '<td>'.$categorization['user_name'].'</td>';
		echo '<td>'.$categorization['product'].'</td>';
		echo '<td>'.$categorization['value'].'</td>';
		echo '<td>'.$categorization['date'].'</td>';
		echo '<td><button type="button" class="btn '.$status.' btn-xs">'.$categorization["status"].'</button></td>';
		echo '</tr>';
	}
}else{
    echo '<tr><td colspan="5">No user(s) found...</td></tr>';
}
exit;

Step 5: Filter Search Result with Ajax, PHP & MySQL

A class that hold methods related to search.

<?php
class Search{
    private $host     = "localhost";
    private $username = "root";
    private $password = "";
    private $database     = "pakainfo_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 categorizations';
        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("categorization_by",$sqlQueryConditions)){
            $sqlQuery .= ' ORDER BY '.$sqlQueryConditions['categorization_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;
    }
}

I hope you get an idea about how to create search filter in php.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.