Facted Search using php and mysql

[anil14353]

I'm trying to implementing faceted search in a Jewellery store, but failed. Problem is when try to filter the attributes table. structure is as follows:
Products Table:

Id Product_Code Product_Name
1  ABCGOLD1GM   1 gm Gold
2  ABCGOLD2GM   2 gm Gold
3  ABCGOLD394   3.94 gm Gold

Attributes Table:

Id   Attr_Name   Alias
1    Metal       metal
2    Fineness    fineness
3    Weight      weight

Product_Attributes Table:

id    product_id    attr_id   value
1         1            2      9999
2         1            3      1 gm
3         2            2      9999
4         2            3      2 gm
5         3            3      3.94 gm
6         3            2      9167 

Now i want to filter if someone selects 1 gm with 999 and 3.94 gm. If you know faceted search then you can easily know what i mean. I want to make query to do the same with the table structure above.

I used the below query but it gives only for 2 gm with 9999 but when i'm selecting 1 gm also it gives no results

select * from product_attributes where (attr_id = 4 and text = 2 gms) or (attr_id = 2 and text = 9999) group by product_id having count(*) = 2

[diafol]

Hint - if you group on product_id - you will only ever get one row for each product_id.

[diafol]

OK, I can see why you'd have it like this. If you're not expecting any other attributes, you could place them in the main products table as fields in their own right:

Id | Product_Code | Product_Name | metal_id | fineness | weight

It may appear to be less flexible than your current setup as in order to add an attribute, you'd have to change the schema. Also you can't easily get attributes from the table to create a dropdown or list for a form.

However, filtering on this table would be extremely easy:

SELECT ... FROM Products AS p 
    INNER JOIN Product_Attributes AS pa ON p.id = pa.product_id 
    INNER JOIN Attributes AS a ON pa.attr_id = a.Id
    WHERE (pa.attr_id = ? AND pa.`value` = ?) AND (pa.attr_id = ? AND pa.`value` = ?) 
    ORDER BY ...
    LIMIT ...

Building up the WHERE clause and associated 'bind array' for execution of a prepared statement would be a trivial thing to do.

[anil14353]

First of all Thank you for your reply but in future we will add more attributes to the products..

[pritaeas]

Can you show the code that builds your query?

[diafol]

First of all Thank you for your reply but in future we will add more attributes to the products..

OK, no problem