Subcategories - a better way than mine

[eluser]iDenta[/eluser]
Hi!

I am working on a small adminarea for one of my projects, there i wan't to show all of my categories, and subcategories. So the function is only used when in the adminarea.

I have hacked together a controller that takes care of that:

Code:

//Get Level 1
$this->db->where("parent_id", 0);
$this->db->order_by("id", "asc");
$query = $this->db->get('categories');
        
if ($query->num_rows() > 0)
{
    foreach ($query->result() as $row):
            
    $data['cat_title'] = $row->title;
    echo $data['cat_title']."<br />";
            
        // Get Level 2
        $this->db->where("parent_id", $row->id);
        $this->db->orderby("id", "asc");
        $query2 = $this->db->get("categories");

            foreach ($query2->result() as $row2):
            $data['subcat_title'] = $row2->title;
            $data['subid'] = $row2->parent_id;
            echo "---".$data['subcat_title']."<br />";
                    
                // Get Level 3
                $this->db->where("parent_id", $row2->id);
                $this->db->orderby("id", "asc");
                $query3 = $this->db->get("categories");

                foreach ($query3->result() as $row3):
                    $data['subsubcat_title'] = $row3->title;
                    echo "------".$data['subsubcat_title']."<br />";
                endforeach;
            endforeach;
        endforeach;
}

And that spits out something like:

Category 1
-- Subcategory
Category 2
-- Subcategory
-- Subcategory
---- Subsubcategory
Category 3
Category 4
-- Subcategory

And so on.

But i feel that this could be made in a much nicer way... Although it does work, it doesn't seem very efficient.

Since i am a complete newbie, this is about the best i could do So any pointers on making this code look better is appreciated.

And ultimately i would like to be able to go deeper than 3 steps, but that isn't my main problem...

[eluser]TheFuzzy0ne[/eluser]
I'd recommend you look into MPTT (Modified Preorder Tree Traversal). However, I'm not entirely sure if this would allow you to dump all categories and sub categories in this fashion.

[eluser]TheFuzzy0ne[/eluser]
I was just thinking that perhaps you could have a column for indentation. Essentially, you'd grab your categories according to the indentation level, starting with 0, then 1, then 2 and so on. You'd need one query for each indentation level. I'm not entirely sure how it would work, or even if it would, but it might be something you'd want to consider. Basically, you need a method where is doesn't matter how many sub levels there are. It might even be an idea to store your categories in an array and serialize the data into a file.

[eluser]pistolPete[/eluser]
[quote author="TheFuzzy0ne" date="1236728521"]I'd recommend you look into MPTT[/quote]

I'd say so as well.

There are already some CI libraries:
- MPTtree, Hieararchical trees in a database table
- Hierarchical database trees using nested sets

[eluser]drewbee[/eluser]
[quote author="pistolPete" date="1236729742"][quote author="TheFuzzy0ne" date="1236728521"]I'd recommend you look into MPTT[/quote]

I'd say so as well.

There are already some CI libraries:
- MPTtree, Hieararchical trees in a database table
- Hierarchical database trees using nested sets[/quote]

+2!!

Two queries to retrieve an entire trees hiearchy can't be beat. These setups can also be used to get breadcrumbs as well.

[eluser]Johan André[/eluser]
Hmmm... Your code is not easy to extend. Let's say you one day want 4 or more levels?
MPTree will do this (i think). Or you can just write an recursive function to output the list.
MPTree will be faster but might be overkill for an adminarea, where you typically have quite few users.
The recursive method will use 1 query per level, compared to MPTTree which only use one to get the whole tree.

Good luck!