Solved

Need to build a tree (organization chart) from the bottom-up using PHP and Mysql

Posted on 2008-10-28
4
511 Views
Last Modified: 2013-12-13
Hello Experts,

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

Example Table:
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

Example Tree:

Joe Johnson
|- 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
Reports:
Jane Smith
John Smith
Bill Jones

How do I build the tree so that I can find a person and all the people that report to them.

Thanks!
0
Comment
Question by:Gridcaster
  • 2
  • 2
4 Comments
 
LVL 5

Accepted Solution

by:
ionutz2k2 earned 500 total points
ID: 22822463
i think something like this should do the trick:

function getLowerRanks($id,$level)
{
      $response = '';
      $result = mysql_query("select * from table where unique_manager_id=".$id);
      while ($row = mysql_fetch_array($result))
      {
            $response .= '<tr><td>';
            for ($i = 0; $i < ($level-1); $i++) $response .= '&nbsp;&nbsp;';
            $response .= '|-'.$row['UserName'];
            $response .= '</td></tr>';
            $response .= getLowerRanks($row['unique_user_id'],$level+1);
      }
      return $response;      
}      

$tree = '<table cellpadding="2" cellspacing="0" border="0">';
$result = mysql_query("select * from table where isnull(unique_manager_id)");
while ($row = mysql_fetch_array($result))
{
      $tree .= '<tr><td>';
      $tree .= $row['UserName'];
      $tree .= '</td></tr>';
      $tree .= getLowerRanks($row['unique_user_id'],1);
}
$tree .= '</table>';

i haven't got a chance to test it cause i'm at work, but hope it works for you.
0
 

Author Comment

by:Gridcaster
ID: 22823317
ionutz2k2:

It works perfectly for my purposes.  The only other thing that I needed (to build from a specific employee) I figured out (and am posting for future reference).  In order to see the direct reports (without building all the way from the top), I used the following sql:

select * from table where unique_employee_id=#

So the code looks like this:

function getLowerRanks($id,$level)
{
      $response = '';
      $result = mysql_query("select * from table where unique_manager_id=".$id);
      while ($row = mysql_fetch_array($result))
      {
            $response .= '<tr><td>';
            for ($i = 0; $i < ($level-1); $i++) $response .= '&nbsp;&nbsp;';
            $response .= '|-'.$row['UserName'];
            $response .= '</td></tr>';
            $response .= getLowerRanks($row['unique_user_id'],$level+1);
      }
      return $response;      
}      

$tree = '<table cellpadding="2" cellspacing="0" border="0">';

// Build from the top
//$result = mysql_query("select * from table where isnull(unique_manager_id)");

//Build from a specific Employee
$result = mysql_query("select * from table where unique_employee_id=#");

while ($row = mysql_fetch_array($result))
{
      $tree .= '<tr><td>';
      $tree .= $row['UserName'];
      $tree .= '</td></tr>';
      $tree .= getLowerRanks($row['unique_user_id'],1);
}
$tree .= '</table>';


0
 

Author Closing Comment

by:Gridcaster
ID: 31510744
Thanks so much for the quick response!!
0
 
LVL 5

Expert Comment

by:ionutz2k2
ID: 22824101
i'm glad i could help you. :)
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question