Getting results in multiple levels

[kilnakorr]

Hi

 

I'm trying to figure out how to do a query for users on multiple levels in an organisation.
Example from picture, I need to be able to fetch the 'users' that a 'leader' has in their path.

I'm thinking in the database, that each user, will have his/hers leader id or name in a field.

 

+----+----------+------------+
| id | username | leadername | 
+----+----------+------------+
|  1 | fred     | mo    	 |
|  2 | mo       | 		     |
|  3 | brian    | mo		 |
|  4 | john     | mo 		 |
|  5 | peter    | fred 		 |
|  6 | curly    | fred 		 |
|  7 | joan     | fred 		 |
|  8 | Dennis   | curly 		 |
+----+----------+------------+

How can I get all the usernames for a given user? ('mo' has ALL users in his path. 'fred' has 'peter', 'curly', 'joan' AND 'Dennis' in his path.)

 

[Barand]

Use ids, not names, to link records.

TABLE: user
+----+----------+------------+
| id | username | leader_id  | 
+----+----------+------------+
|  1 | fred     | 2          |
|  2 | mo       |   NULL     |
|  3 | brian    | 2          |
|  4 | john     | 2          |
|  5 | peter    | 1          |
|  6 | curly    | 1          |
|  7 | joan     | 1          |
|  8 | Dennis   | 6          |
+----+----------+------------+

Recursion is your friend here.

$res = $db->query("SELECT id
                        , username
                        , leader_id
                   FROM usertest
                   order by leader_id     
                  ");
$users = [];
// store users in array for each leader
foreach ($res as $r) {
    $users[$r['leader_id']][] = [ 'id' => $r['id'], 'username' => $r['username'] ];
}

echo '<pre>';
listStaff(null, $users, 0);                // list staff for leader "null"
echo '</pre>';


/**
* recursive function to list staff
* 
* @param int $id
* @param array $users
* @param int $level
*/
    function listStaff($id, &$users, $level=0)
    {
        $indent = str_repeat("\t", $level);
        
        foreach ($users[$id] as $u) {                         // for each of their staff
            echo "$indent{$u['username']}<br>";               //    outout the name
            if (isset($users[$u['id']])) {                    //    if they have staff
                listStaff($u['id'], $users, $level+1);        //        list their staff
            }
        }
    } 

Giving

mo
	fred
		peter
		curly
			Dennis
		joan
	brian
	john

 

Edited November 14, 2019 by Barand

[kilnakorr]

I was thinking using an ids instead.

Can you explain why ids would be a better choice?

Thanks for your answer, I'll give it a try later today

[Barand]

2 hours ago, kilnakorr said:

Can you explain why ids would be a better choice?

• Data normization
• Efficiency
• Spelling mistakes

[kilnakorr]

Point taken.

 

One thing though:

How will I be able to make a 'startpoint' from a specific user?

Let's say I only want results for users under username 'fred'. How will I do that?

[Barand]

Fred's id is 1, therefore

listStaff(1, $users);