Some items were not filter when trying to filter with ajax checkbox

Question

I am learning how to code and I am making my own portfolio right now. I created an eCommerce page with template (created by FreeHTML5.co) and I want to use ajax to filter products.

When I clicked the TYPE filter checkboxes, some items linked to the filter show up but some don't:

My database

Headphone & Speaker - CORRECT

Chair - Missing - LIGOMANCER

Cabinet - Missing - ALATO CABINET

Table - Missing - SCULPTURAL COFFEE TABLE

Box - Missing - HIMITSU MONEY BOX

This is my ajax code (index.php):

filter_data();  

    function filter_data()
    {
        $('.filter_result').html('<div id="loading" style="" ></div>'); 
        var action = 'product_filter'; 
        var minimum_price = $('#hidden_minimum_price').val();
        var maximum_price = $('#hidden_maximum_price').val();
        var brand = get_filter('brand'); 
        $.ajax({
            url:"product_filter.php", 
            method:"POST",
            dataType:'text',
            data:{action:action, minimum_price:minimum_price, maximum_price:maximum_price, brand:brand}, 
            success:function(data){
                $('.filter_result').html(data);
            }
        });
    }

    function get_filter(class_name)
    {
        var filter = [];
        $('.'+class_name+':checked').each(function(){ 
            filter.push($(this).val()); 
        });
        return filter;
    }

    
    $('.common_selector').click(function(){ 
        filter_data();
    });

    $('#price_range').slider({ 
    range:true,
    min:0,
    max:1000,
    values:[0, 1000], 
    step:50,
    stop:function(event, ui)
    {
        $('#price_show').html(ui.values[0] + ' - ' + ui.values[1]);
        $('#hidden_minimum_price').val(ui.values[0]);
        $('#hidden_maximum_price').val(ui.values[1]);
        filter_data();
    }
  });

This is my backend codes (product_filter.php):

<?php

require "./includes/dbh-inc.php";

if(isset($_POST["action"]))
{   

$query = "SELECT * FROM products WHERE productDisplay=1";

if(isset($_POST["minimum_price"], $_POST["maximum_price"]) && !empty($_POST["minimum_price"]) && !empty($_POST["maximum_price"]))
{
    $query .= "
     AND productPrice BETWEEN '".$_POST["minimum_price"]."' AND '".$_POST["maximum_price"]."'
    ";
}

if(isset($_POST["brand"]))
{
    $brand_filter = implode("','", $_POST["brand"]); // 'implode' - convert items in array to string
    $query .= "
    AND productType IN('".$brand_filter."') 
    ";
}

$stmt = mysqli_stmt_init($conn);
if(!mysqli_stmt_prepare($stmt , $query)){
    header("location: index.php");
    exit();
}
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
$row = mysqli_fetch_assoc($result); 
$output = '';
if($row)
{
    while($row = mysqli_fetch_assoc($result)): 
        $output .= ' 
        
        <div class="col-md-4 text-center">
            <div class="product">
                <div class="product-grid" style="background-image:url(images/'.$row['productImage'].'.jpg);">
                    <div class="inner">
                        <p>
                            <a href="product.php?id='.$row['productId'].'" class="icon"><i class="icon-shopping-cart"></i></a>
                            <a href="product.php?id='.$row['productId'].'" class="icon"><i class="icon-eye"></i></a>
                        </p>
                    </div>
                </div>
                <div class="desc">
                    <h3><a href="product.php?id='.$row['productId'].'">'.$row['productName'].'</a></h3>
                    <span class="price">$'.$row['productPrice'].'</span>
                </div>
            </div>
        </div>
        
        
        ';
    endwhile;
}
else
{
    $output = '<h3>No Data Found</h3>';
}
echo $output;
}

?>

Thank you in advance for reading my code and I wish you have a wonderful day!

Your use of 'filter' suggests you want to hide / show certain items in the present listing. Based on your code, you appear to want to perform a whole new query on each click or unclick of an item. When User arrives to this page, will all the data be presented up front or will the user have to make a selection to then get data presented? — Twisty, Apr 26 '21 at 04:36
As @Twisty already implied in his comment: you should think about the possibility of using the Ajax query _only once_ at the beginning of the session to get all the product data and then do the filtering in the browser. This will probably improve the user experience. — Carsten Massmann, Apr 26 '21 at 04:52
Looking at the code you posted from Google Drive, it looks like index.php loads all the data from the `products` table. Tis means that by the time you're ready to filter the data, it's already been transmitted from the server to the browser. IT might be best to show or hide items in the page instead of performing a new AJAX call to the server to get the data again. This owuld only be benificial if the table is update very often, new items added / removed every few seconds. — Twisty, Apr 26 '21 at 04:53
Looking over your code, I do not see anything that looks obviously out of place. Do you see any Errors in Console or what do you see under Network? It would be best to Edit your post and provide the HTML that is a result of your PHP; not the PHP itself. — Twisty, Apr 26 '21 at 05:37
Hi @Twisty, really appreciate your reply. I am a rookie in coding. Yes, when user first open the page, all the products will be presented. I will definitely note down your answer about hiding and showing data instead of using ajax, I never think about it. I am still learning how to use ajax, so in my case may I know what went wrong, I was trying to find the reason why my filter is not working and solution to fix it. BTW I am humbled to join this community, thanks everyone! — Aqus, Apr 26 '21 at 05:38
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Apr 26 '21 at 13:45
Hi @Dharman, really appreciate your reply. After I read the articles you pinned, I realized I have a huge flaws in my codes. I will use prepared statement for my codes. Many thanks! — Aqus, Apr 27 '21 at 03:26

Twisty · Accepted Answer · 2021-04-26T06:09:00.620

Consider the following updates.

$(function() {
  function get_filter(class_name) {
    var filter = [];
    if (class_name != undefined) {
      $('.' + class_name + ':checked').each(function(i, el) {
        filter.push($(el).val());
      });
    }
    return filter;
  }

  function filter_data() {
    $.ajax({
      url: "includes/product_filter.php",
      method: "POST",
      data: {
        action: 'product_filter',
        minimum_price: $("#hidden_minimum_price").val(),
        maximum_price: $("#hidden_maximum_price").val(),
        brand: get_filter("brand")
      },
      beforeSend: function() {
        $(".filter_result").html("<div id='loading'>Loading...</div>");
      },
      success: function(data) {
        $('.filter_result').html(data);
      }
    });
  }

  $('.common_selector').click(function() {
    filter_data();
  });

  $('#price_range').slider({
    range: true,
    min: 0,
    max: 1000,
    values: [0, 1000],
    step: 50,
    stop: function(event, ui) {
      $('#price_show').html(ui.values[0] + ' - ' + ui.values[1]);
      $('#hidden_minimum_price').val(ui.values[0]);
      $('#hidden_maximum_price').val(ui.values[1]);
      filter_data();
    }
  });

  fetch_data();
});

#loading {
  text-align: center;
  background: url('loader.gif') no-repeat center;
  height: 150px;
}

<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.6.0/dist/css/bootstrap.min.css" integrity="sha384-B0vP5xmATw1+K9KRQjQERJvTumQW0nPEzvF6L/Z6nronJ3oUOFUFpCjEUQouq2+l" crossorigin="anonymous">
<link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
<div id="fh5co-product">
  <div class="container">
    <div class="row animate-box">
      <div class="col-md-8 col-md-offset-2 text-center fh5co-heading">
        <span>Cool Stuff</span>
        <h2>Products.</h2>
        <p>Dignissimos asperiores vitae velit veniam totam fuga molestias accusamus alias autem provident. Odit ab aliquam dolor eius.</p>
      </div>
    </div>
    <div class="row">
      <div class="col-md-3">
        <div class="list-group">
          <h3>Price</h3>
          <input type="hidden" id="hidden_minimum_price" value="0" />
          <input type="hidden" id="hidden_maximum_price" value="1000" />
          <p id="price_show">0 - 1000</p>
          <div id="price_range"></div>
        </div>
        <div class="list-group">
          <h3>Type</h3>
          <div style="height: 200px; overflow-y: auto; overflow-x: hidden;">
            <div class="list-group-item checkbox">
              <label><input type="checkbox" class="common_selector brand" value="Type 1">Type 1</label>
              <label><input type="checkbox" class="common_selector brand" value="Type 2">Type 2</label>
              <label><input type="checkbox" class="common_selector brand" value="Type 3">Type 3</label>
            </div>
          </div>
        </div>
      </div>
      <div class="col-md-9">
        <div class="row filter_result">
          <div class="col-md-4 text-center">
            <div class="product">
              <div class="product-grid" style="background-image:url(images/product0001.jpg);">
                <div class="inner">
                  <p>
                    <a href="product.php?id=0001" class="icon"><i class="icon-shopping-cart"></i></a>
                    <a href="product.php?id=0001" class="icon"><i class="icon-eye"></i></a>
                  </p>
                </div>
              </div>
              <div class="desc">
                <h3><a href="product.php?id=0001">Product 1</a></h3>
                <span class="price">$10.00</span>
              </div>
            </div>
          </div>
        </div>
      </div>
    </div>
  </div>
</div>

<div id="fh5co-started">
  <div class="container">
    <div class="row animate-box">
      <div class="col-md-8 col-md-offset-2 text-center fh5co-heading">
        <h2>Newsletter</h2>
        <p>Just stay tune for our latest Product. Now you can subscribe</p>
      </div>
    </div>
    <div class="row animate-box">
      <div class="col-md-8 col-md-offset-2">
        <form class="form-inline">
          <div class="col-md-6 col-sm-6">
            <div class="form-group">
              <label for="email" class="sr-only">Email</label>
              <input type="email" class="form-control" id="email" placeholder="Email">
            </div>
          </div>
          <div class="col-md-6 col-sm-6">
            <button type="submit" class="btn btn-default btn-block">Subscribe</button>
          </div>
        </form>
      </div>
    </div>
  </div>
</div>

No way to test this in the current environment. This should send along the proper payload and should get the proper data back.

You can use the Browser Web Console to view the Network transactions. You will be able to see the Request and the Response. This will help you identify what PHP is sending back what it's getting sent to it.

Hi @Twisty, thanks for the reply. I tried to troubleshoot the codes again and I realize something weird, the first result of my query is not showing. For example, when I check the 'Chair' checkbox, the reason why HAUTEVILLE CONCRETE ROCKING CHAIR (productId = 1) is not showing is because it is at the top of the table when the query is applied, but the second (productId = 3) and third (productId = 7) are showing, the same apply to the other checkboxes. Do you know why my first row in the table is ignored by the query and how can I fix it? — Aqus, Apr 26 '21 at 06:43
@Aqus the reason for this is you run `$row = mysqli_fetch_assoc($result);` and then you run the `while()` with the same code, so you while starts with the 2nd row, not the first. Remove the first `$row = mysqli_fetch_assoc($result);` statement. — Twisty, Apr 26 '21 at 14:39
Hi @Twisty, you literally solved my issue with one comment, I can't thank you enough for your contribution! — Aqus, Apr 27 '21 at 03:20
@Aqus thats great to hear. If that's how you feel, you may want to Upvote my answer in addition to marking it as the answer. — Twisty, Apr 27 '21 at 14:26
Hi @Twisty, I tried to up vote your solution, but when I clicked it it said "Thanks for the feedback! You need at least 15 reputation to cast a vote, but your feedback has been recorded.". I'm sorry I can't upvote your solution now, is there any way I can up vote your solution? — Aqus, Apr 28 '21 at 04:07

Some items were not filter when trying to filter with ajax checkbox

1 Answers1