Need to build a tree (organization chart) from the bottom-up using PHP and Mysql
Posted on 2008-10-28
I need to build a hierarchical tree from the bottom up using PHP and Mysql in order to be able to find people in an organization.
Several concerns are:
1.) I don't know how many levels down the tree will go (In looking at the data, I'm guessing no more than 12)
2.) I need to be able to select any point (person) in the tree and be able to see all the child nodes all the way to the bottom (e.g. If I select the Director of WibblyWidgets Department, I need to see everyone who reports to her.)
3.) The data has the person and their direct manager.
4.) I need the algorithim/code to be fairly efficient (e.g. to look up a point and all the children in a couple of seconds at most).
Assuming that the data is in a table that looks like this:
unique_user_id, User Name, unique_manager_id, Manager Name
unique_user_id | User Name | unique_manager_id | Manager Name
1 | John Smith | 2 | Jane Smith
2 | Jane Smith | 3 | Joe Johnson
3 | Joe Johnson | <no data> | <no data >
4 | Bill Jones | 2 | Jane Smith
|- Jane Smith
|- John Smith
|- Bill Jones
The output will be to an HTML page that will look something like this (if I selected Joe Johnson from the example above):
Manager: Joe Johnson
How do I build the tree so that I can find a person and all the people that report to them.