Recursive function to generate multidimensional array from database result

Question

I'm looking to write a function that takes an array of pages/categories (from a flat database result) and generates an array of nested page/category items based on the parent ids. I would like to do this recursively, so that any level of nesting can be done.

For example: I'm fetching all the pages in one query, and this is the what the database table looks like

+-------+---------------+---------------------------+
|   id  |   parent_id   |           title           |
+-------+---------------+---------------------------+
|   1   |       0       |   Parent Page             |
|   2   |       1       |   Sub Page                |
|   3   |       2       |   Sub Sub Page            |
|   4   |       0       |   Another Parent Page     |
+-------+---------------+---------------------------+

And this is the array I would like to end up with to process in my view files:

Array
(
    [0] => Array
        (
            [id] => 1
            [parent_id] => 0
            [title] => Parent Page
            [children] => Array
                        (
                            [0] => Array
                                (
                                    [id] => 2
                                    [parent_id] => 1
                                    [title] => Sub Page
                                    [children] => Array
                                                (
                                                    [0] => Array
                                                        (
                                                            [id] => 3
                                                            [parent_id] => 1
                                                            [title] => Sub Sub Page
                                                        )
                                                )
                                )
                        )
        )
    [1] => Array
        (
            [id] => 4
            [parent_id] => 0
            [title] => Another Parent Page
        )
)

I've looked and tried nearly every solution I've come across (there's a lot of them here on Stack Overflow, but have had no luck getting something generic enough that will work for both pages and categories.

Here's the closest I've gotten, but it doesn't work because I'm assigning the children to the first level parent.

function page_walk($array, $parent_id = FALSE)
{   
    $organized_pages = array();

    $children = array();

    foreach($array as $index => $page)
    {
        if ( $page['parent_id'] == 0) // No, just spit it out and you're done
        {
            $organized_pages[$index] = $page;
        }
        else // If it does, 
        {       
            $organized_pages[$parent_id]['children'][$page['id']] = $this->page_walk($page, $parent_id);
        }
    }

    return $organized_pages;
}

function page_list($array)
{       
    $fakepages = array();
    $fakepages[0] = array('id' => 1, 'parent_id' => 0, 'title' => 'Parent Page');
    $fakepages[1] = array('id' => 2, 'parent_id' => 1, 'title' => 'Sub Page');
    $fakepages[2] = array('id' => 3, 'parent_id' => 2, 'title' => 'Sub Sub Page');
    $fakepages[3] = array('id' => 4, 'parent_id' => 3, 'title' => 'Another Parent Page');

    $pages = $this->page_walk($fakepages, 0);

    print_r($pages);
}

Answer 1

Some very simple, generic tree building:

function buildTree(array $elements, $parentId = 0) {
    $branch = array();

    foreach ($elements as $element) {
        if ($element['parent_id'] == $parentId) {
            $children = buildTree($elements, $element['id']);
            if ($children) {
                $element['children'] = $children;
            }
            $branch[] = $element;
        }
    }

    return $branch;
}

$tree = buildTree($rows);

The algorithm is pretty simple:

1. Take the array of all elements and the id of the current parent (initially 0/nothing/null/whatever).
2. Loop through all elements.
3. If the parent_id of an element matches the current parent id you got in 1., the element is a child of the parent. Put it in your list of current children (here: $branch).
4. Call the function recursively with the id of the element you have just identified in 3., i.e. find all children of that element, and add them as children element.
5. Return your list of found children.

In other words, one execution of this function returns a list of elements which are children of the given parent id. Call it with buildTree($myArray, 1), it will return a list of elements which have the parent id 1. Initially this function is called with the parent id being 0, so elements without parent id are returned, which are root nodes. The function calls itself recursively to find children of children.