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        ))

Here is a way to get you further, and you can decide how you build your result array and what fields you choose to include. This is not tested, but you should see the logic.

// connect to db// set id counter$ids = 0;// declare array$categories = new Array();// determine max ids$query = mysql_query("SELECT COUNT(1) AS ids FROM test");$result = mysql_fetch_array(query); // get result$count = $result['ids'];// loop through ids for parentsfor($ids = 0; $ids <= $count; $ids++) {  $query1 = mysql_query("SELECT * FROM test WHERE id = '" . $ids . "'");  $query2 = mysql_query("SELECT id, name, parent_id FROM test WHERE parent_id = '" . $ids . "'");  // check if has children  if(mysql_num_rows($query2) > 0) {    // iterate through children and add to array    while (mysql_fetch_array($query2) as $row) {      $categories[$ids]['child'][$row['id']] = $row['name'];    }  }  // check if has siblings  if(mysql_num_rows($query1) > 0) {    // iterate through children and add to array    while (mysql_fetch_array($query2) as $row) {      $categories[$ids]['sibling'][$row['id']] = $row['name'];    }  }}