QUERY - is there a better way?

[Canman2005]

Hi All

 

I wonder if someone can help me out.

 

Basically I have 3 tables, they are

 

`products` , `product_categories`, `categories`

 

So the tables might for example look like

 

`products` (`id`,`title`)
`1`,`iphone`
`2`,`microsoft mouse`

 

`categories` (`id`,`title`,`level`,`parentid`)
`1`,`mouses`,`1`,`0`
`2`,`microsoft`,`2`,`1`
`3`,`mp3`,`1`,`0`
`4`,`apple`,`2`,`3`

 

So `level` basically says that `mouses` is a top level category and `microsoft` is a sub category of `mouses` and `mp3` is a top level category) and `apple` is a sub category of `mp3`

 

`product_categories`
`id`,`product_id`,`category_id`
`1`,`1`,`4`
`2`,`2`,`2`

 

hopefully that makes sense, but it's basically saying that product id number 1 (iphone) belongs to category number 2 (apple) and product number 2 (microsoft mouse) belongs to category number  (microsoft)

 

I then have a series of tick boxes through PHP and it outputs in HTML something like the following

 

<input name="category1[]" value="1">Mouses

<input name="category2[]" value="2">Microsoft

<input name="category1[]" value="3">MP3

<input name="category2[]" value="4">Apple

 

Now at the moment I run a QUERY which looks like

 

SELECT * FROM products p JOIN products_categories pc ON p.id = pc.product_id WHERE (pc.category_id = 2 || pc.category_id = 4)

 

which returns me a result, perfect.

 

I wonder though, how easy is it to further that QUERY and if I select "Mouses" (ID 1) tickbox, then I would find a result, even though I don't store the top level category id (ie: 1 = Mouses) within the `product_categories` table.

 

My only option is to also store the top level category id (ie: 1 = Mouses) within the `product_categories` table.

 

Is there a way without me doing this?

 

Thanks in advance

 

Dave