Trouble retrieving products under a complex category structure in PHP

Question

Hello,

I hope you guys can help me with this one. So, I have a products table and a categories table. The structure for the categories table is listed bellow. There will be about 20 categories with three or four levels deep. In order to get the category tree a wrote a recursive function and it is working fine.

My new task is to display products for a given category. As an example, I will use the data shown bellow. So, if I add a product with category_id = 7, I have to display that product when the user selects category 7, but also when the user will select category 4, or category 1. Also, if a user selects category with id = 1, it has to display products for category = 1, 4, 5, 6.

My categories table looks like this:

CREATE TABLE `categories` (
 `category_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
 `category_name` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `category_slug` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `category_parent` smallint(5) unsigned NOT NULL DEFAULT '0',
 `category_description_ro` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `category_description_en` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (`category_id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1

Bellow is an example of data in the table:

category id | category name | category_parent

1            Categoria 1            0        
2            Categoria 2            0        
3            Categoria 3            0        
4            Categoria 1.1          1        
5            Categoria 1.2          1        
6            Categoria 1.3          1        
7            Categoria 1.1.2        4 

Answer 1

Hi, in similar cases, I've made a recursive function that will get me all children of a category, and the category itself. Then you can simply say

WHERE category in (cat1, cat2, ...)

in SQL

update

you want a function getChildren that yields, for, say 1, the result [1,4,5,6,7]

then your query is

WHERE category in (1,4,5,6,7)

which is a short way of writing

WHERE category=1 OR category=4 OR category=5 ...

So this is really the statement that will find products even though they have exactly one category ID attached

Answer 2

This is how I would do it:

1. ... WHERE category_id = n OR category_parent = n: select the current category (n is current) and it's first-level subcategories. If you want further layers deep, you'll need to recurse down the layers.
2. ... WHERE category_id = p: select parent category (p is parent id). Repeat until p is 0.