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

Question

I have a tree like

and I want the output for requeted ID for example Admin

my table structure is

I have a method that returns Child count level wise but I also want to return the child list level-wise with child count level-wise like 2nd image output required.

function childCountLevelWise($conn,$ID, $level){
if ($level>14){
    $count = array(0=>0); 
    return $count;
}
$sql="select * from user_my_tree t where t.parent_ID=".$ID;
$result=returnResults($conn,$sql);
if ($result==null){
    $count = array(0=>0); 
}
else{
    $count = array(0=>0);
    foreach($result as $key=>$row)
    {   
        $count[0]++;
        $children=childCountLevelWise($conn,$row['ID'], $level+1);
        $index=1;
        foreach ($children as $child)
        {
            if ($child==0)
                continue;
            if (isset($count[$index]))
                $count[$index] += $child;
            else    
                $count[$index] = $child;
                $index++;
        }    
    }    
}
return $count;

}

Provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example) by adding your structure and data of your database table `user_my_tree` to make it more likely someone answers your question. — Definitely not Rafal, Apr 26 '21 at 16:49
The expected result does not seem correct: what should it look like if multiple nodes have sub-items ? I think you might find answers on this question: [How to create a MySQL hierarchical recursive query?](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) — AymDev, May 03 '21 at 09:46

score 1 · Accepted Answer · answered May 03 '21 at 08:46

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.

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

1 Answers1