database tree to multidimensional array

Question

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

Matthew · Accepted Answer · 2010-02-01 17:50:37Z

4

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;
}
?>

answered Feb 1, 2010 at 17:50

Matthew

48.5k11 gold badges90 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Amee Over a year ago

What if we have depth more than 10.

Flavius · Accepted Answer · 2010-02-01 17:57:02Z

3

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.

edited Feb 1, 2010 at 17:57

answered Feb 1, 2010 at 17:50

Flavius

13.8k13 gold badges84 silver badges129 bronze badges

2 Comments

ntan Over a year ago

i have implement the tree traversal but its really painful on repositioning and move so i am not really sure if i wan to use it.Thanks

Eric Butera Over a year ago

I can only agree with Flavius here. I have used the MPTT ever since I found out about it. I only use it for reads though. For adding, moving, or deleting nodes out of the tree I regenerate it using the normal id, parentid recursion method. This way I get the benefits of both worlds with minimal effort.

Collectives™ on Stack Overflow

database tree to multidimensional array

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related