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 MySQL. Load more it's user-friendly without refreshing the page user can get the filter products.

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.

• Ajax Checkbox Search Filter Using PHP and MySQL
• Auto Load More Data Dynamically On Page Scroll Using Jquery And PHP
• Product Brand, Size, Material Checkbox Search Filtering Using PHP And Jquery

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.