[Resolved] subtrees ids of categories tree

[searain]

categories table

categoies_id, parent_id
1, 0
2, 0
3, 0
4, 2
5, 2
6, 5

I want to find all the categoreies_id of the subtree, such as for the categories_id as 2. the subtree ids set will be (2, 4, 5, 6).

Is there a simple function for this job? I think recursive algorithm is needed here. Thanks!

LeatherBack already gave me some answers about how to create a categories tree in the following thread. I think I can modify that funtion to do the above job too. But if any one already have the function to do the above work, please help.

http://www.phpbuilder.com/board/showthread.php?s=&postid=10633599#post10633599

Thanks!

[edcellgavin]

i have one. it works but you could tweak it up a bit and try to optimize it and make it suit your needs. hope this helps

// function to recurse all subcategories, each record is pipe-delimited, stored and returned in an array
function myRenderCategories($db, $curr_id = '', $count = 0) {
	static $options;
	if (!isset($curr_id)) $curr_id = '';
	$count++;
	$strQ  = "";
	$strQ  = "SELECT Autokey, CatLevel, CatCode, Description, IsList FROM Categories";
	$strQ .= " WHERE ParentCode = '$curr_id' ORDER BY ParentCode, Categories.SortOrder";
	$result = mySelectDB($strQ, $db);
	$rows  = mysql_num_rows($result);
	if ($rows > 0) {
		while (list($key, $level, $id, $name, $isList) = mysql_fetch_row($result)) {
			$indent = 0;
			if ($curr_id != '') {
				for ($i = 2; $i <= $count; $i++) {
					$indent++;
				}
			}
			$name = "$indent|$name|$level|$id|$key|$isList";
			$options[] = $name;
			myRenderCategories($db, $id, $count);
		}
	}
	return $options;
}
$arrCategories = myRenderCategories($db, '');

[searain]

Great, thanks. I will try to modify your function.

I have a question.

Your function recursively called sql. It will work fine for my case, because I won't have too many categories in the database.

If for a database with large amount of categories, recuresively calls for sql will create too many database activies, will it?

Could we just use one sql to grab the values of categories_id, parent_id, sort_order and save these values in array. And then, we will apply recursive function (modify from yours) to the array. This way, it will only run sql once. Will it work?

Thanks!

[edcellgavin]

great idea!

all you need is another function to simulate the database query, you'll be 'quering' the arrays instead!