07
05
Product Refine Search Using PHP MYSQL

Hi friends In this tutorial we will see how to search product using ajax php mysql. I created this product filter similar to amazon, flipkart, paytm. The refining category includes brands, size and color. The output will display according to the search category that you have selected. The sql query will result the matching product and display the result in the browser without page refresh. Here I used selectbox, checkbox and radio attributes for refining the product.
  • Refine By Brand(List of brands like adidas nike, puma .. and so on )
  • Refine By Size(S, M, L, XL and so on)
  • Refine By Color(Reg, Green, Yellow, Blue and so on)

In this tutorial I have used the following files.

  • 1. index.php
  • 2. dbfunctions.php
  • 3. filter_products.php
  • 4. jquery-1.10.1.min.js
  • 5. product-filter.js
  • 6. styles.css
    • Product Filter HackandPhp Programming Blog

      Product Filter HackandPhp Programming Blog

      Database:

      I have created the 6 tables named tbl_brands, tbl_colors, tbl_productphotos, tbl_colors, tbl_products, tbl_productsizes, tbl_sizes
      CREATE TABLE `tbl_brands` (
        `id` int(11) NOT NULL,
        `brand` varchar(100) NOT NULL
      ) ;
      CREATE TABLE `tbl_colors` (
        `id` int(11) NOT NULL,
        `color` varchar(100) NOT NULL
      ) 
      
      
      CREATE TABLE `tbl_productphotos` (
        `id` int(11) NOT NULL,
        `ProductID` int(11) NOT NULL,
        `photo` varchar(200) NOT NULL
      ) ;
      
      
      CREATE TABLE `tbl_products` (
        `ProductID` int(255) NOT NULL,
        `Title` varchar(255) NOT NULL,
        `Description` tinytext NOT NULL,
        `Brand` varchar(255) NOT NULL,
        `Color` varchar(100) NOT NULL,
        `Price` int(11) NOT NULL,
        `status` int(11) NOT NULL
      ) ;
      
      
      CREATE TABLE `tbl_productsizes` (
        `id` int(11) NOT NULL,
        `ProductID` int(11) NOT NULL,
        `sizeID` int(11) NOT NULL
      ) 
      
      
      CREATE TABLE `tbl_sizes` (
        `id` int(11) NOT NULL,
        `size` varchar(100) NOT NULL
      )
      

      Database Functions:

      conn = mysqli_connect(DBHOST,DBUSER,DBPWD,DB);
      		//$db_select = mysql_select_db(DB,$conn);		
      	}
      	
      	public function getResults($table) 
      	{
      	    $data = array();
      		$query = mysqli_query($this->conn,"SELECT * FROM $table") or die(mysqli_error());
      		$num_rows = mysqli_num_rows($query);
      		if($num_rows>0) {
              	while($row=mysqli_fetch_array($query,MYSQLI_ASSOC))
      				$data[]=$row;
      		}
      	    return $data;		
          }
      	
      	public function allProducts()
      	{ 
      		$query = mysqli_query($this->conn,"SELECT * FROM tbl_products");	
      		while($row=mysqli_fetch_array($query,MYSQLI_ASSOC))
      		$data[]=$row;
      		
      		return $data;
      	}
      	
      	public function getproductPhoto($id)
      	{
      		$query = mysqli_query($this->conn,"SELECT photo FROM tbl_productphotos where ProductID = $id limit 0,1");	
      		$photo = mysqli_fetch_array($query,MYSQLI_ASSOC);
      		
      				
      		return $photo['photo'];
      	}
      	
      	public function _getAllProductPhotos($id)
      	{
      		$photo = mysqli_query($this->conn,"SELECT photo FROM tbl_productphotos where ProductID = $id limit 0,5");	
      		while($row=mysqli_fetch_array($query,MYSQLI_ASSOC))
      		$data[]=$row;	
      		
      		return $data;
      	}
      	
      	public function getProductDetails($id)
      	{
      		$query = mysqli_query($this->conn,"SELECT * FROM tbl_products where ProductID = $id");	
      		while($row=mysqli_fetch_array($query,MYSQLI_ASSOC))
      		$data=$row;
      		
      		return $data;
      	
      	}
      	
      	public function getAvailableSize($id)
      	{
      		$query = mysqli_query($this->conn,"SELECT sizeID from tbl_productsizes where ProductID = $id");
      		while($row=mysqli_fetch_array($query,MYSQLI_ASSOC))
      		$data[]=$row;
      		
      		return $data;
      	}
      	
      }
      ?>
      

      Product Filter - jQuery Script

      $(document).ready(function() {
      	
      	doRefineFilter();
      	common_refine_filter();
      	function doRefineFilter(){
      		$(".filter").on("change",function(){
      			common_refine_filter(); 
      		
      		});	
      	}
      	function common_refine_filter(){
      		filteration(1); 
          }
      	function filteration(page){
          	var refine_filter_arr = []; //
      		var bflag = 0;
      		var sflag = 0;
      		var cflag = 0;
      		$('input.filter:checkbox:checked,input.filter:radio:checked,.filter option:selected').each(function () {
      			var type = $(this).attr("data-type");
      			var value = $(this).val();
      			
      			 	refine_filter_arr.push({'type':type,'value':value});
      				
      				if((type=="brands")&&(bflag==0)){
      				$('.spanbrandcls').css('visibility','visible');
      					bflag=1;
      				}
      				if((type=="sizes")&&(value!="")&&(sflag==0)){
      				$('.spansizecls').css('visibility','visible');
      					sflag=1;
      				}
      				if((type=="colors")&&(cflag==0)){
      				$('.spancolorcls').css('visibility','visible');
      					cflag=1;
      				}
      				if(bflag==0){
      				$('.spanbrandcls').css('visibility','hidden');	
      				}if(sflag==0){
      				$('.spansizecls').css('visibility','hidden');	
      				}if(cflag==0){
      				$('.spancolorcls').css('visibility','hidden');	
      				}
      		
      				
      		 			});
      		    
      			var filter_data = refine_filter_arr;
      			
      				$.ajax({
      			type: "POST",
      			url: "refine/filter_products.php",
      			data: {'filter_data':refine_filter_arr}, 
      			cache: false,
      			success: function(html){
      				$("#products").html(html); 
      			
      			}
      			});
      			
      		  	
      	}	
      
      	$(".spanbrandcls").click(function(){
      		$('.bcheck').removeAttr('checked');				
      		common_refine_filter();
      		$('.spanbrandcls').css('visibility','hidden');
      	});
      	$(".spansizecls").click(function(){
      		$(".scheck")[0].selectedIndex = 0;
      		common_refine_filter();
      		$('.spansizecls').css('visibility','hidden');
      	});
      	$(".spancolorcls").click(function(){
      		$('.ccheck').removeAttr('checked');
      		common_refine_filter();
      		$('.spancolorcls').css('visibility','hidden');
      	});
      	
      });	
      
      Thanks for watching the tutorial. In next tutorial we will see how to refine the product with advance search includes pagination, sorting, price slider etc. You can integrate this code in your eCommerce site and make an application outstanding. I hope every one loves this code. Subscribe hackandphp and get latest updates.

      By posted on - 7th May 2017