CodeIgniter Forums
Get recursive menu from database - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Get recursive menu from database (/showthread.php?tid=17986)

Pages: 1 2


Get recursive menu from database - El Forum - 04-21-2009

[eluser]mvdg27[/eluser]
Hi guys,

I've been breaking my head all night, on getting a recursive menu from my database! I hope someone can give me some pointers on how I would be able to achieve this.

My database structure is very simple:

id|label|parent

And I'm trying to achieve an array with the following structure as a result of my menu-retrieval function:

Code:
Array
(
    [0] => Array
        (
            [label] => Label 1
        )

    [1] => Array
        (
            [label] => Label 2
            [sub] => Array
                (
                    [0] => Array
                        (
                            [label] => Label 2.1
                            [sub] => Array
                                (
                                    [0] => Array
                                        (
                                            [label] => Label 2.1.1
                                        )

                                )

                        )

                    [1] => Array
                        (
                            [label] => Label 2.2
                        )

                )

        )

    [2] => Array
        (
            [label] => Label 3
        )

    [3] => Array
        (
            [label] => Label 4
        )

)

Hope someone can help me on my way with this one!

Thanks!


Get recursive menu from database - El Forum - 04-21-2009

[eluser]Dam1an[/eluser]
Although the code below isn't what you asked for, its some old code I had which done a nested drop down list of categories, so same concept.
Its essentially a recursive call starting with the top level categories, then searching for categories which have that as its parent etc

If you can't make sense of it, I'll adopt it to your situation in the morning, but I'm going bed now

Code:
// Creates a list of all the categories, nested within each other, used when
// adding products, cetegories etc.  Use the folloing code to add the drop down
// list into a page
/*
<?php
                print "<select name='parent_id'>";
                print "<option value='0'> </option>";
                $selcat="SELECT * from categories order by category_name ASC";
                $selcat2=mysql_query($selcat) or die("Could not select category"
);
                parentCategory(0,0,$selcat2);
                print "</select><br>";
                ?&gt;
*/
function parentCategory($root, $depth, $sql)
{
        $row=0;
        while ($acat = mysql_fetch_array($sql))
        {
                if ($acat['parent_id'] == $root)
                {
                        print "<option value='" . $acat['category_id'] . "'>";
                        $j=0;
                        while ($j<$depth)
                        {
                                print "&nbsp;&nbsp;&nbsp;";
                                $j++;
                        }
                        if($depth>0)
                        {
                                print " - ";
                        }
                        print $acat['category_name'] . "</option>";
                        mysql_data_seek($sql,0);
                        parentCategory($acat['category_id'], $depth+1,$sql);
                }
                $row++;
                mysql_data_seek($sql,$row);
        }
} // parentCategory

On a side note, my old (pre CI) code was hideous


Get recursive menu from database - El Forum - 04-21-2009

[eluser]Clooner[/eluser]
Check out this topic http://ellislab.com/forums/viewthread/108528/


Get recursive menu from database - El Forum - 04-22-2009

[eluser]mvdg27[/eluser]
@Dam1an: thanks for your example, this was something I sort of had already .. printing directly to the screen and using a loop over the depth with '&nbsp;' to indicate the level in the menu. My problem however is how to get everything in an array .. I'll play around some more with your example, but in case you're able to whip up an example easily, by all means Wink Thanks!

@Clooner: Thanks for your suggestion, I've looked into MPTT before, but since my menus will never be very deep, I prefer keeping the simpler 'parent->child' model


Get recursive menu from database - El Forum - 04-22-2009

[eluser]Dam1an[/eluser]
Ok, here's some quick code I've mocked up

Code:
&lt;?php
class Test extends Controller {
    function index() {
        $menu = Test::create_nested_menu();
        
        print_r($menu);
    }
    
    function create_nested_menu($parent = 0) {
        $items = array();
        
        $this->db->where('parent', $parent);
        $query = $this->db->get('menu_items');
        $results = $query->result();
        
        foreach($results as $result) {
            $child_array = Test::create_nested_menu($result->id);
            if(sizeof($child_array) == 0) {
                array_push($items, $result->label);
            } else {
                array_push($items, array($result->label, $child_array));
            }
        }
        
        return $items;
    }
}
?&gt;

and with the array in your example, it would generate the following array
Code:
Array (
    [0] => Label 1
    [1] => Array (
        [0] => Label 2
        [1] => Array (
            [0] => Array (
                [0] => Label 2.1
                [1] => Array (
                    [0] => Label 2.1.1
                )
            )
            [1] => Label 2.2
        )
    )
    [2] => Label 3
    [3] => Label 4
)

You'll obviously want to move the function into a helper, but you get the idea (hopefully)
If not, I'll explain it in detail Smile


Get recursive menu from database - El Forum - 04-26-2009

[eluser]mvdg27[/eluser]
Hi Dam1an,

I wasn't able to respond earlier. But your code has helped me to make the exact recursive function I need. Thanks a lot, this method works like a charm!

Cheers, Michiel


Get recursive menu from database - El Forum - 04-26-2009

[eluser]JoostV[/eluser]
Damian's solution does a seperate query on every parent to obtain its children. You could end up doing twenty or more queries just to fetch a single tree.

You might want to Google for 'adjacency list sql' or check out MPTtree: http://ellislab.com/forums/viewthread/74114/


Get recursive menu from database - El Forum - 04-26-2009

[eluser]Dam1an[/eluser]
[quote author="JoostV" date="1240789405"]Damian's solution does a seperate query on every parent to obtain its children. You could end up doing twenty or more queries just to fetch a single tree.

You might want to Google for 'adjacency list sql' or check out MPTtree: http://ellislab.com/forums/viewthread/74114/[/quote]

I never claimed my solution was perfect, but it gets the job done, and if/when performance ecomes an issue, its all a self contained function, so shoud be easy ehough to update...

Although I should really look into adjacency list sql lol


Get recursive menu from database - El Forum - 04-26-2009

[eluser]JoostV[/eluser]
@Dam1an: no offence meant, Dam1an.


Get recursive menu from database - El Forum - 04-26-2009

[eluser]Dam1an[/eluser]
@JoostV: No offence taken, I just wanted to clear up the fact I know my solution wasn't ideal
Looking at this adjacency list sql... looks fun Smile

Has nobody made a function you could add to the DB class for this sort of thing, cause I've seen stuff like this come up a few times on the forums (if not, I might have a go at hacking a generic function together)