build array to represent company Hierarchy structure

Lets say that I have the following definition table structure:

id(int开发者_开发问答),related_to(int),description(text)

and the table contains data similar to:

id   related_to     description
1        0          departmen1
2        1          section1
3        2          devision1
4        0          departmen2
5        4          section2
6        4          etc....

I need a SQL statement or PHP code to represent each Hierarchy in a seperate array element.

The result must be

$data=array(
0=>'department1','section1','devision1'
2=>'departmen2','section2','devision2'
3=> 'departmen1','section3',.......
);

What is the best way to do this?

Thank you.

The best way to work with tree-like structures in PHP is with objects. A good example can be found here.

Add a function to the tree object that returns children in your desired array format, and calls the same function on child objects. With one function call on the top-level object you'll receive an array of all children in the format you need. See Tree Traversal for more information on recursive functions.

In my experience, Nested Sets solve this issue much better. Using recursive functions at the application level wastes a lot of network time, especially if you have a very large structure. While it might be a bit strange to work with, once you have the functions in place to manipulate nested sets, they becomes easy to use.

See: Managing Hierarchical Data In MySQL

Nested sets can handle an infinitely deep tree and can return the entire tree with parent-child relationships in a single query. You can also return a "lineage" with a single query without resorting to recursion or application sorting.

Once you get the data back from the single SQL query, you can easily sort it into a generic tree structure in your PHP application for display or further processing.