sql query- returning items in subcategories

help with getting records in subcategories in 1 query

[tomroberts]

Hi everyone, this is my first post.

I'm just getting started with PHP/mySQL and i'm trying to build a product catalogue, with dynamic categories/subcategories (2 levels).

I'm using two tables, one for products and one for categories.
My category table structured like this:

cat_id ¦ parent_id ¦ cat_name
------------------------------
1 ¦ 0 ¦ Fruit
2 ¦ 1 ¦ Apple
3 ¦ 1 ¦ Orange
4 ¦ 1 ¦ Banana
5 ¦ 0 ¦ Meat
6 ¦ 5 ¦ Chicken
7 ¦ 5 ¦ Beef

My products table also has cat_id column which i'm using to determine which category/subcategory the product falls under.

This works fine- I can query the db to get all the products in a category or subcategory, but what's the best way to return all the products in a category AND its subcategories?

At the moment I am querying the db once to get the id's of all the subcategories under the chosen category, then using this to build a second query which returns the products. It works- but I'm sure there's a way to do this with 1 query.

Hope this makes sense, thanks in advance for your help!

- Tom

[Demaestro]

Hello tomroberts, welcome to WebmasterWorld.

I'm happy to to help.

Can you post the 2 queries you use and a small sample of data from the other table?

[tomroberts]

Hi Demaestro,

Thanks for fast reply.

The products table is structured like this:

prod_id ¦ cat_id ¦ prod_name
----------------------------
1 ¦ 1 ¦ product 1
2 ¦ 4 ¦ product 2
3 ¦ 5 ¦ product 3

To get all the products in a category and its subcategories I would first query the category table like this:

SELECT cat_id FROM category WHERE parent_id = '1'

Which would give me 2,3,4. I use these category_id's and the original parent category id to build a second query like this:

SELECT * FROM products WHERE cat_id IN ('1,2,3,4')

Which gives me all the products in the 'Fruit' category, as well as all products in 'Apple','Orange' & 'Banana'.

I'm sure this is hopelessly inefficient but I can't figure out how to achive this in a single query.

Thanks again,

- Tom

[Demaestro]

My pleasure.... all you have to do is add a sub-select statement and place it where you want the list of ids returned.

SELECT * FROM products WHERE cat_id IN (SELECT cat_id FROM category WHERE parent_id = '1')

Hope this makes sense. If you have more questions post back.

[tomroberts]

It seems so obvious now!

Thanks for your help Demaestro, I think i'll be posting a lot more around here in future!

- Tom

[Demaestro]

No problem... see you around.