RecursiveArrayIterator from MySQL table

Question

I am looking for an example how a recursive array passed as parameter for RecursiveArrayIterator should be structured. I have a mysql table with id, parent_id and title:

id    parent_id   title
-----------------|-------------------
1         0      | Item 1
2         1      | Item 2
3         1      | Item 3
4         3      | Item 4
5         4      | Item 5

I would like to create an array from the table, that can be passed to RecursiveArrayIterator to build trees, menus, options and so on. I have tried this "build_tree" function https://stackoverflow.com/a/8587437/1746522 to create an array from mysql table. The resulted array looks like:

array (size=2)
  0 => 
    array (size=3)
      'id' => string '1' (length=1)
      'title' => string 'Item 1' (length=6)
      'parent_id' => string '0' (length=1)
      'children' => 
        array (size=2)
          0 => 
            array (size=3)
              ...
          1 => 
            array (size=3)
              ...

But when I pass the resulted array to RecursiveArrayIterator it looks strange. E.g. using this function:

$array = new RecursiveArrayIterator($tree);
$iterater = new RecursiveIteratorIterator($array);  
foreach ($iterater as $key => $value) {
    $d = $iterater->getDepth();            
    echo "depth=$d k=$key v=$value\n";
}

I have the strange and unusable output like:

depth=0 k=0 v=Array
depth=1 k=id v=1
depth=1 k=title v=Item 1
depth=1 k=parent_id v=0
depth=2 k=0 v=Array
depth=3 k=id v=2
depth=3 k=title v=Item 2
depth=3 k=parent_id v=1
depth=4 k=0 v=Array
....

The depth is wrong, and it iterates through single values and not elements. Probably is the array passed to RecursiveArrayIterator is "malformed" and should be structured other way?

I have expected to see something like this:

depth=0 k=1 v=Array
depth=1 k=2 v=Array
depth=1 k=3 v=Array

where Array contains all values to the node, like parent_id and title and k is the id from the mysql column id.

Answer 1

By default, the RecursiveIteratorIterator class will only show you the 'leaves' of the tree. That is, everything in the tree that isn't an array.

You want to initialize the iterator to show the array nodes as well as the leaves. Then you want to print out data only for those nodes which are arrays.

$array = new RecursiveArrayIterator($records);
$iterater = new RecursiveIteratorIterator($array, RecursiveIteratorIterator::SELF_FIRST);  
foreach ($iterater as $key => $value) {
    if (is_array($value)) {
        $d = $iterater->getDepth(); 
        echo "depth=$d k=$key value=$value\n";
    }
}

> depth=0 k=0 value=Array
> depth=0 k=1 value=Array
> depth=1 k=children value=Array
> depth=2 k=0 value=Array
> depth=2 k=1 value=Array

At this point, you're going to notice there's an extra layer called 'children' which messes up your depth count. This happens because the descendants of a node are put into their own array. You can skip printing those lines out, and emit more data about the items in the array like so:

foreach ($iterater as $key => $value) {
    if (is_array($value) && $key !== 'children') {
        $d = $iterater->getDepth(); 
        echo "depth=$d k=$key title={$value['title']}\n";
    }
}

Answer 2

My final solution with unlimited depth, only one query, all information from the table and access to all functions of RecursiveIteratorIterator like children and depth. I use two arrays in my solution.

Flat array

select * from table

and create flat array as usual:

$info[1] = array('parent_id' => 0, 'title' => 'Item 1');
$info[2] = array('parent_id' => 1, 'title' => 'Item 2');
$info[3] = array('parent_id' => 1, 'title' => 'Item 3');
$info[4] = array('parent_id' => 3, 'title' => 'Item 4');
$info[5] = array('parent_id' => 4, 'title' => 'Item 5');

use id column as array index.

Nested Array

Create nested array from $info that contains ONLY ids of the items:

function tree_structure($info, $parent = 0) {    
    foreach ($info as $row) {
        if ($row['parent_id'] == $parent)
            $struc[$row['id']] = tree_structure($info, $row['id']);
    }    
    return $struc;        
}

This array can be passed to RecursiveArrayIterator.

You have two arrays now:

• $info - (flat) contains all information about your nodes
• $struc - (recursive) contains the structure of the nodes

RecursiveArrayIterator

Start to work with RecursiveArrayIterator

    $array = new RecursiveArrayIterator($struc);
    $iterator = new RecursiveIteratorIterator($array, TRUE);
    $iterator->rewind();

    while ($iterator->valid()) {
        // Get the id
        $iterator->key();
        // Get the depth
        $iterator->getDepth();
        // Check if it has children
        $iterator->hasChildren();
        // Get the number of children
        sizeof($iterator->callGetChildren());
        // Get all information for the node from the flat array
        $info[$iterator->key()];

        $iterator->next();
    }

The line $info[$iterator->key()] in the code does the actual trick. With this line you have all information about your node without messing the structure.