CODEWITHSUNDEEP
phpmysql
Psyche
Psyche

Reputation: 8783

SQL query to retrieve products matching one or more filters

I'm writing this message because I ran out of ideas regarding the implementation of the following thing: given a table with products and another one with product filters, I'd like to retrieve the products according to the selected filters.

Tables look like this:

products:
p_id,
p_name,
p_image

product_filters:
pf_id,
pf_product_id,
pf_filter_id,
pf_filter_value

There is a third table with filter name and id but it's not so important.

From the HTML form I get an array with filter id and filter value.

I tried to retrieve the id of the products that matched a selected filter, but it only works file for one selected filter. If there are more than one selected filter I won't get any result.

I'm using PHP and MySQL.

Thanks.

Upvotes: 1

Views: 193

Answers (2)

Brad
Brad

Reputation: 31

The way I usually do this is by adding a join to the filter table for each filter:

SELECT p_id, p_name, p_image FROM products p
INNER JOIN product_filters pf1 ON p.p_id = pf1.pf_product_id
INNER JOIN product_filters pf2 ON p.p_id = pf2.pf_product_id
// Add another join for each additional filter
WHERE pf1.pf_filter_id = @filterid1 AND pf1.pf_filter_value = @filtervalue1
AND pf2.pf_filter_id = @filterid2 AND pf2.pf_filter_value = @filtervalue2
// Add conditions for each additional filter

You just need to watch out for performance issues when doing this. Do some explains on a couple test queries to make sure your indexes are set up correctly, and it's probably a good idea to put a limit on the number of filters that can be used at one time.

Upvotes: 1

Christiaan Nieuwlaat
Christiaan Nieuwlaat

Reputation: 1359

I'm thinking about creating a query like this:

SELECT * FROM products WHERE p_id
IN 
  (
-- this will be repeated in PHP for every filter id and filter value 
    SELECT pf_prod_id FROM product_filters 
    WHERE pf_filter_id = @filterid AND pf_filter_value = @filtervalue
    UNION ALL -- omit this line if it's the last filter id 
-- end repeat
  )

that should do the trick ( although it isn't the prettiest way to solve it probably )

Upvotes: 1

Related Questions