Associate Products with Multiple Categories Using MySQL

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_id (PK)

category_id (PK)

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, 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 🙂

Published by

Rob Allport

Web Developer based in Stoke-on-Trent Staffordshire Google+ - Twitter

7 thoughts on “Associate Products with Multiple Categories Using MySQL”

  1. 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)


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

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

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

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

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

    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.

Leave a Reply

Your email address will not be published. Required fields are marked *