MySQL 查询以搜索多个属性和 value_id [英] MySQL query to search multiple attributes and value_id

问题描述

我的 mysql 查询有问题.有几天我无法解决它.

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

网址参数 ?a=1&b=2&c=3

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

结构:

属性 ID值_id

查询:

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]."')

但问题是如果 a=12&b=10

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

如果有属性为 10 或属性为 12 的产品，它会显示.我需要展示 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.

怎么了?

感谢您的帮助.

推荐答案

听起来您需要使用 GROUP BY 和 HAVING 子句.

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 */

确保您在 product_id 和 attr_id 之间有一个唯一索引以使其正常工作(您应该已经有了这个，因为它可能对产品没有意义有多个记录具有相同的 attr_id).

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

如果您还没有转义，您还需要确保在 SQL 中使用您的值.我在这里用 ? 显示这些变量，如果您使用准备好的语句，这将是您编写此 SQL 的一种方式.

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.

这篇关于MySQL 查询以搜索多个属性和 value_id的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！