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

Question

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>

MySQL doesn't support recursive functions, so it is not well suited to this (adjacency-list) model of storing hierarchical data. If possible, you should consider adopting a more SQL-friendly model (such as nested sets or a transitive closure). — eggyal
– eggyal, Commented Jan 31, 2014 at 9:06
I want the results from recursive function without sub query, Is it possible in PHP array? — Ramesh Moorthy
– Ramesh Moorthy, Commented Jan 31, 2014 at 9:23
perhaps if you create your array in a different way? two dimensional... one for parent_id and for each parent, an inner array holding the children — andrew
– andrew, Commented Jan 31, 2014 at 9:26

Oliver Tappin · Accepted Answer · 2016-09-12 13:53:46Z

3

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>

edited Sep 12, 2016 at 13:53

Oliver Tappin

2,5611 gold badge25 silver badges43 bronze badges

answered Feb 4, 2014 at 1:18

b.b3rn4rd

8,8602 gold badges50 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ramesh Moorthy Over a year ago

thank you b.b3rn4rd, I got it. your code is very optimized and simple.

Mohd Abdul Mujib Over a year ago

shouldnt the 8th line in the first code block be named "array_to_tree_recursive" instead of "array_to_tree" ?

RedScourge Over a year ago

The non-recursive one doesn't seem to work if you add another layer of depth, which I needed for my situation, but the recursive one worked just fine, so thanks!

Mihai Stancu · Accepted Answer · 2014-01-31 13:43:58Z

1

First we create some basic tree management functions:

/* in a separate file for your custom functions: */
function createTreeFromList($list) {
    $tree = array();
    foreach($list AS $item) {
        if(isset($tree[$item['group_id']])) {
            array_merge($tree[$item['group_id']], $item);
        }
        else {
            $tree[$item['group_id']] = $item;
        }
        if(!isset($tree[$item['parent_id']]) {
            $tree[$item['parent_id']] = array();
            $tree[$item['parent_id']]['children'] = array();
        }
        $tree[$item['parent_id']]['children'] []= &$item;
    }
    return($tree);
}

function printTree($tree) {
    echo("<ul>");
    foreach($tree AS $node) {
        echo("<li>".$node['group_name']."</li>");
        if(!empty($node['children'])) {
            echo("<li>");
            printTree($node['children']);
            echo("</li>");
        }
    }
    echo("</ul>");
}

The we use them:

/* in our current file */

/* gain access to the functions we've defined */
include('path/to/our/functions_file.php');

/* select all rows in the tree: */
$sql = "SELECT * ...";
$rows = $pdo->query($sql)->fetchAll();

/* iterate over the list in order to create an equivalent tree: */
$tree = createTreeFromList($rows);

/* Then we print the tree: */
printTree($tree);

edited Jan 31, 2014 at 13:43

answered Jan 31, 2014 at 9:31

Mihai Stancu

16.2k2 gold badges35 silver badges51 bronze badges

10 Comments

Mihai Stancu Over a year ago

Please test & tweak the code yourself, I just wrote it off the top of my head and didn't test it.

Ramesh Moorthy Over a year ago

Hi Mihai, I tried, but it won't work. Can you explain how to frame it?

Yang Over a year ago

@RameshMoorthy Follow this: salman-w.blogspot.com/2012/08/…

Mihai Stancu Over a year ago

What do you mean by "frame"?

Ramesh Moorthy Over a year ago

Hi Mihai, How to create it? If possible, Can you explain the step by step? Because I tried but it won't work for me.

|

Sagar Patni · Accepted Answer · 2014-01-31 09:29:13Z

0

$result = query;
for($i=0 to end)
    find_child($i); 


function find_child($gid){
    echo "<ul>";
    for($i=0 to end){
        if($result[$i].parent_id == gid){
            echo "<li>"+$result+"</li>"
            find_child($result[i].group_id);
        }
    }
    echo "</ul>";
}

answered Jan 31, 2014 at 9:29

Sagar Patni

3122 silver badges11 bronze badges

2 Comments

Mihai Stancu Over a year ago

This will iterate over the result list many many times. I think the complexity is O(N!) or O(N!-N). In any case very inefficient. And in case you need to do this in several places in your page you'll have to compute it every time.

Mihai Stancu Over a year ago

In my answer I only have to iterate through the list once to create a hierarchical tree based on the initial list (which is O(N)). After that you can walk the tree to print it in the desired format (another O(N)). So let's say a total of O(2N) for creating & printing a tree.

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

10 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

10 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related