Recursive categories

[eluser]Thorpe Obazee[/eluser]
I am having quite a problem with my categories. I need to show all categories from the database but they should show below their parent category.

I am running out of ideas on how to publish all categories like this. I am looking into something like the image attached.

[eluser]JoostV[/eluser]
There is probably a much more efficient way to do this if you use a subquery. But this is a way to organise your category data in php.

Code:

// Create empty arry to hold categories
$category = array();

// Retrieve categories from DB, order by parent_id
$this->db->order_by('parent_cat');
$query = $this->db->get('categories');

// Store results in array, parents follewed by children
if ($query->num_rows() > 0) {

    // Store results in array
    $result = $query->result_array();

    foreach ($result as $row) {

        if (!$row['parent_cat']) {// This is a parent
            $category[$row['id']]['parent'] = $row;
        }
        else { // This is a child
            $category[$row['parent_cat']]['children'][] = $row;
        }

    }
}

// Print to screen
echo '<h1>Ordered categories<h1>';
echo '<pre>' . print_r($category, true) . '</pre>';

[eluser]manilodisan[/eluser]
Another clever solution that might help you (I'm using it in one of my scripts):

http://www.sitepoint.com/article/hierarc...-database/

[eluser]Rick Jolly[/eluser]
JoostV's method will only account for 1 level of child categories. Manilodisan's link refers to the 2 common methods of dealing with hierarchical data in a database:
1) Adjacency List
2) Modified Preorder Tree Traversal aka Nested Sets.

The adjacency list is simpler, but less efficient for select queries. If your categories aren't nested very deep, then I'd use the adjacency list.

[eluser]Pascal Kriete[/eluser]
I'm gonna toss in a third method that I like a lot. The materialized path model, which is good for any depth tree that has to deal with a lot of insertions (particularly leaf nodes).

Select's aren't as efficient as the nested sets, but writes are not as volatile (no need to re-index half the tree).

[eluser]Thorpe Obazee[/eluser]
I'll look into manilodisan's link. I think it's doable for my db structure. Thanks. Should update this thread once I get it working.