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);