Product Search Filtering using Ajax, PHP & MySQL

Product Filtering is an important functionality of eCommerce websites to allow users to filter products listing according to requirement.

So if you’re looking for solution to implement product search filtering in your project then you’re here at right place. In our previous tutorial, you have learned to implement your own captcha script in PHP. In this tutorial you will learn how to implement Product Search Filtering using Ajax, PHP & MySQL.

We will cover this tutorial with live example with product filtering functionality just like eCommerce website to filter product with categories, brands, size, material and sort with latest product and price.

Also, read:

So let’s implement product search filtering with PHP & MySQL. The major files are:

index.php
script.js
load_products.php
Product.php: A class that hold methods related to products.

Step1: Create MySQL Database Table

First we will create MySQL database table products to store product details and display with filter functionality.

CREATE TABLE `products` (
  `id` int(11) NOT NULL,
  `sku_id` varchar(50) NOT NULL,
  `category_id` int(11) NOT NULL,
  `category_name` varchar(255) NOT NULL,
  `product_name` varchar(300) NOT NULL,
  `image` varchar(300) NOT NULL,
  `price` int(11) NOT NULL,
  `brand` varchar(100) NOT NULL,
  `material` varchar(100) NOT NULL,
  `size` varchar(20) NOT NULL,
  `qty` int(11) NOT NULL,
  `created_date` datetime NOT NULL,
  `updated_date` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Step2: Design Form with Filter Options

In index.php file, we will design HTML Form to display product filter options and product listing. We will also create a container with id results to list products.

<form method="post" id="search_form">               
	<div class="row">                    
		<aside class="col-lg-3 col-md-4">						
			<div class="panel list">
				<div class="panel-heading"><h3 class="panel-title" data-toggle="collapse" data-target="#panelOne" aria-expanded="true">Categories</h3></div>
				<div class="panel-body collapse in" id="panelOne">
					<ul class="list-group">
					<?php 
					foreach ($categories as $key => $category) {
						if(isset($_POST['category'])) {
							if(in_array($product->cleanString($category['category_id']),$_POST['category'])) {
								$categoryCheck ='checked="checked"';
							} else {
								$categoryCheck="";
							}
						}
					?>
					<li class="list-group-item">
						<div class="checkbox"><label><input type="checkbox" value="<?php echo $product->cleanString($category['category_id']); ?>" <?php echo @$categoryCheck; ?> name="category[]" class="sort_rang category"><?php echo ucfirst($category['category_name']); ?></label></div>
					</li>
					<?php } ?>
					</ul>
				</div>
			</div>
			<div class="panel list">
				<div class="panel-heading"><h3 class="panel-title" data-toggle="collapse" data-target="#panelOne" aria-expanded="true">Brand</h3></div>
				<div class="panel-body collapse in" id="panelOne">
					<ul class="list-group">
					<?php 
					foreach ($brands as $key => $brand) {
						if(isset($_POST['brand'])) {
							if(in_array($product->cleanString($brand['brand']),$_POST['brand'])) {
								$brandChecked ='checked="checked"';
							} else {
								$brandChecked="";
							}
						}
					?>
					<li class="list-group-item">
						<div class="checkbox"><label><input type="checkbox" value="<?php echo $product->cleanString($brand['brand']); ?>" <?php echo @$brandChecked; ?> name="brand[]" class="sort_rang brand"><?php echo ucfirst($brand['brand']); ?></label></div>
					</li>
					<?php } ?>
					</ul>
				</div>
			</div>
			<div class="panel list">
				<div class="panel-heading"><h3 class="panel-title" data-toggle="collapse" data-target="#panelOne" aria-expanded="true">Sort By </h3></div>
				<div class="panel-body collapse in" id="panelOne">
					<div class="radio disabled">
						<label><input type="radio" name="sorting" value="newest" <?php if(isset($_POST['sorting']) && ($_POST['sorting'] == 'newest' || $_POST['sorting'] == '')) {echo "checked";} ?> class="sort_rang sorting">Newest</label>
					</div> 
					<div class="radio">
						<label><input type="radio" name="sorting" value="low" <?php if(isset($_POST['sorting']) && $_POST['sorting'] == 'low') {echo "checked";} ?> class="sort_rang sorting">Price: Low to High</label>
					</div>
					<div class="radio">
						<label><input type="radio" name="sorting" value="high" <?php if(isset($_POST['sorting']) && $_POST['sorting'] == 'high') {echo "checked";} ?> class="sort_rang sorting">Price: High to Low</label>
					</div>								                              
				</div>
			</div>
			<div class="panel list">
				<div class="panel-heading"><h3 class="panel-title" data-toggle="collapse" data-target="#panelTwo" aria-expanded="true"> Material</h3></div>
				<div class="panel-body collapse in" id="panelTwo">
					<ul class="list-group">
					<?php 
					foreach ($materials as $key => $material) {
						if(isset($_POST['material'])) {
							if(in_array($product->cleanString($material['material']),$_POST['material'])) {
								$materialCheck='checked="checked"';
							} else { 
								$materialCheck="";
							}
						}
					?>
						<li class="list-group-item">
							<div class="checkbox"><label><input type="checkbox" value="<?php echo $product->cleanString($material['material']); ?>" <?php echo @$materialCheck?>  name="material[]" class="sort_rang material"><?php echo ucfirst($material['material']); ?></label></div>
						</li>
					<?php } ?>
					</ul>
				</div>
			</div>
			<div class="panel list">
				<div class="panel-heading"><h3 class="panel-title" data-toggle="collapse" data-target="#panelFour" aria-expanded="true">Size</h3></div>
				<div class="panel-body collapse in" id="panelFour">
					<ul class="list-group">
						<?php foreach ($productSizes as $key => $productSize) {
							if(isset($_POST['size'])){
								if(in_array($productSize['size'],$_POST['size'])) {
									$sizeCheck='checked="checked"';
								} else {
									$sizeCheck="";
								}
							}
						?>
						<li class="list-group-item">
							<div class="checkbox"><label><input type="checkbox" value="<?php echo $productSize['size']; ?>" <?php echo @$sizeCheck; ?>  name="size[]" class="sort_rang size"><?php echo $productSize['size']; ?></label></div>
						</li>
						<?php } ?>
					</ul>
				</div>
			</div>
		</aside>
		<section class="col-lg-9 col-md-8">
			<div class="row">
				<div id="results"></div>
			</div>
		</section>
	</div>
	<input type="hidden" id="totalRecords" value="<?php echo $totalRecords; ?>">
</form>

Step3: Get Product Filter Options

In index.php file, we will call product filter options by calling methods from class Product.php.

include("Product.php");
$product = new Product();
$categories = $product->getCategories();
$brands = $product->getBrand();
$materials = $product->getMaterial();
$productSizes = $product->getProductSize();
$totalRecords = $product->getTotalProducts();

We will implement these methods in class Product.php to get filter options values.

public function getCategories() {		
	$sqlQuery = "
		SELECT category_id, category_name
		FROM ".$this->productTable." 
		GROUP BY category_name";
	return  $this->getData($sqlQuery);
}
public function getBrand () {
	$sql = '';
	if(isset($_POST['category']) && $_POST['category']!="") {
		$category = $_POST['category'];
		$sql.=" WHERE category_id IN ('".implode("','",$category)."')";
	}
	$sqlQuery = "
		SELECT distinct brand
		FROM ".$this->productTable." 
		$sql GROUP BY brand";
	return  $this->getData($sqlQuery);
}
public function getMaterial () {
	$sql = '';
	if(isset($_POST['brand']) && $_POST['brand']!="") {
		$brand = $_POST['brand'];
		$sql.=" WHERE brand IN ('".implode("','",$brand)."')";
	}
	$sqlQuery = "
		SELECT distinct material
		FROM ".$this->productTable." 
		$sql GROUP BY material";
	return  $this->getData($sqlQuery);
}
public function getProductSize () {
	$sql = '';
	if(isset($_POST['brand']) && $_POST['brand']!="") {
		$brand = $_POST['brand'];
		$sql.=" WHERE brand IN ('".implode("','",$brand)."')";
	}
	$sqlQuery = "
		SELECT distinct size
		FROM ".$this->productTable." 
		$sql GROUP BY size";
	return  $this->getData($sqlQuery);
}

Step4: Products Listing with Filter

In script.js file, we will implement functionality to load products from MySQL database using product filter by making ajax request to load_products.php file to load products details.

var totalRecord = 0;
var category = getCheckboxValues('category');
var brand = getCheckboxValues('brand');
var material = getCheckboxValues('material');
var size = getCheckboxValues('size');
var totalData = $("#totalRecords").val();
var sorting = getCheckboxValues('sorting');
$.ajax({
	type: 'POST',
	url : "load_products.php",
	dataType: "json",			
	data:{totalRecord:totalRecord, brand:brand, material:material, size:size, category:category, sorting:sorting},
	success: function (data) {
		$("#results").append(data.products);
		totalRecord++;
	}
});	
$(window).scroll(function() {
	scrollHeight = parseInt($(window).scrollTop() + $(window).height());		
	if(scrollHeight == $(document).height()){	
		if(totalRecord <= totalData){
			loading = true;
			$('.loader').show();                
			$.ajax({
				type: 'POST',
				url : "load_products.php",
				dataType: "json",			
				data:{totalRecord:totalRecord, brand:brand, material:material, size:size},
				success: function (data) {
					$("#results").append(data.products);
					$('.loader').hide();
					totalRecord++;
				}
			});
		}            
	}
});

Step5: Get Products from MySQL Database

In load_products.php file, we will include class Product.php and call method getProducts() to get products and return as JSON data to load product listing.

<?php
include("Product.php");
$product = new Product();
$products = $product->getProducts();
$productData = array(
	"products" => $products	
);
echo json_encode($productData);
?>

In class Product.php, we will implement getProducts() method to get products from MySQL database table products and return product listing HTML.

public function getProducts() {
	$productPerPage = 9;	
	$totalRecord  = strtolower(trim(str_replace("/","",$_POST['totalRecord'])));
	$start = ceil($totalRecord * $productPerPage);		
	$sql= "SELECT * FROM ".$this->productTable." WHERE qty != 0";	
	if(isset($_POST['category']) && $_POST['category']!=""){			
		$sql.=" AND category_id IN ('".implode("','",$_POST['category'])."')";
	}
	if(isset($_POST['brand']) && $_POST['brand']!=""){			
		$sql.=" AND brand IN ('".implode("','",$_POST['brand'])."')";
	}
	if(isset($_POST['material']) && $_POST['material']!="") {			
		$sql.=" AND material IN ('".implode("','",$_POST['material'])."')";
	}		
	if(isset($_POST['size']) && $_POST['size']!="") {			
		$sql.=" AND size IN (".implode(',',$_POST['size']).")";
	}	
	if(isset($_POST['sorting']) && $_POST['sorting']!="") {
		$sorting = implode("','",$_POST['sorting']);			
		if($sorting == 'newest' || $sorting == '') {
			$sql.=" ORDER BY id DESC";
		} else if($sorting == 'low') {
			$sql.=" ORDER BY price ASC";
		} else if($sorting == 'high') {
			$sql.=" ORDER BY price DESC";
		}
	} else {
		$sql.=" ORDER BY id DESC";
	}		
	$sql.=" LIMIT $start, $productPerPage";		
	$products = $this->getData($sql);
	$rowcount = $this->getNumRows($sql);
	$productHTML = '';
	if(isset($products) && count($products)) {			
		foreach ($products as $key => $product) {				
			$productHTML .= '<article class="col-md-4 col-sm-6">';
			$productHTML .= '<div class="thumbnail product">';
			$productHTML .= '<figure>';
			$productHTML .= '<a href="#"><img src="images/'.$product['image'].'" alt="'.$product['product_name'].'" /></a>';
			$productHTML .= '</figure>';
			$productHTML .= '<div class="caption">';
			$productHTML .= '<a href="" class="product-name">'.$product['product_name'].'</a>';
			$productHTML .= '<div class="price">$'.$product['price'].'</div>';
			$productHTML .= '<h6>Brand : '.$product['brand'].'</h6>';
			$productHTML .= '<h6>Material : '.$product['material'].'</h6>';
			$productHTML .= '<h6>Size : '.$product['size'].'</h6>';
			$productHTML .= '</div>';
			$productHTML .= '</div>';
			$productHTML .= '</article>';				
		}
	}
	return 	$productHTML;	
}

You may also like:

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

6 thoughts on “Product Search Filtering using Ajax, PHP & MySQL”

Paolo Cerruti says:

May 9, 2021 at 10:27 pm

Dear Webdamn,
thank you for this script.
It’s possible add a slider range price selection?, I have try many times without success.
I dont’ understand how pass value to ajax with other element different from checkboxes.
Thank you very much for you help.
1. webdamn says:
  
  August 15, 2021 at 11:45 am
  
  I will try to update this with this feature. thanks!
snm says:

April 9, 2021 at 10:14 am

good work sir
1. webdamn says:
  
  July 11, 2021 at 4:09 am
  
  You’re welcome, thanks!
Martin says:

September 15, 2020 at 1:53 pm

How add filter parameters to url? When clicking on detail product and go back history.. thx
1. webdamn says:
  
  November 21, 2020 at 12:48 pm
  
  We are checking this and update you, thanks!

Comments are closed.