views:

158

answers:

2

When retrieving a hierarchical structure from MySQL (table with one ID column and one PARENT column signifying the hierarchical relationships), I map the result into an enumerated array as follows (for this example the numbers are arbitrary):

Array ( [3] => Array ( [7] => Array () ), [7] => Array ( [8] => Array () ) )

Notice 3 is the parent of 7, and 7 is the parent of 8 (this could go on and on; and any parent could have multiple children).

I wanted to shrink this array into a nested multidimensional array as follows:

Array ( [3] => Array ( [7] => Array ( [8] => Array () ) ) )

That is, each NEW id is automatically assigned an empty array. Regardless, any ID's children will be pushed into their parent's array.

Take a look at the following illustration for further clarification:

alt text

This will probably result in a complicated recursive operation, since I always have to check whether a parent with any certain ID already exists (and if so, push the value into its array).

Is there a built-in php function that can assist me with this? Do you have any idea as to how to go about constructing this? For what it's worth I'm using this to built a navigation bar in wordpress (which can contain categories, subcategories, posts... essentially anything).

+1  A: 

This question and it's answers should be helpful to you: turn database result into array.

Be sure to read the PDF presentation by @Bill Karwin, specifically the topics regarding the Closure table.

Alix Axel
+1  A: 

The idea is that you keep an auxiliary array with all the nodes (parent and child) you find. The values of this arrays are references that back your result.

This builds the tree in linear time (array_key_exists does a hash table lookup, which is on average O(1)):

//table contains (id, parent)
$orig = array(
    11 => 8,
    7 => 3,
    8 => 7,
    99 => 8,
    16 => 8,
);

$childrenTable = array();
$result = array();

foreach ($orig as $n => $p) {
    //parent was not seen before, put on root
    if (!array_key_exists($p, $childrenTable)) {
        $childrenTable[$p] = array();
        $result[$p] = &$childrenTable[$p];
    }
    //child was not seen before
    if (!array_key_exists($n, $childrenTable)) {
        $childrenTable[$n] = array();
    }

    //root node has a parent after all, relocate
    if (array_key_exists($n, $result)) {
        unset($result[$n]);
    }

    $childrenTable[$p][$n] = &$childrenTable[$n];
}
unset($childrenTable);

var_dump($result);

gives

array(1) {
  [3]=>
  array(1) {
    [7]=>
    array(1) {
      [8]=>
      array(3) {
        [11]=>
        array(0) {
        }
        [99]=>
        array(0) {
        }
        [16]=>
        array(0) {
        }
      }
    }
  }
}

EDIT: unset $childrenTable in the end to clear reference flags. In practice, you will probably want to do the operation inside a function anyway.

Artefacto
@Artefacto thanks for the effort, I'm dabbling with it now to see if it's indeed bulletproof.
sombe