PHP dynamic multidimensional array or objects

Question

Hi,

I'm trying to create a recursive function (or method) that stores a sub-tiered navigation in an array variable or object. Here is what I have:

class Navigation extends Database
{

    function build($parent_id = 0)
    {
     $query = 'SELECT id, name, href, parent_id 
     FROM navigation
     WHERE parent_id = '.$parent_id.' 
     ORDER BY name';
     $results = $db->query($query);
     while ($row = $results->fetch_object()) {
      $nav[$row->id] = $row;
      // echo $row;
      $this->build($row->id);
     }
     return $nav;
    }
}

If you comment out the echo $row everything works fine. So what I want it to do in a three tier navigation is this:

Array
(
    [1] => stdClass Object
    (
     [id] => 1
     [name] => Home
     [href] => home.php
     [parent_id] => 0
    )
    [2] => stdClass Object
    (
     [id] => 2
     [name] => Company
     [href] => company.php
     [parent_id] => 0
    )
     [4] => stdClass Object
     (
      [id] => 4
      [name] => Company Vision
      [href] => company_vision.php
      [parent_id] => 2
     )
     [5] => stdClass Object
     (
      [id] => 5
      [name] => Company Goals
      [href] => company_goals.php
      [parent_id] => 2
     )
    [3] => stdClass Object
    (
     [id] => 3
     [name] => Products
     [href] => products.php
     [parent_id] => 0
    )
     [6] => stdClass Object
     (
      [id] => 6
      [name] => Products Shoes
      [href] => products_shoes.php
      [parent_id] => 3
     )
      [7] => stdClass Object
      (
       [id] => 7
       [name] => Nike
       [href] => products_shoes_nike.php
       [parent_id] => 6
      )

)

Just as an example, so the array would dynamically do this:

$nav[$row->id] = $row; // Home
$nav[$row->id] = $row; // Company
$nav[2][$row->id] = $row; // Company Vision
$nav[2][$row->id] = $row; // Company Goals
$nav[$row->id] = $row; // Products
$nav[3][$row->id] = $row; // Products Shoes
$nav[3][6][$row->id] = $row; // Products Shoes Nike

Thanks in advance.

Question: How do you make a recursive function/method and store the recursive information in a variable rather than echoing the results?

Issues: (a) PHP overwrites the variable every time it calls itself recursively (b) A solution would be dynamically creating an array on the fly, but I don't know if that is possible

Answer 1

I suspect you need the nested sets algorithm http://dev.mysql.com/tech-resources/articles/hierarchical-data.html