Product Search Filtering Using PHP and Ajax - Mostlikers

25 August, 2015

Product Search Filtering Using PHP and Ajax

In this tutorial, we will see how to create search filter in PHP. Here I have created an E-commerce website product search filters like Flipkart and Amazon search filter functionality using PHP,MySQLi and ajax. The output result will get without refresh the page. Follow this simple code create eCommerce site search filter.

Product search filtering using php and ajax





In this tutorial design, i have used bootstrap. Add bootstrap below the file link on your website.

PHP

-- index.php

-- ajax_search.php 

CSS

-- bootstrap.min.css 

-- bootstrap-theme.min.css

js

-- jquery-1.11.3.min.js 



Database


Create sample table name called "products" and insert some information, size value like (32,36,38).
CREATE TABLE IF NOT EXISTS `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` varchar(250) NOT NULL,
  `size` int(11) NOT NULL,
  `price` decimal(10,0) NOT NULL,
  PRIMARY KEY (`id`)
);

Bootstrap css

Add below boostrap css link and jquery link <head></head> tag.
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap-theme.min.css">
<script src="//code.jquery.com/jquery-1.11.3.min.js"></script>

index.php

<?php
    $db=new mysqli('localhost','root','','mostlikers'); 
    $all_row=$db->query("SELECT * FROM products");
?>
<html>
<body>
<div class="container">
<div class="row">      
<div class="col-sm-3 col-md-3">
    <form id="search_form">
    <div class="well">
    <h4 class="text-info">Search by Size</h4>
    <input value="32" class="sort_rang" name="size[]"  type="checkbox"> 32
    <input value="36" class="sort_rang" name="size[]"  type="checkbox"> 36
    <input value="38" class="sort_rang" name="size[]"  type="checkbox"> 38
    </div>
    </form>
</div>
</div>   
<div class="row">
<div class="ajax_result">
<?php if(isset($all_row) && is_object($all_row) && count($all_row)): $i=1;?>
<?php foreach ($all_row as $key => $product) { ?>       
<div class="col-sm-3 col-md-3">
<div class="well">
<h2 class="text-info"><?php echo $product['name']; ?></h2>
<p><span class="label label-info">Size : 
<?php echo $product['size']; ?></span></p>                      
<p><?php echo $product['description']; ?></p>
<hr>
<h3>$<?php echo $product['price']; ?> / month</h3>
<hr>
<p><a class="btn btn-default btn-lg" href="#">
<i class="icon-ok"></i> Add cart</a></p>
</div>
</div>        
<?php } ?>
<?php endif; ?>
</div>
</div>      
</div>
</body>
</html>

ajax

Here I have created. sort_rang class, on change this class  data will post on ajax_search.php file.
<script type="text/javascript">
$(document).on('change','.sort_rang',function(){
   var url = "ajax_search.php";
   $.ajax({
     type: "POST",
     url: url,
     data: $("#search_form").serialize(),
     success: function(data)
     {                  
        $('.ajax_result').html(data);
     }               
   });
  return false;
});
</script>

ajax_search.php

SQL Query, i have used WHERE IN. The IN operator allows you to specify multiple values in a WHERE clause. after that ajax response to the script data
<?php
    $db=new mysqli('localhost','root','','mostlikers');
    $sql="SELECT * FROM products";
    extract($_POST);
    if(isset($size)) 
        $sql.=" WHERE size IN (".implode(',', $size).")";
    $all_row=$db->query($sql);
?>
<?php if(isset($all_row) && is_object($all_row) && count($all_row)): $i=1;?>
    <?php foreach ($all_row as $key => $product) { ?>       
    <div class="col-sm-3 col-md-3">
        <div class="well">
            <h2 class="text-info"><?php echo $product['name']; ?></h2>
            <p><span class="label label-info">Size :
             <?php echo $product['size']; ?></span></p>                      
            <p><?php echo $product['description']; ?></p>
            <hr>
            <h3>$<?php echo $product['price']; ?> / month</h3>
            <hr>
          <p><a class="btn btn-default btn-lg" href="#">
          <i class="icon-ok"></i> Add cart</a></p>
        </div>
    </div>
   <?php } ?>
<?php endif; ?>

Search filter product table


Product search filtering using php and ajax

Thank you for visiting. if have any queries write your comments below.
God, our Creator, has stored within our minds and personalities, great potential strength and ability. Prayer helps us tap and develop these powers.  - A. P. J. Abdul Kalam










Related Topics



93 comments:

  1. How to store url On refresh search result gone

    ReplyDelete
  2. what do for more isset value. example color, brand, price

    ReplyDelete
  3. Use same form to post brand value.
    if(isset($brand) && isset($size))
    $sql.=" AND brand IN (".implode(',', $brand).")";

    ReplyDelete
  4. how to paginate in filter???

    ReplyDelete
    Replies
    1. http://www.mostlikers.com/2015/11/auto-load-more-data-dynamically-on-page.html

      Delete
  5. it is nice good but can i know what is the value $size contain

    ReplyDelete
    Replies
    1. I have updated table pic for your reference.

      Delete
  6. it seems to work well but it unable to fetch data in string format it works well in integer value can you have a solution to fetch data in string form

    ReplyDelete
  7. i have created search system by brand price offer price everything goes well only their is one problem when i go for brand it accept only one value like when i click nokia then it will show brands of nokia but when i click on another checkbox then it show blank result

    ReplyDelete
  8. this is my code


    if(isset($size))
    $sql.=" and op in (".implode(',', $size).")";
    if(isset($brand))
    $sql.="and price in (".implode(',', $brand).")";
    $tinu=implode(',', $name);

    if(isset($name))
    $sql.="and brand like '%$tinu%'";


    $qriy="SELECT * from `stock` WHERE cat='' and 1 $sql ";
    $all_row=$db->query($qriy);
    ?>

    ReplyDelete
  9. Hi, i am working with Konarch.. we need to know multiple value string.. suppose.. we want to choose multiple mobile brand in string format.. but the system only one value, when i am doing it only taking one value.. suppose i select Nokia, it only show Nokia Result,, if i want Nokia & Samsung Result... it does not fetch.. Please help..
    Thank You

    ReplyDelete
  10. brother i love you save my day google and yahoo are waiting for you

    ReplyDelete
  11. hi karthick its me konarch i want to know i am using session for add to cart system for before login but their is a problem i am unable to udate the product info like quantity using session i am using push array function can you help me how update session variables

    ReplyDelete
  12. hi this code is working fine with single category how can we check brand , price , product name

    ReplyDelete
    Replies
    1. Follow sql statement
      if(isset($brand) && isset($size))
      $sql.=" AND brand IN (".implode(',', $brand).")";

      Delete
    2. $sql="SELECT * FROM products";
      extract($_POST);

      if(isset($size) && isset($price))
      $sql.=" WHERE size IN (".implode(',', $size).") OR price IN (".implode(',', $price).")";
      else if(isset($size))
      $sql.=" WHERE size IN (".implode(',', $size).")";

      else if(isset($price))

      $sql.=" WHERE (price IN (".implode(',', $price)."))";

      $all_row=$db->query($sql);

      Delete
  13. The demo is awesome ...... plz add more filter options like price brand and update a link plz...............

    ReplyDelete
  14. I've added more Checkbox filters and its working super smooth I don't know how to implement dropdown to refine results ,and whenever page is refreshed data will reset, how to overcome this problem plz reply me I am curiously waiting for your reply once again thank you very much for such nice tutorial.

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. I hope you want like amazon,filpkart code correct. I have done that concept i will update very soon.

      Delete
  15. How to add n0 results found message if no result found in ur coding ?

    ReplyDelete
  16. sir
    i am use this code.
    if(isset($brand) && isset($size))
    $sql.=" AND brand IN (".implode(',', $brand).")";
    this code is correct. but working on at give both isset value but i give only single isset value then given me not data. so pls give me correct data.
    thanks....
    sanjeev sharma

    ReplyDelete
    Replies
    1. if(isset($brand))
      $sql.=" AND brand IN (".implode(',', $brand).")";
      if(isset($size))
      $sql.=" AND size IN (".implode(',', $size).")";

      Try this code it will work.



      Delete
  17. Hey! If I have two values in checkbox like a range between 36-38. How I can do this?

    ReplyDelete
  18. WHERE column_name BETWEEN 36 AND 38

    ReplyDelete
  19. Thanx for reply
    but if I have
    input type="checkbox and values are 10"-10.9"

    how I will pass 10 and 10.9 in value for one checkbox value

    ReplyDelete
  20. I try like this
    input type="checkbox" values="36 38"
    input type="checkbox" values="38 40"
    input type="checkbox" values="30 42"
    and in php
    (explode(" ",$size));
    how can I use this in sql query

    ReplyDelete
  21. hey, i am not getting output result in index.php if i use "is_object($all_row)". If i remove this i will get output result however i am getting repeated data (due to foreach loop in index.php), how to stop repeated data? i am using mysql query (not mysqli).

    ReplyDelete
  22. I have a text field in the same form..then how can i get the value of text box for filteration

    ReplyDelete
  23. hi... this demo only working for a locallhost. not working online. i changed hostname,username,password and database name. but still not working

    ReplyDelete
    Replies
    1. I am sure check you're database connection file

      Delete
  24. Give me demo2 download link

    ReplyDelete
    Replies
    1. Tutorial - http://www.mostlikers.com/2016/10/brand-size-checkbox-search-filter-using-php-jquery.html

      Downloa link - https://app.box.com/s/tyg76wu0q3td1kffqg5ua7ueg19l4bkd

      Delete
  25. I can't fetch any value.


    ReplyDelete
  26. sir i had downloaded your demo 2 files but there is no ajax result page exist .

    ReplyDelete
    Replies
    1. Hi Khaslm saheb,

      Follow the demo2 tutorial link.

      http://www.mostlikers.com/2016/10/brand-size-checkbox-search-filter-using-php-jquery.html


      Delete
  27. Hi Brother Please recheck your product filtration demo2..
    price filtration not working properly...

    ReplyDelete
  28. Thank you very for the project sir, its really useful for me. My one humble request for you sir,, Will you add pagination?when we show all product and when we filter product using checkbox at that if we have some many records so page numbers is require. I tried my best but i am not able to do that. will you please add pagination. Thank you.

    ReplyDelete
    Replies
    1. Okay sure, soon i will update product search filter with auto scroll more pagination.

      Delete
  29. Hey Karthick
    Thank You so much for your blog. :)

    ReplyDelete
  30. hi k,


    any update on dropdown filter

    ReplyDelete
  31. how to provide different link in different product because in demo code product name has only one anchor tag

    ReplyDelete
  32. how to provide different link each product please help me

    ReplyDelete
    Replies
    1. Create seo friendly url for a particular product.

      Delete
  33. how can we add pagination to this

    ReplyDelete
  34. Hello Sir.. how ru?

    Excellent script of product filtering.. Thanks a lot.

    I have clicked on the google ads and you should get some dollars or cent.. thanks a lot.

    ReplyDelete
  35. Karthick hi

    Could not get it working here.
    I only see:
    Shop by Brand
    Shop by Material
    Shop by Size

    Rest of the page is grey

    Bert


    ReplyDelete
  36. Can you tell me what the problem is??
    See above

    ReplyDelete
    Replies
    1. Follow search filter new update tutorial

      http://www.mostlikers.com/2017/03/product-ajax-search-filter-with.html

      Delete
  37. I downloaded the script.
    Installed the database.
    Inserted the products.

    Same result.

    ReplyDelete
  38. Replies
    1. Step 1 - Check your database connection
      Step 2 - import table structure in database connection
      Step 3 - Change the content per page data

      Check this steps then also you are not getting data. echo $sql; or share the error.

      Delete
  39. I want to no result found page, when no data found into the table

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. Before the endif; php line add else condition. else : echo 'No results'

      Delete
    3. Thank you for your reply but it does not seem to work. Where am i making mistake, please help:

      $sql= "SELECT distinct id FROM `products` WHERE category_id = '1'";
      if(isset($_GET['brand']) && $_GET['brand']!="") :
      $brand = $_GET['brand'];
      $sql.=" AND brand IN ('".implode("','",$brand)."')";
      else : echo 'No results'
      endif;

      Delete
    4. Not this file ajax_search.php file you need add this code.

      Delete
  40. Hey,I'm use PDO prepare statement how do I bound variable IN() function I try your code , But Problem is only one Brand display not multiple Brand display.

    Below My code : -

    $query=$db->prepare('SELECT * FROM products WHERE category_id =:cid AND brand IN(:brname)');
    $query->bindParam(':cid',$filter);
    $query->bindParam(':brname',implode("','",$type));
    $query->execute();

    above my code i implode the brand in bindParam() but only one brand display I click second checkbox The result is Record Not Found.

    ReplyDelete
  41. Hey, Man Fast Reply Me Solved My error, I Wait help me....
    Above My Code Send Before 3 Days Ago

    ReplyDelete
    Replies
    1. $query->bindParam(':brname',implode(",",$type));

      Delete
  42. $query->bindParam(':brname',implode(",",$type));

    I try above your code, but same problem Record Not Found ?

    ReplyDelete
  43. $query->bindParam(':brname',implode(",",$type));

    I try above your code,But same proble Record Not Found

    ReplyDelete
  44. $query->bindParam(':brname','write your brand id manually'); check first the value or follow below query
    prepare(
    'SELECT * FROM products WHERE category_id =:cid AND brand IN(' . $inQuery . ')'
    );

    // bindvalue is 1-indexed, so $k+1
    foreach ($ids as $k => $id)
    $stmt->bindValue(($k+1), $id);

    $stmt->execute();
    ?>

    ReplyDelete
  45. hallo Mr. mostliker, i hope you can be always healt.

    top tutorial. but i can ask. how to make PRODUCT SEARCH from HEADER form ? thank before if

    ReplyDelete
    Replies
    1. Hey Ricky Yusuf,

      For your reference http://www.mostlikers.com/2016/05/create-your-own-search-engine-using-php.html use this tutorial

      Delete
  46. I'm not understand what you explain me!

    not clearly understand your code

    I try another way ,look see this

    I implode IN() clause look at,

    $query=$db->prepare('SELECT * FROM products WHERE category_id =:cid AND brand IN('.implode("','",$type).');
    $query->bindParam(':cid',$filter);
    $query->execute();

    print_r($query);


    Above my code I not bound, brand variable $type ,
    I print query ,the Query is Properly execute no error

    but not record found display

    look query string..........


    PDOStatement Object ( [queryString] => SELECT * from product WHERE category_id=:cid AND brand IN(puma,addidas) )

    ReplyDelete
  47. hey , I not clearly understand your logic please explain me easily.

    ReplyDelete
    Replies
    1. For an PDO need to change query bind. email your code to mostlikers@gmail.com

      Delete
  48. Hay hamid, sorry for my bad english.

    If all e-commerce (eg. Tokopedia.com, alibaba.com) always option "seacrh product" in header. My qustion is : ho to make it ?

    ReplyDelete
    Replies
    1. Your English good only bro. I will tel you some steps

      1) Create product table insert some product information
      2) Front end Create UI auto complete search bar in header ex : https://jqueryui.com/autocomplete/
      3) Fetch all the product name by using mysql query and load the fetch data to auto complete json data.
      4) Search and filter the keyword http://www.mostlikers.com/2016/05/create-your-own-search-engine-using-php.html

      Delete
  49. Ok I send my code your email address , I update you.

    ReplyDelete
  50. hey , mostlikers I send my codes zip file on your email, Before 1 week ago you don't reply me...... solve my error

    ReplyDelete
  51. hey Karthick can you guide me how to get multiple checkboxes and show there data accordingly i am trying but when i select the another checkbox no results are shown

    ReplyDelete
  52. hey karthick can you please guide me how to take multiple checkbox values and display it actually i am trying it but i can't able to get results

    ReplyDelete
    Replies
    1. http://www.mostlikers.com/2016/10/brand-size-checkbox-search-filter-using-php-jquery.html

      Delete
  53. sir your code work like
    http://demos.mostlikers.com/php/sort-search-filter/index.php?sort_price=price-desc-rank&brand=fashion%27
    by i want fixt url cat/mobile/smartphone
    $cat=$_GET[cat];
    $subcat=$_GET[sub-cat];
    SELECT * FROM products WHERE category =$cat AND subcategory=$subcat
    http://demos.mostlikers.com/php/sort-search-filter/index.php?cat=mobile&subcat=smart-phone?sort_price=price-desc-rank&brand=fashion%27-phone

    cat/mobile/smartphone?sort_price=price-desc-rank&brand=fashion%27-phone

    ReplyDelete
  54. when is select checkbox value i am getting -Object not found

    ReplyDelete
  55. sir please tell me how to make default viwe set order by id desc in the demo2

    ReplyDelete