Ramesh Moorthy Ramesh Moorthy - 2 months ago 27
MySQL Question

How to create recursive function for hierarchy results from PHP array?

I have the following records in "test_group" table, I got the array results from SELECT query.

group_id group_name parent_id
1 fruits 0
2 vegetables 0
3 one seed 1
4 many seed 1
5 seedless 2
6 many seed 2
7 mango 3
8 guava 4
9 jack fruit 4
10 gooseberry 3
11 drumstick 6
12 beans 6
13 onion 5
14 cauli flower 5


Query:

SELECT * FROM test_groups;


Results:

Array (
[0] => Array
(
[group_id] => 1
[group_name] => fruits
[parent_id] => 0
)
[1] => Array
(
[group_id] => 2
[group_name] => vegetables
[parent_id] => 0
)
[2] => Array
(
[group_id] => 3
[group_name] => one seed
[parent_id] => 1
)
[3] => Array
(
[group_id] => 4
[group_name] => many seed
[parent_id] => 1
)
[4] => Array
(
[group_id] => 5
[group_name] => seedless
[parent_id] => 2
)
[5] => Array
(
[group_id] => 6
[group_name] => many seed
[parent_id] => 2
)
[6] => Array
(
[group_id] => 7
[group_name] => mango
[parent_id] => 3
)
[7] => Array
(
[group_id] => 8
[group_name] => guava
[parent_id] => 4
)
[8] => Array
(
[group_id] => 9
[group_name] => jack fruit
[parent_id] => 4
)
[9] => Array
(
[group_id] => 10
[group_name] => gooseberry
[parent_id] => 3
)
[10] => Array
(
[group_id] => 11
[group_name] => drumstick
[parent_id] => 6
)
[11] => Array
(
[group_id] => 12
[group_name] => beans
[parent_id] => 6
)
[12] => Array
(
[group_id] => 13
[group_name] => onion
[parent_id] => 5
)
[13] => Array
(
[group_id] => 14
[group_name] => cauli flower
[parent_id] => 5
)

)


I want the following output from these results. How to frame the hierarchy levels with these results set? Please help me and thanks for advance.

<ul>
<li>Fruits</li>
<ul>
<li>one seed</li>
<ul>
<li>mango</li>
<li>gooseberry</li>
</ul>
<li>many seed</li>
<ul>
<li>guava</li>
<li>jack fruit</li>
</ul>
</ul>
<li>Vegetables</li>
<ul>
<li>seedless</li>
<ul>
<li>onion</li>
<li>cauli flower</li>
</ul>
<li>many seed</li>
<ul>
<li>drumstick</li>
<li>beans</li>
</ul>
</ul>
</ul>

Answer

You can build list using recursion or without:

Using Recursion:

function array_to_tree_recursive(array $array, $parent_id = 0)
{
    $return = array();

    foreach ($array as $k => $v) {
        if ($v['parent_id'] == $parent_id) {
            $return[$k] = $v; 
            $return[$k]['children'] = array_to_tree_recursive($array, $v['group_id']);  
        }
    }

    return $return;
}

Without Recursion:

function array_to_tree(array $array, $parent_id = 0)
{
    $array = array_combine(array_column($array, 'group_id'), array_values($array));

    foreach ($array as $k => &$v) {
        if (isset($array[$v['parent_id']])) {
            $array[$v['parent_id']]['children'][$k] = &$v;
        }
        unset($v);
    }

    return array_filter($array, function($v) use ($parent_id) {
        return $v['parent_id'] == $parent_id;
    });
}

Convert tree to list:

function tree_to_list(array $tree) {
    $return = "";

    foreach ($tree as $branch) {
        $return .= "\n<li>".$branch['group_name']."</li>";
        if (isset($branch['children']) && sizeof($branch['children'])) {
            $return .= tree_to_list($branch['children']);
        }
    }

    return "\n<ul>{$return}</ul>\n";
}

Usage:

echo tree_to_list(array_to_tree($a));

Output:

<ul>
<li>fruits</li>
<ul>
<li>one seed</li>
<ul>
<li>mango</li>
<li>gooseberry</li></ul>

<li>many seed</li>
<ul>
<li>guava</li>
<li>jack fruit</li></ul>
</ul>

<li>vegetables</li>
<ul>
<li>seedless</li>
<ul>
<li>onion</li>
<li>cauli flower</li></ul>

<li>many seed</li>
<ul>
<li>drumstick</li>
<li>beans</li></ul>
</ul>
</ul>