PHP and MySQL filter multiple tables not working on ajax request-ThrowExceptions

Exception or error:

Challenges:
I had a table which store products, it sizes, colors, and brands in a delimited format. I discovered while I tried to filter colors, brands etc, it was not returning products with delimited comma. I used IN(), LOCATE() and find_in_set() but none worked. So, I thought the best thing to do was to normalize my tables which I did: separating, colors, sizes and brands.

Now, to the issue, after my normalization, I tried to filter products based on the parameters chosen(colors, brands..) by joining the tables but it is not filtering.

Below is my code:

<?php
    if (isset($_POST["action"])) {
    $gquery = "
    SELECT 
    product.id, 
    product.title,
    product.price,
    product.discount,
    product.rating,
    product.images, 
    GROUP_CONCAT(product_colors.color) as colors, 
    GROUP_CONCAT(product_brands.brand) as brands, 
    GROUP_CONCAT(product_sizes.size) as sizes  
   FROM product
    left join product_colors on product_colors.product_id = product.id
    left join product_brands on product_brands.product_id = product.id
    left join product_sizes on product_sizes.product_id = product.id
    WHERE product.availability = '1'

    GROUP BY 
    product.id, 
    product.title,
    product.price,
    product.discount,
    product.rating,
    product.images
    ";
    if (isset($_POST["minimum_price"], $_POST["maximum_price"]) 
    && !empty($_POST["minimum_price"]) 
    && !empty($_POST["maximum_price"])) {
    $gquery .= "
    AND product.price BETWEEN '" . $_POST["minimum_price"] . "' 
    AND '" . $_POST["maximum_price"] . "'
    ";
    }
    if (isset($_POST["brand"])) {

    $brand_filter = implode("','", $_POST["brand"]);
    //var_dump($brand_filter);
    $gquery .= "
    AND product_brands.brand IN('" . $brand_filter . "')
    ";
    }
    if (isset($_POST["color"])) {
    $color_filter = implode("','", $_POST["color"]);
    $gquery .= "
    AND product_colors.color IN('" . $color_filter . "')
    ";
    }
    if (isset($_POST["size"])) {
    $size_filter = implode("','", $_POST["size"]);
    $gquery .= "
    AND product_sizes.size IN('" . $size_filter . "') 
    ";

    print_r($size_filter);
    }

    $statement = mysqli_query($mysqli,$gquery);
    $total_row = mysqli_num_rows($statement);
    $output    = '';

    if ($total_row > 0) {
    while($row = mysqli_fetch_array($statement)) {
    if($row['images'] != "" && 
    file_exists(UPLOAD_DIR.'/product/'.$row['images'])){
    $thumbnail = UPLOAD_URL.'product/'.$row['images'];
    } else {
    $thumbnail = FRONT_IMAGES.'no-image.png';
    }
    ?>

    <div class="col-lg-3 col-6 px-0 px-sm-2 mb-sm-4">
    <div class="card product-card card-static">

    <a class="card-img-top d-block overflow-hidden" 
    href="details?id=<?php echo $row['id']; ?>">
    <img src="<?php echo $thumbnail;?>" 
    alt="<?php echo stripslashes($row['title']); ?>" class="">
    </a>

    <div class="card-body py-2">
    <h3 class="product-title font-size-sm">
    <a  href="details?id=<?php echo $row['id']; ?>">
    <?php echo stripcslashes($row['title']); ?>
    </a>
    </h3>
    <div class="d-flex justify-content-between price-star">
    <div class="product-price">
    <span class="text-accent">
    <?php 
    $price =  $row['price'];
    $discount =  $row['discount'];
    if($discount > 0){
    $price = $price-(($price*$discount)/100);
    }

    echo " ".$left_currency.number_format($price).$right_currency;
    if($discount > 0){

    echo ' <del class="product-old-price">  '.
    $left_currency.number_format($row['price']).
    $right_currency.'</del>';
    }
    ?>

    </span>
    </div>
    <div class="star-rating">


    <?php 
    $class="fa-star star-filled";
    for($i=0;$i<5;$i++){
    if($row['rating'] <= $i){
      $class = "fa-star-o empty";
    }
    echo '<i class="fa '.$class.'"></i>';
    }
    ?>

    </div>
    </div>
    </div>


    </div>
    </div>


    <?php


    }
    } else {
    echo '<h3>No Data Found</h3>' . $mysqli->error;
    }

    }

    ?>

Is there something I am not doing right and how can I solve it?

How to solve:

Leave a Reply

Your email address will not be published. Required fields are marked *