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?
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.
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
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.