Counting listings in a parent categories subcategories?...

[listenmirndt]

Hi all,

I am trying to count listings within a parent categories subcategories...

The problem is that the subcategories can have their own categories, and those can have their own, and so on forever...

So, my database looks like this

--=CATEGORIES=--
ID
TITLE
PARENT

The ID of a parent category will be assigned to subcategories PARENT field, and then the same for a sub-sub category, the sub-sub category will get the subcategories ID value, and I have no further reference to the grandparent of a sub-sub category,

So anyway,
I am a little unsure how to traverse this possible unlimited hierarchy of sub categories,

my LISTINGS table has a field called CATEGORY, which contains the unique ID number of the category which it belongs to,

Is possible to count all listings which have either a sub category (or deeper ) of the parent category with this kind of setup?

Thanks!
-Arron

[bretticus]

Wow, now that's a conundrum! It appears to me that you either need to write a recursive function in MySQL, or perhaps, you can count numbers using a GROUP_CONCAT. See this example.