Filtering results from mysql with php and ajax

Question

I am making a filtering system and I have three tables:

Products: ProductId, CatId, SubCatId, ProductName, ProductDescription
Filters: FilterId, FilterName
ProductsData: ProductDataId, ProductId, FilterId, ProductDataEN(containing value for the filter which in my case is "on" meaning that the filter is selected for this particular product)

I have script that filters the information, but the problem is that the sript now gets data only from ProductsData and the returned result is JSON with all the information from this table(ProductsData) which contains the filters values for every product. Keep in mind that one product can have more than one filter therefore ProductsData doesn't match Products.
I am posting the js code for the filtering and the php code for the mysql queries.
My qiestion is how to combine all the tables and the end JSON to be list of products with filters(not just filters).

JavaScript Code:

function makeItem(data){
    var tbl_body = "";
    $.each(data, function() {
        var tbl_row = "";
        tbl_row += "<div class='subCatName'>"+this.FilterId+"</div>";
        tbl_row += "<div class='subCatText'>"+this.ProductId+"</div>";
        tbl_body += "<div class='categoryWrap'>"+tbl_row+"</div>";
    })
    return tbl_body;
}

function getChecks(){
    var checks = [];
    $checkboxes.each(function(){
        if (this.checked) {
            checks.push(this.name);
        };
    })
    return checks;
}

function update(checks){
    $.ajax({
        type: "POST",
        dataType : "json",
        url: 'submit.php',
        data: {checkOptions : checks},
        success: function(records){
            $('.subcategories').html(makeItem(records));
        }
    })
}

var $checkboxes = $("input:checkbox");
$checkboxes.on("change", function(){
    var checks = getChecks();
    update(checks);
});

update();

PHP code:

$select = "SELECT *";
$from = " FROM ProductsData";
$where = " WHERE TRUE ";
$checks = isset($_POST['checkOptions'])? $_POST['checkOptions'] : array('');

foreach ($filters as $key => $filter) {
  if (in_array($filter['nameBG'], $checks)) {
    $where .= "AND FilterId = $filter[id]";
  }
}
$sql = $select . $from . $where;
$statement = $db -> query($sql);

while( $row = $statement -> fetch_assoc()) {
    $json[] = $row;
}
$json1 = json_encode($json);
echo($json1);

If it was me, I'd forget about all the non-MySQL stuff here for the time being, and just focus on the SQL. Provide proper CREATE and INSERT statements AND a desired result — Strawberry, Jul 06 '16 at 05:56
Have you tried echo $sql; before executing it. AND is $filters array stores the value of the checkbox your passing? — Nithee, Jul 06 '16 at 06:03
it executes correctly but the end result is not what I need. The end result is everithyng from ProductsData but I need ProductId, ProductName and all the FilterIds assosiated with this ProductId — A.L., Jul 06 '16 at 07:19

score 0 · Answer 1 · edited May 23 '17 at 11:52

Loop through checkboxes and count each one checked or unchecked function update(checks){ $.ajax({ var info = 'id=' + checkOptions; type: "POST", dataType : "json", url: 'submit.php', data: info, success: function(records){ $('.subcategories').html(makeItem(records)); } })

            foreach ($filters as $key => $filter) {
              if (in_array($filter['nameBG'], $checks)) {
                $where .= "AND FilterId IN($_REQUEST['id'])";// this id comes from info variable and IN Operator since there will be multiple checkbox value must be [5 or 5,6 this will be checkbox value ]
              }
            }
            }

Hope this will helps.

Filtering results from mysql with php and ajax

1 Answers1