可以使用PHP / MySQL列出多个类别中的相同项目吗？

Question

I have a website which lists products in various categories and subcategories - fairly standard stuff. However, I now need to list the same product in multiple categories - how can I do this?

I'm using the code:

"SELECT * FROM ProductTable WHERE Category = 1"

I can get it to work by entering multiple numbers in the Category table i.e."1 2 3" and using the code:

"SELECT * FROM ProductTable WHERE Category LIKE 1"

But this also returns products from category 10 and 11 (I have 20 categories).

I may be approaching this from the wrong angle but is there a way to make the code work?

Answer 1

Since you no longer have a one-to-many relationship between categories and products you will need a new table to give the desired many-to-many relationship. Something like:

product_categories
------------------
category_id INT
product_id INT

Primary key: (category_id, product_id)

This will allow a product to belong to multiple categories in a normalized way instead of treating the Category field as a list of ids.