• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 546
  • Last Modified:

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

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
Gridcaster
Asked:
Gridcaster
  • 2
  • 2
1 Solution
 
ionutz2k2Commented:
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
 
GridcasterAuthor Commented:
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
 
GridcasterAuthor Commented:
Thanks so much for the quick response!!
0
 
ionutz2k2Commented:
i'm glad i could help you. :)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now