Recursive function to generate multidimensional array from database result

Solution 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.

Solution 2:

I know this question is old, but I Was facing a very similar problem - except with a very large amount of data. After some struggle, I managed to build the tree in one pass of the resultset - using references. This code is not pretty, but it works and it works quite fast. It's non-recursive - that is, there's only one pass over the resultset and then one array_filter at the end:

$dbh = new PDO(CONNECT_STRING, USERNAME, PASSWORD);
$dbs = $dbh->query("SELECT n_id, n_parent_id from test_table order by n_parent_id, n_id");
$elems = array();

while(($row = $dbs->fetch(PDO::FETCH_ASSOC)) !== FALSE) {
    $row['children'] = array();
    $vn = "row" . $row['n_id'];
    ${$vn} = $row;
    if(!is_null($row['n_parent_id'])) {
        $vp = "parent" . $row['n_parent_id'];
        if(isset($data[$row['n_parent_id']])) {
            ${$vp} = $data[$row['n_parent_id']];
        }
        else {
            ${$vp} = array('n_id' => $row['n_parent_id'], 'n_parent_id' => null, 'children' => array());
            $data[$row['n_parent_id']] = &${$vp};
        }
        ${$vp}['children'][] = &${$vn};
        $data[$row['n_parent_id']] = ${$vp};
    }
    $data[$row['n_id']] = &${$vn};
}
$dbs->closeCursor();

$result = array_filter($data, function($elem) { return is_null($elem['n_parent_id']); });
print_r($result);

When executed on this data:

mysql> select * from test_table;
+------+-------------+
| n_id | n_parent_id |
+------+-------------+
|    1 |        NULL |
|    2 |        NULL |
|    3 |           1 |
|    4 |           1 |
|    5 |           2 |
|    6 |           2 |
|    7 |           5 |
|    8 |           5 |
+------+-------------+

The last print_r produces this output:

Array
(
    [1] => Array
        (
            [n_id] => 1
            [n_parent_id] => 
            [children] => Array
                (
                    [3] => Array
                        (
                            [n_id] => 3
                            [n_parent_id] => 1
                            [children] => Array
                                (
                                )

                        )

                    [4] => Array
                        (
                            [n_id] => 4
                            [n_parent_id] => 1
                            [children] => Array
                                (
                                )

                        )

                )

        )

    [2] => Array
        (
            [n_id] => 2
            [n_parent_id] => 
            [children] => Array
                (
                    [5] => Array
                        (
                            [n_id] => 5
                            [n_parent_id] => 2
                            [children] => Array
                                (
                                    [7] => Array
                                        (
                                            [n_id] => 7
                                            [n_parent_id] => 5
                                            [children] => Array
                                                (
                                                )

                                        )

                                    [8] => Array
                                        (
                                            [n_id] => 8
                                            [n_parent_id] => 5
                                            [children] => Array
                                                (
                                                )

                                        )

                                )

                        )

                    [6] => Array
                        (
                            [n_id] => 6
                            [n_parent_id] => 2
                            [children] => Array
                                (
                                )

                        )

                )

        )

)

Which is exactly what I was looking for.