Forum Moderators: coopster
**********************************
ID,name,supervisorID
1,Adam,NULL
2,Bob,1
3,Brian,1
4,Charlie,2
5,Chris,3
**********************************
I would like to obtain the number of supervisors for each employee so I can model something like this:
**********************************
Adam - Boss
Bob - 1 Supervisor (Adam)
Brian - 1 Supervisor (Adam)
Charlie - 2 Supervisors (Bob --> Adam)
Chris - 2 Supervisors (Brian --> Adam)
**********************************
How can I get the SQL/PHP to do this? I think that the supervisorID needs to be piped into the ID but I'm not sure. I have managed to extract the immediate supervisor, but I cannot get the whole list of them. There is a Java program that can do this I believe (http://www.faqs.org/docs/javap/c11/s2.html), but I need it in SQL/PHP. Any help would be appreciated.
You can accomplish this by using a while loop [php.net] to recursively look up each employee's supervisor. Here's some pseudocode to give you an idea of what I mean:
/* look up employee */
$supervisors = array();
if( /* employee has a supervisor */ ) {
while( (isset($supervisorID)) && $supervisorID != null){
/* lookup supervisor */
$supervisors[] = array('name' => $name, 'id' => $ID);
}
}
$supervisors = array_reverse($supervisors);
print_r($supervisors);
echo 'Employee has '.count($supervisors).' supervisors.';
amazed that anyone can write code that fast
Well, I still left quite a bit of code writing for you to do. ;)
If you run into trouble post back and let us know so we can help. :)
