MySQL查询搜索多个属性和value_id

Question

I have a problem with mysql query. for a cuople days I cannot solve it.

URL params ?a=1&b=2&c=3

Structure:

attr_id value_id

query:

SELECT DISTINCT `product_id`
FROM `products_attr_val`
WHERE
    (`attr_id` = '".$searchqueryArray2[0]."' AND `value_id` = '".$searchqueryArray2[1]."')
    OR (`attr_id` = '".$searchqueryArray2[0]."' AND `value_id` = '".$searchqueryArray2[1]."')

but the problem is that if a=12&b=10

and if there is a product with atribute 10 or atribute 12 it shows. I need to show products with a=12 and b=10.

Whats wrong?

Thanks for help.

Answer 1

Sounds like you need to use GROUP BY and HAVING clauses.

SELECT
    `product_id`,
    COUNT (`primary_key_id`) AS `attr_count` /* primary key field here */
FROM `products_attr_val`
WHERE
   (`attr_id` = ? AND `value_id` = ?)
   OR (`attr_id` = ? AND `value_id` = ?)
   /* additional as necessary
   OR (`attr_id` = ? AND `value_id` = ?)
   */
GROUP BY `product_id`
HAVING `attr_count` = ? /* value here should be equal to number of attributes you are checking for */

Make sure you have a unique index across product_id and attr_id for this to work properly (you should already have this since it would not likely make sense for a product to have multiple records with the same attr_id).

You also need to make sure you are escaping your values for use in your SQL if you are not already. I am showing these variables here with ? which, if you we using prepared statments, would be a way you might write this SQL.