Dynamic array for UL method in HTML helper

[eluser]MaartenDeGroote[/eluser]
Hi everyone,

I'm not sure if this question has been asked before, but i just can't seem to find a satisfying answer.

For my navigation structure I want to make use of the UL method from the HTML helper.

I want the array that is passed in as argument to be dynamically generated from a database table through a recursive function. The table's name is called "categories" and has the following structure:

Code:

ID | Categoryname | Parent

However, I just can't find the appropriate function to get this job done. Until now, I have spent hours of trying without any luck.

Any help is greatly appreciated!

[eluser]JoostV[/eluser]
Fetching from database in a loop is a dangerous manouvre. As your table grows, you could end up with hundreds of queries.

If your category list is no more than two levels deep, you can fetch all categories in a single query and loop through the results to create an array that will display nicely with ul():

Code:

// Fetch categories, no parents first
$categories = $this->db->order_by('Parent')->get('categories')->results_array();

// Create an array of links, to display with ul()
if (is_array($categories) && count($categories)) {
    
    // Turn you category array into an assoc array, with ID being the key
    // We need this to be able to retrieve the proper parent anchors, later on
    $tmp_array = array();
    foreach ($categories as $category) {
        $key = current($category);
        $tmp_array[$key] = $category;
    }
    
    // Construct an array of navigation links, ready to use with ul();
    $nav_array = array();
    foreach ($categories as $page) {
        
        // Set anchors
        $href = anchor('cat/' . $page['ID'], $page['Categoryname']);
        $parenthref = anchor('cat/' . $page['Parent'], $categories[$page['Parent']]['Categoryname']);
        
        if ($page['Parent'] == 0) {
            // This is a level 1 anchor
            $nav_array[$page['ID']] = $href;
        }
        else {
            // This is a level 2 anchor
            if (isset($nav_array[$page['Parent']])) {
                unset($nav_array[$page['Parent']]);
                $nav_array[$parenthref] = array();
            }
            $nav_array[$parenthref][$page['ID']] = $href;
        }
    }
}

[eluser]MaartenDeGroote[/eluser]
Well thanks Joost. This is a great start.

But what about when I have more levels? Sometimes 2, sometimes 4, or sometimes even 7? The recursive part tricks me the most and I'm really challenged figuring this one out

Thanx again for your reply!

[eluser]JoostV[/eluser]
I always use MPTT in that case. You fetch the entire tree in just two queries, creating navigation is easy once you figure out the recursive function for that.

However:
- it takes quite some time to wrap your head around.
- if you use it in a cms it is vital that only one person at a time is allowed to make changes to the categories or you iwll end up in an epic disaster. If that's no problem for you I'd be happy to post my code on Github for you. It'll take some time preparing, though.

[eluser]MaartenDeGroote[/eluser]
It would be great if you are willing to post the code so I can use it as a reference!

Few questions though:
1. What is MPTT?
2. Why fetch the tree in two queries?