Getting manager of manager of an employee

I have an employee table which contains a manager ID as a column. If I need to get the manager of manager of a certain employee, how would I do it efficiently? How about if we are required to a 5 layers de开发者_开发问答ep?

If this a requirement can we change the database schema so that this could be done more efficienty?

Read this article to see how you can define your own version of Oracle's connect by customization.

When I'm doing something like this I would probably just have people in table employee

Then I'd have a table management with columns manager_id and employee_id

Both columns are referencing employee.id (because a manager is an employee too, get it?)

That way you can just create a management entry for each employee that points to his manager, and do the same for each manager, etc... and it will go as many levels deep as you need.

Edit: Really I would assume you would have another table managerdata with additional data about each manager, but I wouldn't duplicate data that's in employee - just add the extra details about the manager and include a column pointing to employee.id

The only way I can think of is to just recurse up as far as you need. A simple example would be:

///hight: how far up the manager chain to go
///startid: The id from which to start
function getManager ($startid, $hight)
{
    return getManagerRecurse ($hight, 0, $startid);
}
function getManagerRecurse ($hight, $curHight, $curid)
{
    if ($hight == $curHight)
        return $curid;
    $sql = "SELECT `managerid` FROM `table` WHERE `id`='$curid' LIMIT 1";
    $result = mysql_query($sql);
    if (mysql_num_rows($result) == 0)
        return -1; //Can't go up any further, so just return -1
    list($manid) = mysql_fetch_array($result);
    return getManagerRecurse ($hight, $curHight + 1, $manid);
}


getManager (15,2); //Start at id 15, go up 2 managers

Note: not tested