php Recursive function for tree like structure

[game_over]

I'm struggling to get my head around this one, i've had a few attempts and failed miserably.

 

Basically I have a list of categories in a database, each with an ID. A category can have a parent category.

 

I need to output the structure in some sort of tree

 

Main category

- Child 1

- Child 2

Another category

- Child 1

- Child 2

 

Here is a very basic structure example.

 

 

In this case, Fruit is a child of Food and Shoes is a child of Clothing.

 

I can manage it quite easily performing several queries but I need to do it as efficient as possible with one query and whatever is the fastest method to generate the tree like structure, either a loop or recursive function.

 

Any ideas?

 

Thanks

 

 

[Seahorsepip Veteran]

There are multiple structures, for very large menus I use the nested set model explained here:

http://stackoverflow.com/questions/5916482/php-mysql-best-tree-structure

 

Otherwise it's a matter of making an sql function that grabs all direct childs of an id and call it again for all those items and again for all those items till items don't have any children.

 

In PHP it's the same, you have a $items array with id, parentId and name as example:

function awesome($parentId) {
    $tree = "";
    foreach($items as $item) {
        if($item->parenId == $parentId) {
            $tree += $item->name;
            $tree += awesome($item->id);
        }
    }
    return $tree;
}

 

Then call it:

 

$awesomeTree = awesome(NULL);

 

You can't avoid loops or recursion when you use a structure with ids and parentIds, by defining a sql function that is recursive it's not such a big problem for normal sized menus. For large menus it can become slow so I would use a different structure for that as I mentioned above, keep in mind that such structures are slower when you edit menus, there's always a trade off.

 

More info and SQL examples: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/