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.


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;
}


You can view the live demo from the Demo link and can download the full script from the Download link below.
Demo [sociallocker]Download[/sociallocker]