• Sign in to Neowin Faster!

    Create an account on Neowin to contribute and support the site.

  • 0
Sign in to follow this  

Recursive function for tree like structure

Question

game_over    794

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.

 

59213c324c21b_ScreenShot2017-05-21at08_04_49.thumb.png.0105bbb0f643ad39380c850786cbe710.png

 

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

 

 

Share this post


Link to post
Share on other sites

2 answers to this question

Recommended Posts

  • 0
Seahorsepip    610

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/

 

  • Like 1

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.