[Resolved] function to count category depth

[perpetualshaun]

I'm trying to write a function that will return the number of "levels" under a given category. For example, if my category tree would look something like this (in text):

Top Level 1
Sub Level 1
Sub Level 2
Sub Level 3

I want to create a function where I can call like findSubCount(Top Level 1) and get the number 3 back.

After trading messages w/ a few different folks on this forum a while back, I found a function that till create the "tree" for me based on the MySQL results, and I tried several possible alterations to this function to try and get a number back like I described above, but no luck. Here's what I have working to create the tree:

function categoryTreeView($id,$top_level_id) {
if($top_level_id != "NULL") global $top_level_id;
static $tab;
$tree_query = "SELECT cat_id, cat_title FROM categories WHERE cat_parent_id=".$id." ORDER BY cat_rank ASC";
$tree_result = @($tree_query) OR die ("Couldn't complete this MySQL query: ".$tree_query."<br />\n<br />\nError: ".mysql_error());
if(mysql_num_rows($tree_result) > 0) {
$oldtab = $tab; // backup tab
if($id != $top_level_id) $tab .= '&nbsp;&nbsp;&nbsp;'; // increase tab
while($row = mysql_fetch_array($tree_result)) {
echo $tab."";
if($id != $top_level_id)
echo "<a href=\"?cat_id=".$row["cat_id"]."\">".$row["cat_title"]."</a><br />\n";
else
echo "<strong>".$row["cat_title"]."</strong><br />\n";
categoryTreeView($row["cat_id"], NULL);
}
$tab = $oldtab; // restore tab
}
}

... and then I simply call it like this: categoryTreeView(0,0) - to begin w/ the "top level".

So, is there a function guru out there that has some advise for me on how I can pass something like findSubCount(0,0) and get the number 3 back? For this application, I already know that the max "depth" is three levels, but I want to come up w/ a function that will accommodate pretty much any range.

Thanks,

Shaun

[ajbrown]

Try this:

function num_sub_categories($cat_id) {

global $connection;

$sql = "SELECT cat_id FROM categories WHERE cat_parent_id=".$cat_id." ORDER BY cat_rank ASC";
$result = mysql_query($sql,$connection);

$count = mysql_num_rows($result);
if ($count > 0) {
	while ($row = mysql_fetch_assoc($result)) {
		$count += num_sub_categories($row['cat_id']);
	}
}

return $count;

}

[perpetualshaun]

I'm afraid that didn't work. That gave me a very large number, in this case 386. I had a function earlier yesterday that was giving me the same number.

Looking at this loop:

function num_sub_categories($cat_id) { 

    global $connection; 

    $sql = "SELECT cat_id FROM categories WHERE cat_parent_id=".$cat_id." ORDER BY cat_rank ASC"; 
    $result = mysql_query($sql,$connection); 

    $count = mysql_num_rows($result); 
    if($count > 0) { 
        while ($row = mysql_fetch_assoc($result)) { 
            $count += num_sub_categories($row['cat_id']); 
        }
    }

    return $count; 

}

The problem is that the $count variable is being incremented every time there is a match found, in the while loop. So for exampe, there are 23 Top Level categories, a couple hundred subs right under that, and then a few more further below that.

I want a function that will return a 3 to me here when I pass $cat_id of 0, or 2 when I pass the id of one of the subs ($cat_id of 220 in this instance for me).

[ajbrown]

I see...you're looking for the depth of the category, not the recursive number of sub-categories.

Try this:

function category_depth($cat_id) { 

global $connection; 

$sql = "SELECT cat_id FROM categories WHERE cat_parent_id=".$cat_id." ORDER BY cat_rank ASC"; 
$result = mysql_query($sql,$connection); 

$count = mysql_num_rows($result); 
$depth = 1;
if ($count > 0) { 
	$roof_depth = 0;
    while ($row = mysql_fetch_assoc($result)) { 
        $cat_depth = category_depth($row['cat_id']); 
        if ($cat_depth > $roof_depth) $roof_depth = $cat_depth;
    } 
} 

$depth += $roof_depth; 

return $depth; 

}