MySQL querying products from one table only if they appear in certain category table

Question

I've got the following tables:

Products:
  id, name, ...

Products_in_Categories
  id, category_id, product_id

Categories
  id, name, ...

I have an admin page where I want to let him search products by name, catalog id etc. And of course by category and name and catalog id ...

So without the category - it's really easy. But when I want the search to be able to extract only the information of the products that are related to the chosen category - I just can't figure it out.

Am I using some old methods or is this the way to store it? I know and have used serialize and unserialize but I didn't want to use it here.

Any help will be very appreciated, thank you!

Answer 1

SELECT p.* FROM products as p
LEFT JOIN Products_in_Cateogries pc ON pc.product_id = p.id,
LEFT JOIN Categories c ON c.id = pc.categroy_id
WHERE c.name = 'CATEGORY A'

you also don't need to have a id in the Products_in_Categories table since the combination of category_id and product_id is unique.

Answer 2

There is nothing wrong with the way you store your products, these three tables are the right way to store that sort of relation.

SELECT *
FROM Categories c
JOIN Products_in_Categories pic ON ( pic.category_id = c.category_id )
JOIN Products p ON ( p.id = pic.product_id )
WHERE c.name = 'YOUR_CATEGORY_NAME';

Replace YOUR_CATEGORY_NAME by the category name you search for. If you want the user to be able to enter just a part of the name, replace WHERE c.name = 'YOUR_CATEGORY_NAME'; by

WHERE c.name LIKE '%CATEGORY_NAME_PART%;