I am making a webshop in MySQL and PHP with a database design similar to this (see the picture)
I am not having a problem with multiple-price items with different specifications, like in the question linked above.
This will be a computer webshop so there is no need for me to make a specification for each product. I can categorize them, for ex. Smartphones, Laptops... and add specifications for each category.
Smartphones could have: Brand, Processor type, Screen Size, Color, Battery, Price, etc.
Laptops: Processor, Graphics card, Brand, Price, Size, ...
I also see no need for a [combinations] table, as in my opinion, it would be much to store needlessly.
My problem is with filtering the products. I can easily select all of them, or just a single one, or for example. select all of the white colored phones or all of the white or gold colored ones, but I cannot select all of the white phones that have an 8MP front camera.
Table structures:
[products]: id, name, category_id, description
[categories]: id, name
[specifications]: id, category_id, name
[spec_values]: id, product_id, spec_id, value
The following SQL returns all of the distinct products that are golden or have an 8mp front camera.
SELECT products.id, products.name, spec_values.value AS Filter
FROM products, categories, specifications, `spec_values`
WHERE products.category_id = categories.id
AND spec_values.product_id = products.id
AND ((specifications.name = 'Color' AND spec_values.value = 'Gold')
OR (specifications.name = 'Front Camera' AND spec_values.value = '8MP'))
GROUP BY products.id;
I want to have a logical AND instead an OR between them but simply changing it returns nothing, as it cannot be fulfilled within a single row because my specification names and values are stored on multiple rows.
One solution would be to SQL CREATE TEMPORARY TABLE for each filter, but I think that would require a lot of unnecessary server-side work.
The other one would be to get all of the non-distinct values and iterate through them with PHP and check if all of them exist; if yes then display them on the page, but still, that one could be messy, too.
Is there a better solution that I did not notice due to my inexperience in SQL? Thank you in advance for any help. Have a great day!
You can use aggregation for this. Include a HAVING clause to check, that the count matches the number of filters. Unless you have inserted the exact specification value twice (or more) you'll know that the products meeting that condition must have all the specification values filtered for. If one does not, one row (or more rows), i.e. one (or more) specificion value, is missing.
SELECT products.id,
products.name
FROM products
INNER JOIN spec_values
ON spec_values.product_id = products.id
INNER JOIN specifications
ON specifications.id = spec_values.spec_id
WHERE specifications.name = 'Color' AND spec_values.value = 'Gold'
OR specifications.name = 'Front Camera' AND spec_values.value = '8MP'
GROUP BY products.id,
products.name
HAVING count(*) = 2;
BTW, it is advisable to use explicit join syntax over the comma delimited list in the FROM clause for better readability.