Having a database structure that allows a product to be associated with more than one category is a very common scenario in any eCommerce website. However, after working on a couple of truely awful bespoke solutions from other developers recently, whose methods to store and retreive such data were so convoluted, have inspired me to write this article.
Story such data need to not be overly complicated. The following, simple, table structure is required (in a real ecommerce system you would definately have additional fields – these have been omitted here for thae sake of the example):
Product
product_id (PK)
name
Category
category_id (PK)
name
Products_Category
product_id (PK)
category_id (PK)
The products_category table is a simple linking table that allows a many-to-many relationship between the product and category table. It contains to two primary keys to ensure every combination of product and category is unique. for example, this table will contain many unique number pairs and a row may be 1,4 or product_id 1 and category_id 4. The files to create and populate this table structure with sample data can be found here.
Now it is simplay a case of running a series of MySQL statements (I’d advise converting them to stored procedures for more security and better application seperation) to retreieve the appropriate data. For example:
Products Within a Certain Category (E.g. category_id 1):
SELECT p.product_id, p.name FROM product p INNER JOIN product_category pc ON p.product_id = pc.product_id WHERE pc.category_id = '1';
Count Products Within a Certain Category (E.g. category_id 1):
SELECT COUNT(p.product_id) As myCount FROM product p INNER JOIN product_category pc ON p.product_id = pc.product_id WHERE pc.category_id = '1';
…and that’s it. Extremely simple, can be expanded to any eCommerce system and not convoluted at all 🙂
How would we do this if we wanted to show all products?
i.e Display : Product Name, Product Categories (that this product is listed in)
Cheers
It’s literally just another join without the where contraint. If you post a link where I can download your tab le structure, I’ll post a query for you.
I got this guy to develop my website [SPAM_FILTER] and he tried to explain me this queries becoz my website was using a database,but i am trying to learn but its not the easiest thing.
Where I find this topic gets interesting is nested categories. A while back I needed to store hierarchical data and spent a while looking at various solutions. There’s an old, but good, Sitepoint article on hierarchical database structures that explains various options.
The modified preorder tree traversal(!) method is an interesting one.
I’ve since maade some improvements to this. The main one is storing the branch (E.g. Home > TShirts > Retro > …) in the database for the given product – I then update this when the product gets updated. Saves a heavy call to the database.
Thanks so much for your contribution here. I am in a situation where this helped tremendously. I have one thing I cant figure out. if I want to list my products and next to each, list all the categories that product is in, how would you go about doing that?
eg.
Product 1 – cat2, cat3, cat6
Product 2 – cat3, cat8, cat12
The only way I can think of is to submit separate queries for each product that gets listed to display categories that match. But I’m sure that’s not the best way.
can you please help how you did that through query?
eg.
Product 1 – cat2, cat3, cat6
Product 2 – cat3, cat8, cat12
hello.. it helped me alot.. tell me how can i list categories assigned by a product..
hello,
I know it is an old thread but if you are still there :), how would you add a condition where the article needs to be in 1 category at least ?
Thanks
Hi
I tried save data but multiple categories not saving instead it saving first selected category id.