Product Filter Search with Ajax, PHP & MySQL

Product Filter Search functionality is very popular in eCommerce website to allow product search with different options like product price range filter and checkbox search filter etc. So if you’re thinking about implementing product filter search functionality in your project with product feature options, then you’re here at right place. In this tutorial you will learn how to build product search filter with Ajax, PHP and MySQL.

Also, read:

We will cover this tutorial in easy steps to display product feature options for filter search with price range slider and display filter search result accordingly with Ajax, PHP and MySQL.

As we will cover this tutorial with live example to build product search filter with Ajax, PHP & MySQL, so the major files for this example is following.

index.php
search.js
action.php
Product.php

Step1: Create MySQL Database Tables
First we will create table product_details to store the product details to display according to search filter.

CREATE TABLE `product_details` (
  `id` int(20) NOT NULL,
  `name` varchar(120) NOT NULL,
  `brand` varchar(100) NOT NULL,
  `price` decimal(8,2) NOT NULL,
  `ram` char(5) NOT NULL,
  `storage` varchar(50) NOT NULL,
  `camera` varchar(20) NOT NULL,
  `image` varchar(100) NOT NULL,
  `quantity` mediumint(5) NOT NULL,
  `status` enum('0','1') NOT NULL COMMENT '0-active,1-inactive'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

We will insert few records into product_details table for this example.

INSERT INTO `product_details` (`id`, `name`, `brand`, `price`, `ram`, 
`storage`, `camera`, `image`, `quantity`, `status`) VALUES
(1, 'Honor 9 Lite (Sapphire Black, 64 GB)  (4 GB RAM)', 'Honor', '14499.00', '4', '64', '13', '1.png', 10, '1'),
(2, 'Infinix (Sandstone Blue, 32 GB)  (3 GB RAM)', 'Infinix', '8999.00', '3', '32', '13', '2.png', 10, '1'),
(3, 'VIVO V8 Youth (Black, 32 GB)  (4 GB RAM)', 'VIVO', '16990.00', '4', '32', '16', '3.png', 10, '1'),
(4, 'Moto (Gold, 32 GB)  (3 GB RAM)', 'Moto', '11499.00', '3', '32', '8', '4.png', 10, '1'),
(5, 'Lenovo (Venom Black, 32 GB)  (3 GB RAM)', 'Lenevo', '8999.00', '3', '32', '13', '5.png', 10, '1'),
(6, 'Samsung Galaxy (Gold, 16 GB)  (3 GB RAM)', 'Samsung', '11990.00', '3', '16', '13', '6.png', 10, '1'),
(7, 'Moto Plus (Pearl White, 16 GB)  (2 GB RAM)', 'Moto', '8799.00', '2', '16', '8', '7.png', 10, '1'),
(8, 'Panasonic (White, 16 GB)  (1 GB RAM)', 'Panasonic', '6999.00', '1', '16', '8', '8.png', 10, '1'),
(9, 'OPPO (Black, 64 GB)  (6 GB RAM)', 'OPPO', '18990.00', '6', '64', '16', '9.png', 10, '1'),
(10, 'Honor 7 (Gold, 32 GB)  (3 GB RAM)', 'Honor', '9999.00', '3', '32', '13', '10.png', 10, '1'),
(11, 'Asus ZenFone (Midnight Blue, 64 GB)  (6 GB RAM)', 'Asus', '27999.00', '6', '128', '12', '11.png', 10, '1'),
(12, 'Redmi 5A (Gold, 32 GB)  (3 GB RAM)', 'MI', '5999.00', '3', '32', '13', '12.png', 10, '1'),
(13, 'Intex (Black, 16 GB)  (2 GB RAM)', 'Intex', '5999.00', '2', '16', '8', '13.png', 10, '1'),
(14, 'Google Pixel (18:9 Display, 64 GB) White', 'Google', '62990.00', '4', '64', '12', '14.png', 10, '1');

Step2: Include Bootstrap, jQuery and Bootstrap Slider
As we will handle design with Bootstrap, so first we will include bootstrap, jQuery and Bootstrap slider in index.php file.

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/
css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/
jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/
js/bootstrap.min.js"></script>
<link href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-slider/9.8.0/css/
bootstrap-slider.min.css" rel="stylesheet"/>
<script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-slider/9.8.0/
bootstrap-slider.min.js"></script>
<script src="js/search.js"></script>
<link rel="stylesheet" href="css/style.css">

Step3: Display Product Filter Search Options
Now in index.php file, we will display product filter search options to allow users to search product with filter. We will include class Product.php and then call methods to display filter options values from MySQL database table product_details. We will also create price range slider with Bootstrap slider to search product with price range.

<div class="container">		
	<?php
	include 'class/Product.php';
	$product = new Product();	
	?>	
	<div class="row">
	<div class="col-md-3">                    
		<div class="list-group">
			<h3>Price</h3>	
			<div class="list-group-item">
				<input id="priceSlider" data-slider-id='ex1Slider' 
type="text" data-slider-min="1000" data-slider-max="65000" data-slider-step="1" data-slider-value="14"/>
				<div class="priceRange">1000 - 65000</div>
				<input type="hidden" id="minPrice" value="0" />
				<input type="hidden" id="maxPrice" value="65000" />                  
			</div>			
		</div>    
		<div class="list-group">
			<h3>Brand</h3>
			<div class="brandSection">
				<?php
				$brand = $product->getBrand();
				foreach($brand as $brandDetails){	
				?>
				<div class="list-group-item checkbox">
				<label><input type="checkbox" 
class="productDetail brand" value="<?php echo $brandDetails["brand"]; ?>"  > <?php echo $brandDetails["brand"]; ?></label>
				</div>
				<?php }	?>
			</div>
		</div>
		<div class="list-group">
			<h3>RAM</h3>
			<?php			
			$ram = $product->getRam();
			foreach($ram as $ramDetails){	
			?>
			<div class="list-group-item checkbox">
			<label><input type="checkbox" class="productDetail ram" 
value="<?php echo $ramDetails['ram']; ?>" > <?php echo $ramDetails['ram']; ?> GB</label>
			</div>
			<?php    
			}
			?>
		</div>    
		<div class="list-group">
			<h3>Internal Storage</h3>
			<?php
			$storage = $product->getStorage();
			foreach($storage as $storageDetails){	
			?>
			<div class="list-group-item checkbox">
			<label><input type="checkbox" class="productDetail storage" 
value="<?php echo $storageDetails['storage']; ?>"  > <?php echo $storageDetails['storage']; ?> GB</label>
			</div>
			<?php
			}
			?> 
		</div>
	</div>
	<div class="col-md-9">
		<div class="row searchResult">
	</div>
	</div>
    </div>	
</div>

We will also create searchResult container to display filter search result using jQuery Ajax.

Step4: Make Product Filter Search Ajax Request
In search.js, we will define a function filterSearch() to make Ajax request according to filter search option display search result. The Ajax request made to action.php to load search data from MySQL database table as JSON response.

function filterSearch() {
	$('.searchResult').html('<div id="loading">Loading .....</div>');
	var action = 'fetch_data';
	var minPrice = $('#minPrice').val();
	var maxPrice = $('#maxPrice').val();
	var brand = getFilterData('brand');
	var ram = getFilterData('ram');
	var storage = getFilterData('storage');
	$.ajax({
		url:"action.php",
		method:"POST",
		dataType: "json",		
		data:{action:action, minPrice:	minPrice, maxPrice:maxPrice, 
brand:brand, ram:ram, storage:storage},
		success:function(data){
			$('.searchResult').html(data.html);
		}
	});
}

Step5: Call Product Filter Search Method
In action.php file, we will include class Product.php and call method $product->searchProducts() to get search result HTML and passed as JSON response using json_encode.

<?php
include 'class/Product.php';
$product = new Product();
if(isset($_POST["action"])){
	$html = $product->searchProducts($_POST);
	$data = array(
		"html"	=> $html,	
	);
	echo json_encode($data);	
}
?>

Step6: Get Product Filter Search Data from MySQL Database Table
In class Product.php, we define method searchProducts() to get product filter search data from MySQL database table. We will create SELECT query with filter search options and get data. Then create result HTML with result data and return as complete search result HTML.

public function searchProducts(){
	$sqlQuery = "SELECT * FROM ".$this->productTable." WHERE status = '1'";
	if(isset($_POST["minPrice"], $_POST["maxPrice"]) && 
!empty($_POST["minPrice"]) && !empty($_POST["maxPrice"])){
		$sqlQuery .= "
		AND price BETWEEN '".$_POST["minPrice"]."' AND '".$_POST["maxPrice"]."'";
	}
	if(isset($_POST["brand"])) {
		$brandFilterData = implode("','", $_POST["brand"]);
		$sqlQuery .= "
		AND brand IN('".$brandFilterData."')";
	}
	if(isset($_POST["ram"])){
		$ramFilterData = implode("','", $_POST["ram"]);
		$sqlQuery .= "
		AND ram IN('".$ramFilterData."')";
	}
	if(isset($_POST["storage"])) {
		$storageFilterData = implode("','", $_POST["storage"]);
		$sqlQuery .= "
		AND storage IN('".$storageFilterData."')";
	}
	$sqlQuery .= " ORDER By price";
	$result = mysqli_query($this->dbConnect, $sqlQuery);
	$totalResult = mysqli_num_rows($result);
	$searchResultHTML = '';
	if($totalResult > 0) {
		while ($row = mysqli_fetch_array($result, MYSQL_ASSOC)) {
			$searchResultHTML .= '
			<div class="col-sm-4 col-lg-3 col-md-3">
			<div class="product">
			<img src="images/'. $row['image'] .'" 
alt="" class="img-responsive" >
			<p align="center"><strong><a 
href="#">'. $row['name'] .'</a></strong></p>
			<h4 style="text-align:center;" class="text-danger" 
>'. $row['price'] .'</h4>
			<p>Camera : '. $row['camera'].' MP<br />
			Brand : '. $row['brand'] .' <br />
			RAM : '. $row['ram'] .' GB<br />
			Storage : '. $row['storage'] .' GB </p>
			</div>
			</div>';
		}
	} else {
		$searchResultHTML = '<h3>No product found.</h3>';
	}
	return $searchResultHTML;	
}

10 thoughts on “Product Filter Search with Ajax, PHP & MySQL”

Mohd Faraz says:

3 June, 2019 at 7:39 am

There is no pagination.
Can you add pagination.

1. PHPZAG Team says:
  
  16 June, 2019 at 5:51 pm
  
  I will try to add in future. Thanks!
  
  1. Ranjith says:
    
    22 January, 2021 at 10:14 am
    
    Hi…
    Nice work..
    can you add pagination..
    
    1. PHPZAG Team says:
      
      23 January, 2021 at 5:58 pm
      
      I will try to add this. thanks!
      
Ghanshyam Dhaduk says:

9 August, 2020 at 3:05 pm

Great piece of code.

aditya says:

4 May, 2021 at 5:16 pm

can you add pagination

1. PHPZAG Team says:
  
  8 May, 2021 at 11:24 am
  
  I will try to add this when get time and update you. thanks!
  
2. david5786 says:
  
  26 January, 2022 at 10:06 pm
  
  Hello, do you find a solution to add pagination to this feature ? thans a lot
  
pooya says:

12 December, 2021 at 2:23 pm

thank you.
Can you use the infinite scroll in this script?
I really need this feature.

Tony Auckland says:

27 May, 2022 at 12:50 pm

Hi,

There are two errors in the Product.php file in the downloadable demo, and the above example.

MYSQL_ASSOC should be MYSQLI_ASSOC (ie, the I is missing)

Tony

Product Filter Search with Ajax, PHP & MySQL

10 thoughts on “Product Filter Search with Ajax, PHP & MySQL”

Leave a Reply Cancel reply

Latest Tutorials

10 thoughts on “Product Filter Search with Ajax, PHP & MySQL”

Leave a Reply Cancel reply

Latest Tutorials

Popular Tags