Jump to content
kilnakorr

Getting results in multiple levels

Recommended Posts

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.)

 

org.JPG

Share this post


Link to post
Share on other sites

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 by Barand
  • Like 1

Share this post


Link to post
Share on other sites

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 :)

Share this post


Link to post
Share on other sites
2 hours ago, kilnakorr said:

Can you explain why ids would be a better choice?

  • Data normization
  • Efficiency
  • Spelling mistakes

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.