database tree to multidimensional array

Question

Hi,

i have a simple database tree with parentid and i want to read the db and get an array like above

Array
(
 Title: Category 1
 Children => Array
             (
              => Title: Category 1.1

              => Title: Category 1.2
                     Children =>  Array
                               (
                                => Title: Category 1.2.1

                               )
              ) 

)

I try to implement with above code

    function getTree($rootid)
    {
       $result = =mysql_query("select * from tree where parentid='$rootid'");
       while ($row = mysql_fetch_array($result)) { 

        $arr[]=$row["Title"];
        getChilds($row["id"]);

      }

    }


   function getChilds($id)
    {
       $result = =mysql_query("select * from tree where parentid='$id'");
       while ($row = mysql_fetch_array($result)) { 

        //childers nodes here
        $arr[]=$row["Title"];

        getChilds($row["id"]);

      }

    }

}

I have a problem on how to pass the array to recursion function so continue children from the last node i wrote and so on.

Its implement inside a class and i know i have to pass as & $arr but i am not sure how

Any help appreciated

Thanks

Answer 1

Try something like this:

<?php
function getTree($rootid)
{
   $arr = array();

   $result = mysql_query("select * from tree where parentid='$rootid'");
   while ($row = mysql_fetch_array($result)) { 
     $arr[] = array(
       "Title" => $row["Title"],
       "Children" => getTree($row["id"])
     );
   }
   return $arr;
}
?>

Answer 2

As pointed out over here, using the adjacency list model is not a good approach. You'd better use preorder tree traversal.

Why? Look, most of the time (read: in 99.99% of the cases), the bottleneck of your application WILL be the database. Preorder tree traversal indeed looks more complicated (mainly because it's not modeled after your data in its "natural" state, recursively), but you only need to send a query once and you're done. Beside that, in 99% of the use cases, your application will only display the tree, not modify/reorder it.