PHP Recursion with Associative Array (Menu structure)

Question

I've been banging my head against a wall today. I have a mysql table that has a list of menu items which have a recursive parent/child relationship. I need to create an associative array that matches it.

Here is my code, and below it is an explanation of my problem.

MySQL Database

CREATE TABLE IF NOT EXISTS `menus` (
  `sectionid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `parentid` int(10) unsigned NOT NULL DEFAULT '0',
  `name` varchar(128) NOT NULL DEFAULT '',
  PRIMARY KEY (`sectionid`)
);

INSERT INTO `menus` (`sectionid`, `parentid`, `name`) VALUES
(1, 0,'DOWNLOADS'),(4, 1,'Player Scenarios'),
(5, 1,'Co Op Scenarios'),(9, 1,'MAPS');

PHP CODE

function get_db_children($parentid) {

  $mysqli = mysqli_open();
  $sqlStr = "select sectionid as childid from menus where parentid=$parentid order by sectionid";
  $result = $mysqli->query($sqlStr);
  $mysqli->close();

  return $result;

}

function get_children(&$node) {

  foreach ($node as $key=>$value) {
    $result = get_db_children($key);
    while ($row = $result->fetch_assoc()) {
       $tmp = array($row['childid'] => array());
       $node[$key][$row['childid']]= get_children($tmp);
    }
  }
  return $node;
}

===========================================

The above functions are called from my main script as follows:

$categories[0] = array ();
get_children($categories);
print "Categories=".print_r($categories);
exit;

==============

My PROBLEM.

My problem is that the structure of the returned array is not QUITE how I want it.

The code above returns:

Categories=
Array ( [0] => Array 
        ( [1] => Array 
          ( [1] => Array 
            ( [4] => Array 
              ( [4] => Array ( ) )
              [5] => Array 
              ( [5] => Array ( ) )
              [9] => Array 
              ( [9] => Array ( ) )
            )
          )
        )
      )

What I want is:

Categories=
Array ( [0] => Array 
          ( [1] => Array 
            (
              ( [4] => Array ( ) )
              ( [5] => Array ( ) )
              ( [9] => Array ( ) )
            )
          )
      )

A null array indicates no children.

I can't figure out how to get rid of the double up on key values. :(

If anyone can help, would be much appreciated.

Cheers, Nap

gone · Accepted Answer · 2013-02-17T02:14:17.557

Changing the approach a little has fixed my problem. Instead of passing the $categories array by reference, I just pass the ID of the parent menu item and return the array.

Code below uses same SQL data and SQL accessor function get_db_children($parentid) as given in my OP. I'm sure this could be improved upon, but at least I've got my result.

@Josh, thnx for your input.

PHP Functions

function get_twigs($nodeid) {

  $result = get_db_children($nodeid);
  if ($result->num_rows != 0) {
    while ($row = $result->fetch_assoc()) {
      $node[$row['childid']] = array();
      $node[$row['childid']] = get_twigs($row['childid']);
    }
  } else {
    $node=NULL; // or any other 'no children below me indicator.
  }
  return $node;
}

function get_branch($nodeid) {

  $node[$nodeid] = array();
  $node[$nodeid] = get_twigs($nodeid);
  return $node;
}

PHP Caller

$categories = get_branch(0);

Finally got it going.

:)

if you opted for changing the code, i'd say take a bit further and get it to one query. do the tree logic in a loop — Josh, Feb 16 '13 at 15:27

Josh · Answer 2 · 2013-02-16T11:35:29.270

0

you are passing in the $node by reference therefore instead of doing $node[$key][$row['childid']] = get_children($tmp); you should really be doing

$value[$row['childid']] = get_children($tmp)

or

$node[$row['childid']] = get_children($temp)

but if you go with the second option you will have to pass in $categories[0] on the first call to get_children (which is what you are doing when you are making the recurring calls)

update:

ill try and explain the reason for that...

as you can see the first entry (0) is not duplicated. only after the first recurring call is where your problem starts, the reason for that is because you are passing in the child array by reference therefore the second time around [as a example] when you call get_children the $node var actually already refers to the nested part

array(
   0=>array(
      1=>array(/*this is what $node actually is the second time around*/
         /*when you say $node[$key][$row['childid']] what you are actually saying is assign the value to array[0][1][1][4] = array())*/
      )
   )
);

i hope this helps. if i can think of a way to explain it batter ill come back and update...

edited Feb 16 '13 at 11:35

answered Feb 16 '13 at 10:15

Josh

3,264
1
23
35

It's a PHP thing, nothing to do with the sql. The order by only ensures the sequence and doesn't double up the rows. :( – gone Feb 16 '13 at 10:21
"where parentid=$parentid" ensures that returned dataset only contains those entries which have the parentid I'm looking for. Therefore grouping by parentid will have no effect anyway. – gone Feb 16 '13 at 10:25
I believe the problem is in the get_children() function in the line: " $node[$key][$row['childid']]= get_children($tmp);" – gone Feb 16 '13 at 10:31
correct you just got to take out the $key part from that line since you are already referring to the child array because you are doing nested calls. ill try to update my answer to explain that batter – Josh Feb 16 '13 at 11:02
thnx 4 taking the time, but I can't get your suggestions to work. I had already tried the 2nd method except I'm not sure how to pass `$categories[0]` any other way than how I've done so. And the first method: `$value[$row['childid']]` introduces a new var `$value`... which is an orphan. If you're up for it, please elaborate. Thnx for the heads up on var_dump :) – gone Feb 16 '13 at 13:49

PHP Recursion with Associative Array (Menu structure)

MySQL Database

PHP CODE

2 Answers2

update: