Solved

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

Posted on 2008-10-28
4
522 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
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…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

705 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