Get All Child, Grandchild etc Nodes Under Parent Using PHP with MySQL Query Results

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:

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
        )

)

PHP and MySQL find all children and grandchildren of parents in database

Use the following code to get the data :

function getChildren($parent) {
    $query = "SELECT * FROM tableName WHERE parent_id = $parent";
    $result = mysql_query($query);
    $children = array();
    $i = 0;
    while($row = mysql_fetch_assoc($result)) {
        $children[$i] = array();
        $children[$i]['name'] = $row['name'];
        $children[$i]['children'] = getChildren($row['id']);
    $i++;
    }
return $children;
}

Call this function using

$finalResult = getChildren('*');

EDIT by James

Just to finish this answer, to print out the results into the list:

<?php
    function printList($array = null) {
        if (count($array)) {
            echo "<ul>";

            foreach ($array as $item) {
                echo "<li>";
                echo $item['name'];
                if (count($item['children'])) {
                    printList($item['children']);
                }
                echo "</li>";
            }

            echo "</ul>";
        }
    }

    printList($finalResult);
?>

PHP mysql Tree Child count and List All Child node level wise

It sounds like you just want a way of counting nested sets, I can reproduce your example without using a database with this code:

function returnResults($parent)
{
    switch ($parent) {
        case 'root':
            return ['vijay', 'suresh', 'mukesh'];
        case 'vijay':
            return ['manish', 'rohan', 'manu'];
        case 'manish':
            return ['rinku', 'raja', 'vijay2'];
        default:
            return [];
    }
}

function childCountLevelWise($parent, $level, &$result)
{
    $result[$level] = empty($result[$level]) ? [] : $result[$level]; // init array
    if ($level > 14) {
        return; // ignore levels over 14
    }

    $levelResults = returnResults($parent); // get results for this parent
    $result[$level] = array_merge($result[$level], $levelResults); // add to results for this level
    foreach ($levelResults as $child) {
        childCountLevelWise($child, $level + 1, $result); // check for each child at this level
    }
}

And calling it and printing the results with this code

childCountLevelWise('root', 0, $result);

// print result
foreach ($result as $level => $people) {
    if (!empty($people)) {
        printf('Result for level %d: %s', $level, implode(',', $people));
        echo "\n";
    }
}

Will result in:

Result for level 0: vijay,suresh,mukesh
Result for level 1: manish,rohan,manu
Result for level 2: rinku,raja,vijay2

From there I think it should be simple enough to modify the returnResults function in my example to query the database, although if you're using this on a lot of results you might want to consider the performance costs of this. There are good solutions to having tree structures in the database already, such as the Nested Set in Doctrine.

Create multiple categories with parent, child and grandchild

Finally i found good answer

ref : Parent child mysql

select t0.*,
  concat(
      case coalesce(t4.parent_id, 0) 
        when 0 then ''
        else concat(cast(t4.parent_id as char), '\\')
      end,
      case coalesce(t3.parent_id, 0) 
        when 0 then ''
        else concat(cast(t3.parent_id as char), '\\')
      end,
      case coalesce(t2.parent_id, 0) 
        when 0 then ''
        else concat(cast(t2.parent_id as char), '\\')
      end,
      case coalesce(t1.parent_id, 0) 
        when 0 then ''
        else concat(cast(t1.parent_id as char), '\\')
      end,
      case coalesce(t0.parent_id, 0) 
        when 0 then ''
        else concat(cast(t0.parent_id as char), '\\')
      end,
      cast(t0.id as char)
    ) as path
from mytable t0
    left join mytable t1 on t0.parent_id = t1.Id
    left join mytable t2 on t1.parent_id = t2.Id
    left join mytable t3 on t2.parent_id = t3.Id
    left join mytable t4 on t3.parent_id = t4.Id
order by 
  concat(
      case coalesce(t4.parent_id, 0) 
        when 0 then ''
        else concat(cast(t4.parent_id as char), '\\')
      end,
      case coalesce(t3.parent_id, 0) 
        when 0 then ''
        else concat(cast(t3.parent_id as char), '\\')
      end,
      case coalesce(t2.parent_id, 0) 
        when 0 then ''
        else concat(cast(t2.parent_id as char), '\\')
      end,
      case coalesce(t1.parent_id, 0) 
        when 0 then ''
        else concat(cast(t1.parent_id as char), '\\')
      end,
      case coalesce(t0.parent_id, 0) 
        when 0 then ''
        else concat(cast(t0.parent_id as char), '\\')
      end,
      cast(t0.id as char)
    )

How to get only the first level of depth child nodes?

Here's a solution:

select p1.* from tree_path as p1
left outer join (tree_path as p2 join tree_path as p3 on p2.children = p3.parent)
 on p2.parent = p1.parent 
 and p3.children = p1.children 
 and p2.parent <> p2.children 
 and p3.parent <> p3.children
where p1.parent = 3 and p2.parent is NULL;
+----------+--------+
| children | parent |
+----------+--------+
|        3 |      3 |
|        7 |      3 |
|        8 |      3 |
+----------+--------+

Change the p1.parent=7 and you get this output:

+----------+--------+
| children | parent |
+----------+--------+
|        7 |      7 |
|       15 |      7 |
|       16 |      7 |
+----------+--------+

Here's the way it works: immediate children are descendants where there is a path from parent to child, but there is no path from parent through a third node to the child. So we try to join to such a path (p2->p3) and if none is found, then all columns of p2 and p3 will be NULL.

Get All Child, Grandchild etc Nodes Under Parent Using PHP with MySQL Query Results