So this has been haunting me for a very long time...
I have a category structure with an unlimited hierarchy:
Table name: CATEGORIES
FIELDS: ID, CATEGORY, ETC
And I have a Listings table...
Table name: LISTINGS
FIELDS: ID, CATEGORY, ETC
to organize the parent-child relationship between categories I assign the ID of a category to the CATEGORY field in the CATEGORIES table.
Top level categories have an empty CATEGORY field,
a child level category has the ID number of it's parent in the CATEGORY field.
A sub-sub-level child has the ID number of it's parent in the CATEGORY field, but it does NOT associate with it's grand-parent in any way.
So to effectively count all Listings within a Top Level category, I would need to search recursively through the entire structure...
This is where my brain checks out.
I am using a single level count right now...
$sql_count = "SELECT count(*) FROM LISTINGS";
$res_count = mysql_query($sql_count);
$row_count = mysql_fetch_row($res_count);
echo $row_count[0];
which gets listings in the top level to show... if anyone could help me figure out how to also count any sub-level listings to display the total of any Top Level category, it would be MOST appreciated!
Thanks
-Arron
