Getting total sub category rows

micmac

Hi all,
If i have the following table structure: (this is a table containing categories, sub-cateories and however many categories under that)

knowledge_category_id  |  knowledge_category_parent  |  knowledge_category_name
1  |  0  |  Home
2  |  1  |  FAQ
3  |  1  |  Support
4  |  1  |  Manual
5  |  2  |  Ordering
6  |  5  |  Credit Cards

What i want to be able to do is beside the top category name which i output in the query below:

<?
$query_knowledgebase_categories = mysql_query("select * from knowledgebase_categories where knowledge_category_parent = 1");
while($row = mysql_fetch_array($query_knowledgebase_categories)){
?>                          

						  <TR>
                            <TD vAlign=top width="50%"><A 
                        href="knowledgebase.php?knowledge_category_id=<? echo $row["knowledge_category_id"] ?>"><STRONG><? echo stripslashes($row["knowledge_category_name"]) ?></STRONG></A> (<? echo $total_rows ?>) </TD>
                            <TD vAlign=top width="50%">&nbsp;</TD>
                          </TR>
<?
}
?>

I want to be able to diplay the $total_rows. The $total_rows will be all the sub categories and any other categories under the top category.
So in the example above the output should read:

FAQ (2)
Support (0)
Manual (0)

FAQ should display 2 $total_rows because it has two sub-categories associated to it.

Any ideas?
Cheers,
micmac

tomandhannah

Haven't tested it, but something like this should do the trick:

<?
$query_knowledgebase_categories = mysql_query("select * from knowledgebase_categories where knowledge_category_parent = 1"); 

while($row = mysql_fetch_array($query_knowledgebase_categories)) {
	extract($row);

$total_rows= mysql_num_rows("SELECT * 
from knowledgebase_categories 
where knowledge_category_parent = $row[knowledge_category_id]");
?>

<TR><TD vAlign=top width="50%">
<A href="knowledgebase.php?knowledge_category_id=
<? echo $row["knowledge_category_id"]; ?>"><STRONG>
<? echo stripslashes($row["knowledge_category_name"]); ?>
</STRONG></A> (<? echo $total_rows; ?> ) </TD> 
<TD vAlign=top width="50%">&nbsp;</TD></TR>

<?
	}
?>

Drakla

I've got a heirarchy just like yours, and this pulls out all the sub category ids from a given id. Zero would return the whole lot. You could just change the implode to a count of the results array.

I first did this with recursion and did some tests and this version mullers it

// iterative version MUCH quicker
function all_sub_ids($id)
{
	$offset=0;
	$results=array();

require("db-connect-inc.php");

do
{
	$q="SELECT id FROM cats WHERE pid=$id";
	$db->query($q);

	while($row=$db->fetch_row())
		$results[]=$row[0];
}
while(@$id=$results[$offset++]);

return implode(",",$results);
}