Possible to list same item in multiple categories with 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.

Answer 2

To add to davidethell's answer, search and read about 'database normalization'. Good and simple relationships are key to relational databases. Wikipedia has a good article but there are many like it: https://en.m.wikipedia.org/wiki/Database_normalization

Answer 3

OK so for anybody else trying to do this, here is my code:

"SELECT * FROM ProductTable INNER JOIN (LinkTable INNER JOIN CategoryTable ON LinkTable.intCategory = CategoryTable.intCategoryID) ON ProductTable.intProductID = LinkTable.intProduct WHERE LinkTable.intCategory = %s"

I have three tables: The first is the product table, each product has a unique number which is the primary key. The second is the category table, each category has a unique number which is the primary key. I then created a third table with two columns; intProduct and intCategory as per David's answer above. It's this third table that stores the product/category listing, each product has a separate row for each category it is listed in.