表中相同字段的多个查询

Really hope someone can help me or at least point me in the right direction. I working on a product site, that allows visitors to filter their results using a menu on side of the product results, similar to what www.asos.com have done. Basically, they can chose product type, style,price,fit ... etc. Each of these fiter types, have their own table and a reference table which links each value to the products table. EG There is a

真的希望有人可以帮助我,或者至少指出我正确的方向。我在一个产品网站上工作,允许访问者使用产品结果侧面的菜单过滤他们的结果,类似于www.asos.com所做的。基本上,他们可以选择产品类型,样式,价格,适合...等。这些fiter类型中的每一个都有自己的表和一个引用表,它将每个值链接到products表。 EG有一个

Products table with productkey and all other product information Colours table, with the following fields, Colour_ID, Name Colourref table with productkey, Colour_ID

带有productkey和所有其他产品信息的产品表颜色表,包含以下字段:Colour_ID,带产品密钥的Colourref表,Colour_ID

I'm using MySQL and PHP. I know how to query the database and display the data if the visitor makes one selection from each filter type and then to display the counts for each attribute, but I'd really like to be able to allow them to make multiple selections and then calculate the counts for each attribute, based on what's been selected. I've looked into how this should be done, and I've seen that subqueries are an option, but I'm concerned about how many subqueries I would need to create as I have 9 filter groups, that can have a large number of values. There are currently, 1/2 million products in the database, and this grow over time. I'm capturing the values that need to be queried via the url, so as an example

我正在使用MySQL和PHP。我知道如果访问者从每个过滤器类型中选择一个然后显示每个属性的计数,如何查询数据库并显示数据,但我真的希望能够允许他们进行多项选择然后计算每个属性的计数,基于已选择的内容。我已经研究了应该如何做,并且我已经看到子查询是一个选项,但我担心我需要创建多少个子查询,因为我有9个过滤器组,可以有大量的值。目前,数据库中有50万种产品,并且这种产品会随着时间的推移而增长。我正在捕获需要通过url查询的值,以此作为示例

page=1&view=20&product=125,137,147&type=1,3,5&colour=3,9,5&material=187,345

As you can see from the example, I can have multiple values for each time. I tried writing a query using AND. Example, product = 125 AND product = 137, but that doesn't work.

从示例中可以看出,每次都可以有多个值。我尝试使用AND编写查询。例如,product = 125 AND product = 137,但这不起作用。

Does anyone have any advice on the best way to go about doing this even if it's just a point in the right direction?

有没有人对最好的方法有任何建议,即使这只是朝着正确的方向发展的一点?

Any help will be greatly appreciated

任何帮助将不胜感激

Thank you in Advance

先谢谢你

Vivien

1 个解决方案

#1

Basically you answered your own question already:

基本上你已经回答了你自己的问题:

SELECT ...
FROM ...
WHERE   (product = 125 OR product = 137) AND  
        (colour = 3 OR colour = 8 OR colour = 5) ...

You need to use OR instead of AND if you want to select several products, colours and so on. If you want both a product and colour then you need to combine those using AND in between. There is no need for subqueries here.

如果要选择多个产品,颜色等,则需要使用OR而不是AND。如果你想要产品和颜色,那么你需要在两者之间结合使用AND。这里不需要子查询。

It's easier to use IN though:

但是使用IN更容易:

SELECT ...
FROM ...
WHERE product IN (125, 137, 147) AND colour IN (3, 5, 8)

A more complete example of this SQL code:

此SQL代码的更完整示例:

SELECT p.* 
FROM Products p

LEFT JOIN Colourref cr
ON cr.productkey = p.productkey

LEFT JOIN Colours c
ON c.Colour_ID = cr.Colour_ID

WHERE 
    p.productkey IN (1, 2, 4)
    AND c.Colour_ID IN (1, 2)

This will select all products that have the ID 1, 2 or 4 which have the colours 1 or 2.

这将选择ID为1,2或4且颜色为1或2的所有产品。

It left joins the required tables on the IDs and then filters the desired values.

它左边连接ID上的必需表,然后过滤所需的值。

#1