Getting # of files in sub-categories

[ShawnK]

I am almost finished with my download manager but I am having on big problem.

Ok, I've got one table kdd_filesinside that table I have the following columns:

file_id
file_cat_id
file_location
file_title
file_long_desc
file_short_desc
file_downloads

and in my kdd_categories table I have this:

cat_id
cat_parent_id
cat_title

I have it set up where you can have UNLIMITED nested categories just by changing the cat_parent_id. When I want to get the # of files in the lowest category I can do that.

Main Cat 1

• Sub Cat
  --- Sub-Sub Cat

• Sub Cat 2
  --- Sub-Sub Cat

So if I want to get the number of files in Sub-Sub Cat all I do is run this query:

SELECT * FROM $db[4]_files WHERE `file_cat_id` = '$_GET[id]`

note: $db[4] is the variable that holds the db prefix( in this case 'kdd')

My problem is that I need to get the total number of files in Main Cat 1. How can I set up a loop that will go through all subcategories and add them up?

Any help would be much appreciated.

[Drakla]

I've got a similar system, and first wrote a recursive solution, then a new version that's loooads quicker -

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

require("adminwis/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);
}

Run that passing the category id, then from the results just do

$q = "SELECT count(*) FROM files WHERE catid IN ($theIds)";

EDIT: of course you might want to add the initial category id to $theIds