php - 来自内部联接中多个列的多个 AND

Question

我有一个产品表、一个自定义定义表和一个值表:

产品

id   product_name         price
48   product one          32.99
87   another product      2.50
...etc

字段定义

id   name
6    brand
9    colour
23   flavour
...etc

字段值

product_id   fielddef_id   value
48           6             Rowntree
48           9             Red
48           23            Strawberry
87           6             Cadburys
87           9             Yellow
87           23            Lemon
etc...

我现在正在尝试使用带有每个字段定义下拉选项的过滤器进行搜索:

$sql = "SELECT * FROM products INNER JOIN fieldvals ON fieldvals.product_id=products.id WHERE product_name LIKE '%".$q."%' AND (fielddef_id='7' AND value='".$c."') AND (fielddef_id='8' AND value='".$b."') AND (fielddef_id='9' AND value='".$f."') AND (fielddef_id='10' AND value='".$t."') AND (fielddef_id='5' AND value='".$h."') AND (fielddef_id='6' AND value='".$v."')";

SQL 的回显给了我类似的东西:

SELECT * FROM products INNER JOIN fieldvals ON fieldvals.product_id=products.id WHERE product_name LIKE '%cola%' AND (fielddef_id='7' AND value='Purple') AND (fielddef_id='9' AND value='Apple') AND (fielddef_id='10' AND value='Party') AND (fielddef_id='5' AND value='true') AND (fielddef_id='6' AND value='true')

此代码仅适用于 $q 之后的一个条件 - 即您可以包含 $q 和 $c，或 $q 和 $h，或 $q 和 $v，它会成功找到匹配的产品，但多个嵌套的 AND语句似乎不起作用。

我哪里错了？

Answer 1

This link offers a similar question有人想从同一个“字段数据”表中查询并想要多个条件。

您遇到的问题是，对于任何单个记录，对于给定的列，它们都不能具有多个值，这只是比较多个记录并应用 HAVING COUNT(*) = 您正在寻找的条件的结果。另一个(根据我上面的链接)表明了这一点。现在如何将这种类似的方法应用于您的问题。

SELECT 
      P.ID,
      P.Product_Name,
      P.Price
   FROM 
      products P
         INNER JOIN fieldvals FV1
            ON P.id = FV1.product_id 
            AND FV1.FieldDef_ID = '7'
            AND FV1.Value = 'value expected for 7'

         INNER JOIN fieldvals FV2
            ON P.id = FV2.product_id 
            AND FV2.FieldDef_ID = '8'
            AND FV2.Value = 'value expected for 8'

         INNER JOIN fieldvals FV3
            ON P.id = FV3.product_id 
            AND FV3.FieldDef_ID = '9'
            AND FV3.Value = 'value expected for 9'

         (continue same "join" sample above for as many
          criteria conditions you need to add... just keep
          incrementing the alias ... FV1, FV2, FV3, etc...)
   WHERE 
      P.Product_Name like '%your criteria%'