Get all child, grandchild etc nodes under parent using php with mysql query results

Edit:

I had previously posted a solution to build a multi-dimensional array out of the output you gave as well as a way to get all child elements of a particular id out of that particular array. I have now figured out how to retrieve the child elements straight from your output (without having to first go through a buildtree() function:

function fetch_recursive($src_arr, $currentid, $parentfound = false, $cats = array())
{
    foreach($src_arr as $row)
    {
        if((!$parentfound && $row['id'] == $currentid) || $row['parent_id'] == $currentid)
        {
            $rowdata = array();
            foreach($row as $k => $v)
                $rowdata[$k] = $v;
            $cats[] = $rowdata;
            if($row['parent_id'] == $currentid)
                $cats = array_merge($cats, fetch_recursive($src_arr, $row['id'], true));
        }
    }
    return $cats;
}

To use the function above, simply pass in the output array $data to the first argument and the id you want to retrieve the child elements from in the second argument:

ex.:

$list = fetch_recursive($data, 3);

Which should give you the correct array structure for id 3 (as seen in the example in the last codebox to this answer).

---

Original Answer:

I had never got around to writing a recursive function to build nested trees out of this design until now. I’m sure there are plenty of others who have written similar functions, but this one should definitely work for you:

function buildtree($src_arr, $parent_id = 0, $tree = array())
{
    foreach($src_arr as $idx => $row)
    {
        if($row['parent_id'] == $parent_id)
        {
            foreach($row as $k => $v)
                $tree[$row['id']][$k] = $v;
            unset($src_arr[$idx]);
            $tree[$row['id']]['children'] = buildtree($src_arr, $row['id']);
        }
    }
    ksort($tree);
    return $tree;
}

This function will recursively build a tree out of an adjacency list and keep the id’s ordered in ascending order. This also makes the id‘s of each parent/child the key of each array of information.

This code:

$r = mysql_query("SELECT * FROM test ");
$data = array();
while($row = mysql_fetch_assoc($r)) {
    $data[] = $row;
}
echo '<pre>';
print_r(buildtree($data));
echo '</pre>';

Will output something like this:

Array 
(
    [1] => Array 
    (
        [id] => 1
        [name] => Electronics 
        [parent_id] => 0 
        [children] => Array
        (
            [2] => Array 
            ( 
                [id] => 2
                [name] => Televisions 
                [parent_id] => 1 
                [children] => Array
                (
                    [4] => Array 
                    (
                        [id] => 4
                        [name] => Tube 
                        [parent_id] => 2
                        [children] => Array()
                    )
                    [5] => Array 
                    (
                        [id] => 5
                        [name] => LCD 
                        [parent_id] => 2
                        [children] => Array()
                    )
                    [6] => Array
                    (
                        [id] => 6
                        [name] => Plasma 
                        [parent_id] => 2
                        [children] => Array()
                    )
                )
            )
            [3] => Array 
            (
                [id] => 3
                [name] => Portable Electronics 
                [parent_id] => 1
                [children] => Array
                (
                    [7] => Array
                    (
                        [id] => 7
                        [name] => Mp3 Players 
                        [parent_id] => 3 
                        [children] => Array
                        (
                            [10] => Array
                            (
                                [id] => 10
                                [name] => Flash 
                                [parent_id] => 7
                                [children] => Array()
                            ) 
                        )
                    )
                    [8] => Array 
                    (
                        [id] => 8
                        [name] => CD Players 
                        [parent_id] => 3
                        [children] => Array()
                    )
                    [9] => Array 
                    (
                        [id] => 9
                        [name] => 2 Way Radios 
                        [parent_id] => 3
                        [children] => Array()
                    )
                )
            )
        )
    )
)

To get all child-nodes of a particular id onto a one-dimensional array, you can use this function:

function fetch_recursive($tree, $parent_id, $parentfound = false, $list = array())
{
    foreach($tree as $k => $v)
    {
        if($parentfound || $k == $parent_id)
        {
            $rowdata = array();
            foreach($v as $field => $value)
                if($field != 'children')
                    $rowdata[$field] = $value;
            $list[] = $rowdata;
            if($v['children'])
                $list = array_merge($list, fetch_recursive($v['children'], $parent_id, true));
        }
        elseif($v['children'])
            $list = array_merge($list, fetch_recursive($v['children'], $parent_id));
    }
    return $list;
}

Based on the buildtree() function above, let’s say we wanted to get all child nodes of id 3:

echo '<pre>';
print_r(fetch_recursive(buildtree($a), 3));
echo '</pre>';

This will output:

Array
(
    [0] => Array
        (
            [id] => 3
            [name] => Portable Electronics
            [parent_id] => 1
        )

    [1] => Array
        (
            [id] => 7
            [name] => Mp3 Players
            [parent_id] => 3
        )

    [2] => Array
        (
            [id] => 10
            [name] => Flash
            [parent_id] => 7
        )

    [3] => Array
        (
            [id] => 8
            [name] => CD Players
            [parent_id] => 3
        )

    [4] => Array
        (
            [id] => 9
            [name] => 2 Way Radios
            [parent_id] => 3
        )

)