PHP SQL get values with complex checkbox filter

I have 3 tables

1. product
2. product_feature
3. feature

In the first one I have my products, the second one I have the id to the product and the id to the feature and the 3rd one I have the features that can be different colors, sizes, material…

I built a filter menu with Color blue, red, white, black… Size XL L M S… and when the user chooses it, the script send an AJAX request showing the products… the form input are checkbox type…

The problem is that I send an object that fils everytime the user chose something

Example

object = {
"color": [13215, 23154, 21658,...],
"size": [5123, 6574, 9875,...]
}

The arrays are filled with id_features When I print_r the data on file.php it shows me exacly what I want.

So my problem is at the SQL query, I have something like that

$features = implode("','", $ajaxArray['feature']); //ajaxArray is the object sent by JS
$query = "SELECT id_product FROM product_feature WHERE id_feature IN ('$features')";

So if I chose in my filter for example “red” and “XL” it shows me every product that has “red” feature or “XL” feature and what I want is ONLY THE PRODUCTS that have “red” and “XL” features… If a product has Red but not XL, I don’t want them to show!!!

Answer

What you are asking Mysql to do is

SELECT id_product FROM product_feature WHERE id_feature IN ('XL', 'red')

so, it is doing exactly what you are asking it to do, the “IN” clause is an implicit “OR”

You need a more complex query, there are several ways to do this, but here is a Q&D way

$sql = "SELECT id_product FROM product_feature 
  WHERE id_feature IN ('$features')
  group by id_product having count() = " . sizeof( $ajaxArray['feature'] );