How can I find all products from one category using mysql?
Posted by Michael G.K on 30 August 2008 05:17 AM

There are 3 main tables containing information about products and categories.

1) products – there is pid field (internal product id), and cid – primary product's category id
2) categories cid this is category id
3) products_categories – contains a relationship between primary and secondary products categories. There are 3 fields: pid – product id, cidcategory id and is_primary – indicates if this is primary catagory for product. It is set to 1 when category is same as products.cid; otherwise it is 0.

To select all products from a category run this sql query:
SELECT products.*
FROM products_categories
INNER JOIN products ON =
WHERE products_categories.cid = 777
GROUP BY products.product_id;

In this query "777" is category id (cid). Replace it with your category id

If you also would like to select ALL products in NESTED categories of a parent category, you have to change this query just a bit – add in WHERE statement subcategories ids you need:
WHERE products_categories.cid = 777 OR products_categories.cid = 555 OR products_categories.cid = 999 OR products_categories.cid = 111.

(260 vote(s))
Not helpful

Comments (0)
Post a new comment
Full Name:
CAPTCHA Verification 
Please enter the text you see in the image into the textbox below (we use this to prevent automated submissions).