Solved

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

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

707 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

15 Experts available now in Live!

Get 1:1 Help Now