Product Ajax Search Filter with Infinite Scroll more Using PHP and Mysqli - Mostlikers

28 March, 2020

Product Ajax Search Filter with Infinite Scroll more Using PHP and Mysqli

Recently I have posted Product Brand, Size, Material Checkbox Search Filtering Using PHP And Jquery. This search filter script doesn't have any pagination. Now i have created search filter with pagination. In this tutorial, we will discuss Product Ajax Search Filter with Infinite Scroll more Using PHP and MySQLi. Load more it's user-friendly without refresh the page user can get the filter products.

Product Ajax Search Filter with Infinite Scroll more Using PHP and Mysqli

Live Demo        Download


This tutorial related to E-commerce platform. If you want to learn of a similar kind post follow the below tutorials. You may get the basic idea about ajax search filters.
Note: All the tutorials have youtube video guidelines. Follow Our channel. The link is mentioned below.

Database and configuration

To get product information a table needs to be created in the database. The following SQL code creates a product with some required fields. insert some product information for checking the script.
CREATE TABLE IF NOT EXISTS `products` (
`id` int(11) NOT NULL,
  `sku_id` varchar(50) NOT NULL,
  `category_id` int(11) 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 AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

Product table



Create a new file config.php load the database connection code.
<?php  
   $user = 'root'; 
   $password = ''; 
   $database = 'mostlikers'; 
   $db = new mysqli('localhost',$user,$password,$database); 
 ?>

HTML & PHP

The following codes contain the index.php file. For a UI implementation, we have used a bootstrap template.


Bootstrap CDN 

<link rel="stylesheet" href="bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>


PHP Script

For a left sidebar filter data display on the checkbox. Fetch all the brand, material type, size data without duplicate and repeated data. So, I would suggest  Group_by() it will combine based on the brand name, size.
<?php 
include("config.php");
$all_brand=$db->query("SELECT distinct brand FROM `products` WHERE category_id = '1' GROUP BY brand");
$all_material=$db->query("SELECT distinct material FROM `products` WHERE category_id = '1' GROUP BY material");
$all_size=$db->query("SELECT distinct size FROM `products` WHERE category_id = '1' GROUP BY size");
// Filter query
    $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)."')";
    endif;

    if(isset($_GET['material']) && $_GET['material']!="") :
        $material = $_GET['material'];
        $sql.=" AND material IN ('".implode("','",$material)."')";
    endif;

    if(isset($_GET['size']) && $_GET['size']!="") :
        $size = $_GET['size'];
        $sql.=" AND size IN (".implode(',',$size).")";
    endif;
    $all_product=$db->query($sql);
    $content_per_page = 3;
    $rowcount=mysqli_num_rows($all_product);
    $total_data = ceil($rowcount / $content_per_page);
    function data_clean($str)
    {
        return str_replace(' ','_',$str);
    }

?>

HTML

<div class="container-fluid">
<form method="get" id="search_form">                
    <div class="row">
        <!-- sidebar -->
        <aside class="col-lg-3 col-md-4">
            <div class="panel list">
                <div class="panel-heading"><h3 class="panel-title">Shop by Brand</h3></div>
                <div class="panel-body collapse in" id="panelOne">
                    <ul class="list-group">
                    <?php foreach ($all_brand as $key => $new_brand) :
                        if(isset($_GET['brand'])) :
                            if(in_array(data_clean($new_brand['brand']),$_GET['brand'])) : 
                                $check='checked="checked"';
                            else : $check="";
                            endif;
                        endif;
                    ?>
                        <li class="list-group-item">
                            <div class="checkbox"><label>
                            <input type="checkbox" value="<?=data_clean($new_brand['brand']);?>" <?=@$check?> name="brand[]" class="sort_rang brand">
                            <?=ucfirst($new_brand['brand']); ?></label></div>
                        </li>
                    <?php endforeach; ?>
                    </ul>
                </div>
            </div>
        </aside> <!-- /.sidebar -->
        <section class="col-lg-9 col-md-8">
            <div class="row">
                <div id="results"></div>
            </div>
        </section>
    </div>
</form>
</div>

Javascript

This script loads the data based on the scroll more data it will send ajax request to the autoload.php file.
$(window).scroll(function()) - Bind an event handler to the "scroll" JavaScript event, or trigger that event on an element.
$(document).height() - Find the page height when page scroll.

<script type="text/javascript">
$(document).ready(function() {
    var total_record = 0;
    var brand=check_box_values('brand');
    var material=check_box_values('material');
    var size=check_box_values('size');
    var total_groups = <?php echo $total_data; ?>;
    $('#results').load("autoload.php?group_no="+total_record+"&brand="+brand+"&material="+material+"&size="+size,  function() {
        total_record++;
    });
    $(window).scroll(function() {       
        if($(window).scrollTop() + $(window).height() == $(document).height())  
          
        {    
            if(total_record <= total_groups)
            {
                loading = true;
                $('.loader').show();
                $.get("autoload.php?group_no="+total_record+"&brand="+brand+"&material="+material+"&size="+size,
                function(data){ 
                if (data != "") {                               
                    $("#results").append(data);
                    $('.loader').hide();                  
                    total_record++;
                }
                });     
            }
                // total_record ++;
        }
    });
    function check_box_values(check_box_class){
        var values = new Array();
            $("."+check_box_class+":checked").each(function() {
               values.push($(this).val());
            });
        return values;
    }
    $('.sort_rang').change(function(){
        $("#search_form").submit();
        return false;
    });
});
</script>

autoload.php

The following code contains autoload.php. On the scroll, the data it will post through ajax, based on search request array data will filter the SQL query.
url_clean() - It will clean the URL special character code.
<?php
include("config.php");
$content_per_page = 3;  
$group_no  = strtolower(trim(str_replace("/","",$_REQUEST['group_no'])));
$start = ceil($group_no * $content_per_page);
$sql= "SELECT distinct * FROM `products` WHERE category_id = '1'";
    if(isset($_REQUEST['brand']) && $_REQUEST['brand']!="") :
        $brand = explode(',',url_clean($_REQUEST['brand']));    
        $sql.=" AND brand IN ('".implode("','",$brand)."')";
    endif;

    if(isset($_GET['material']) && $_GET['material']!="") :
        $material = explode(',',url_clean($_REQUEST['material']));  
        $sql.=" AND material IN ('".implode("','",$material)."')";
    endif;

    if(isset($_GET['size']) && $_GET['size']!="") :
        $size = explode(',',$_REQUEST['size']); 
        $sql.=" AND size IN (".implode(',',$size).")";
    endif;
    $sql.=" LIMIT $start, $content_per_page";
    $all_product=$db->query($sql);
    $rowcount=mysqli_num_rows($all_product);
    // echo $sql; exit;

    function url_clean($String)
    {
        return str_replace('_',' ',$String); 
    }
?>

<!-- listing -->
<?php if(isset($all_product) && count($all_product) && $rowcount > 0) : $i = 0; ?>
    <?php foreach ($all_product as $key => $products) : ?>
        <article class="col-md-4 col-sm-6">
            <div class="thumbnail product">
                <figure>
                    <a href="#"><img src="product_images/<?php echo $products['image']; ?>" /></a>
                </figure>
                <div class="caption">
                    <a href="" class="product-name"><?php echo $products['product_name']; ?></a>
                    <div class="price">Rs.<?php echo $products['price']; ?>/-</div>
                    <h6>Brand : <?php echo $products['brand']; ?></h6>
                    <h6>Material : <?php echo $products['material']; ?></h6>
                    <h6>Size : <?php echo $products['size']; ?></h6>
                </div>
            </div>
        </article>
    <?php $i++; endforeach; ?> 
<?php endif; ?>
                        
<!-- /.listing -->


I hope this tutorial helpful for you. We request to Share your feedback or Suggestion to improve our coding standard.


14 comments:

  1. Page is reloading. Please help me..

    ReplyDelete
  2. hey , cards displaying on the right how can i fetch all the values displaying on individual cards to next page like a session cart

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. Hallo bro, i can replace Array parameter from GET to pagination, ho to set condition it ? i not used javascript or ajax (only pure PHP). thank before it

    ReplyDelete
  5. I need page numbers instead of javascript
    its my request please

    ReplyDelete
  6. Hello, Is there a version with pagination?

    ReplyDelete
  7. I have added a form code in index.php to sort the product by price:

    and also added the relevant code in autoload.php

    but it is not working. when i click on sort by price and select any ACS and DESC it get the url variable like this. http://example.com/?sort_price=asc

    then i realize its works with autoload.php?sort_price=asc but not with http://example.com/?sort_price=asc

    Can you please tell me how can we fix it.

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

      Delete
    2. Hi Logan Check now incase any issue send me email mostlikers@gmail.com

      Delete