You can fetch all categories at once.
Suppose you have a flat result from the database, like this:
$categories = array(
array('id' => 1, 'parent' => 0, 'name' => 'Category A'),
array('id' => 2, 'parent' => 0, 'name' => 'Category B'),
array('id' => 3, 'parent' => 0, 'name' => 'Category C'),
array('id' => 4, 'parent' => 0, 'name' => 'Category D'),
array('id' => 5, 'parent' => 0, 'name' => 'Category E'),
array('id' => 6, 'parent' => 2, 'name' => 'Subcategory F'),
array('id' => 7, 'parent' => 2, 'name' => 'Subcategory G'),
array('id' => 8, 'parent' => 3, 'name' => 'Subcategory H'),
array('id' => 9, 'parent' => 4, 'name' => 'Subcategory I'),
array('id' => 10, 'parent' => 9, 'name' => 'Subcategory J'),
);
You can create a simple function that turns that flat list into a structure, preferably inside a function. I use pass-by-reference so that there are only one array per category and not multiple copies of the array for one category.
function categoriesToTree(&$categories) {
A map is used to lookup categories quickly. Here, I also created a dummy array for the "root" level.
$map = array(
0 => array('subcategories' => array())
);
I added another field, subcategories, to each category array, and add it to the map.
foreach ($categories as &$category) {
$category['subcategories'] = array();
$map[$category['id']] = &$category;
}
Looping through each categories again, adding itself to its parent's subcategory list. The reference is important here, otherwise the categories already added will not be updated when there are more subcategories.
foreach ($categories as &$category) {
$map[$category['parent']]['subcategories'][] = &$category;
}
Finally, return the subcategories of that dummy category which refer to all top level categories._
return $map[0]['subcategories'];
}
Usage:
$tree = categoriesToTree($categories);
And here is the code in action on Codepad.