Solved

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

Posted on 2008-10-28
4
510 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
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 dynamically set the form action using jQuery.

895 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now