Array "tree" creation from db table

Question

My php function

function generateMenu($parent, $level, $menu, $utype) {
    global $db;
    $tree = array();
    $stmt = $db->prepare("select id, parent, name FROM navigation WHERE menu=? AND user_type=?") or die($db->error);
    $stmt->bind_param("ii", $menu, $utype) or die($stmt->error);
    $stmt->execute() or die($stmt->error);
    $stmt->store_result();
    $meta = $stmt->result_metadata();
    $stmt->bind_result($id, $parent, $name);
    while ($stmt->fetch()) {
        $arr[$id] = array(
            'name' => $name, 
            'parent' => $parent
        );
        if (!array_key_exists($parent,$arr) and $parent != 0) {
            $arr[$parent][$id] = $id;
        }
    }
    $stmt->close();
}

generates following array from db table. [1], [2] ... - are ids of li item

Array (
    [1] => Array ( 
        [name] => Parent1
        [parent] => 0
    ) 
    [2] => Array ( 
        [name] => Parent2
        [parent] => 0 
    )
    [3] => Array (
        [name] => Parent3 
        [parent] => 0 
    )
    [4] => Array ( 
        [name] => Child1 of P1
        [parent] => 1
    ) 
    [5] => Array (
        [name] => Child2 of P1
        [parent] => 1
    ) 
)

What I want to do is to create the menu like that

<ul>
  <li><a href="?page=1">Parent1</a>
    <ul>
      <li><a href="?page=4">Child1 of P1</a></li>
        ...

Second function is for generating menu from this array. But I know that before sending this array into second function I need to convert it into multidimensional tree array. I can't figure out how to do it.

Here is second function

function olLiTree($tree) {
    $out = '<ul>';

    foreach($tree as $key => $value) {
        $out.= '<li>';

        if (is_array($value)) {
            $out.= $key . olLiTree($value);
        } else {
            $out.= $value;
        }

        $out.= '</li>';
    }

    $out.= '</ul>';

    return $out;
}

Db structure

possible duplicate of How can I convert a series of parent-child relationships into a hierarchical tree? — jeroen
– jeroen, Commented Nov 24, 2011 at 22:48
By the way, I'd go for the first answer, using recursion, and two separate functions, one to generate a multi-dimensional array (always useful) and one to generate your html. — jeroen
– jeroen, Commented Nov 24, 2011 at 22:55

Community · Accepted Answer · 2017-05-23 11:55:16Z

The function array_key_exists() needs two parameters, you only passes one. I think you mean:

while(list($id, $parent, $name) = mysql_fetch_assoc($results)) {
    $tree[$id] = array(
        'name' => $name, 
        'children' => array(), 
        'parent' => $parent
    );
    if (!array_key_exists($parent,$tree)) {
        $tree[$parent]['children'][$id] = $id;
    }
}

You changed your answer, so I think your problem are not with array_keys_exists. Anyway you can try with this way to obtain the data with MaxDB:

function generateMenu($parent, $level, $menu, $utype) {
    global $db;
    $tree = array();
    $stmt = $db->prepare("select id, parent, name FROM navigation WHERE menu=? AND user_type=?") or die($db->error);
    $stmt->bind_param("ii", $menu, $utype) or die($stmt->error);
    $stmt->execute() or die($stmt->error);
    $stmt->store_result();
    $meta = $stmt->result_metadata();

    $stmt->bind_result($id, $parent, $name);
    while ($stmt->fetch()) {
        $tree[$id] = array(
            'name' => $name, 
            'children' => array(), 
            'parent' => $parent
        );
        if (!array_key_exists($parent,$tree)) {
            $tree[$parent]['children'][$id] = $id;
        }
    }
    $stmt->close();
    print_r($tree);
}

For your second function I think @jeroen has rigth, in this anwser is what you need.

Jonathon Reinhart · Accepted Answer · 2011-11-22 01:32:57Z

2

You only passed one parameter to array_key_exists(). I think you may want to use isset() to see if that element exists in the array.

array_key_exists($id, $tree[$parent]['children']);

Should be roughly equivalent to:

isset($tree[$parent]['children'][$id]);

answered Nov 22, 2011 at 1:32

Jonathon Reinhart

138k38 gold badges266 silver badges347 bronze badges

2 Comments

Tural Ali Over a year ago

I know, but how can I modify this function to get it work, that is not my function that's why asking

Tural Ali Over a year ago

Getting Undefined offset: 0,1,2 on line list($id, $parent, $name) = $results; errors

Community · Accepted Answer · 2017-05-23 10:32:34Z

0

I posted code to convert an array like yours into a multi-dimensional array in my answer to:

Convert flat array to the multi-dimentional.

The tricky part is understanding PHP references. I'm still not sure I understand them, but the code I posted is the only way it would work.

edited May 23, 2017 at 10:32

CommunityBot

11 silver badge

answered Nov 24, 2011 at 22:41

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

1 Comment

Tural Ali Over a year ago

I have already taken a look at your code. There 2 problems: 1) My array structure is a bit different 2) You said "algorithm works only if parents appear up in the db result set before their children appear." Please help me to get it works. I don't understand generation of tree part

Collectives™ on Stack Overflow

Array "tree" creation from db table

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related