How could I either combine this query or send these to the view correctly?

[eluser]Shpigford[/eluser]
I have a table named 'categories'. Each row has a field called 'parent'. If 'parent' is 0, it's a top-level category, if not...then it's a sub category.

I'm having an issue ultimately querying that and then sending it to the view.

It's obviously pretty easy to pull all the categories:

Code:

function getCategories()
    {
        
        $this->db->select('id, name, parent_id');
        $this->db->where('type', 'product');
        $this->db->where('active', 'Y');
        
        $query = $this->db->get('categories');
        
        return $query->result();
    }

Then in my controller I set that with:

Code:

'categories' => $this->categories->getCategories()

Then in the view:

Code:

<?php foreach($categories as $category): ?>
<p>&lt;?=$category->name?&gt;</p>
&lt;?php endforeach; ?&gt;

But what I ultimately want to pull of is having the top level categories display and then under each top level category have its subcategories listed:

Category 1
--Category 1a
--Category 1b
Category 2
--Category 2a
etc etc

Any suggestions for pulling that off?