Filter the data table based on the User Login

[Senthilkumar]

Dear Team,

I want to write the query for fileter the databse and display the values based on the user login.

My data structure is like 

 

and the database is like 

 

When Groupleader 1 is login, he has to view Engineer 1, Engineer 2, Engineer 3 & Engineer 4

When Groupleader 2 is login, he has to view Engineer 5, Engineer 6, Engineer 7 & Engineer 8

When Groupleader 3 is login, he has to view Engineer 9, Engineer 10, Engineer 11 & Engineer 12

When Groupleader 4 is login, he has to view Engineer 11, Engineer 12, Engineer 13 & Engineer 14

When Teamleader 1 is login, he has to view Groupleader 1, Groupleader 2, Engineer 1, Engineer 2, Engineer 3, Engineer 4, Engineer 5, Engineer 6, Engineer 7 & Engineer 8

When Teamleader 2 is login, he has to view Groupleader 3, Groupleader 4, Engineer 9, Engineer 10, Engineer 11, Engineer 12, Engineer 13, Engineer 14, Engineer 15 & Engineer 16

When ASM is login, he has to view Teamleader 1, Teamleader 2, Groupleader 1, Groupleader 2,Groupleader 3, Groupleader 4, Engineer 1, Engineer 2, Engineer 3, Engineer 4, Engineer 5, Engineer 6, Engineer 7, Engineer 8, Engineer 9, Engineer 10, Engineer 11, Engineer 12, Engineer 13, Engineer 14, Engineer 15 & Engineer 16

When Manager is login, he has to view ASM, Teamleader 1, Teamleader 2, Groupleader 1, Groupleader 2,Groupleader 3, Groupleader 4, Engineer 1, Engineer 2, Engineer 3, Engineer 4, Engineer 5, Engineer 6, Engineer 7, Engineer 8, Engineer 9, Engineer 10, Engineer 11, Engineer 12, Engineer 13, Engineer 14, Engineer 15 & Engineer 16

How to filter the database for above my requirement. Can any one help me to solve this

[Barand]

My first observation is that the column "Approve2" in the table is redundant. If you know their supervisor (Approve1) then you know that Approve2 is that supervisor's supervisor. This can be found in the query with a join.

My second is that it would be more efficient to store Approve1 as the id of the supervisor and not the name.

I would have written the query for you but pictures of data are notoriously difficult to load into a table for testing purposes.

[Senthilkumar]

Dear Mr.Barand,

I have upload my table. you can download that from the bellow link

https://drive.google.com/file/d/10cHJOMTP1WpB0_fOu6OuS1FXXWW5bO2D/view?usp=share_link

Please provide the query 

[Barand]

Hmm. I see there is some resemblance to the structure that you posted.

[Barand]

There are some discrepancies in your data

• Karthik M appears to have 4 different employee numbers
• Naveen Kumar P appears to have 2 different employee numbers

but #321 and #1088 actually have different names...

so by doubling-up and storing id(emp no) and name of the approver you now have 2 versions of the truth.

[Senthilkumar]

I will modify the employee id later. I updated the table for your reference

[Barand]

I went with the original data that you posted as you had provided the expected results for that dataset which made checking easier. I also used approver ID instead of name and removed the redundant approver2 column. So my data is

TABLE: user

+----+--------------+-------------------+--------+------------+
| id | user_type    | user_name         | branch | supervisor |
+----+--------------+-------------------+--------+------------+
|  1 | Manager      | Manager           |    123 |       NULL |
|  2 | ASM          | Assistant Manager |    123 |          1 |
|  3 | Team Leader  | Team Leader 1     |    123 |          2 |
|  4 | Team Leader  | Team Leader 2     |    123 |          2 |
|  5 | Group Leader | Group Leader 1    |    123 |          3 |
|  6 | Group Leader | Group Leader 2    |    123 |          3 |
|  7 | Group Leader | Group Leader 3    |    123 |          4 |
|  8 | Group Leader | Group Leader 4    |    123 |          4 |
|  9 | Engineer     | Engineer 1        |    123 |          5 |
| 10 | Engineer     | Engineer 2        |    123 |          5 |
| 11 | Engineer     | Engineer 3        |    123 |          5 |
| 12 | Engineer     | Engineer 4        |    123 |          5 |
| 13 | Engineer     | Engineer 5        |    123 |          6 |
| 14 | Engineer     | Engineer 6        |    123 |          6 |
| 15 | Engineer     | Engineer 7        |    123 |          6 |
| 16 | Engineer     | Engineer 8        |    123 |          6 |
| 17 | Engineer     | Engineer 9        |    123 |          7 |
| 18 | Engineer     | Engineer 10       |    123 |          7 |
| 19 | Engineer     | Engineer 11       |    123 |          7 |
| 20 | Engineer     | Engineer 12       |    123 |          7 |
| 21 | Engineer     | Engineer 13       |    123 |          8 |
| 22 | Engineer     | Engineer 14       |    123 |          8 |
| 23 | Engineer     | Engineer 15       |    123 |          8 |
| 24 | Engineer     | Engineer 16       |    123 |          8 |
+----+--------------+-------------------+--------+------------+

Below is a small script whch allows you to select an id and view the results

<?php
include 'db_inc.php';         // USE YOUR OWN
$pdo = pdoConnect('db2');     // DB CONNECTION CODE

$login_id = $_GET['id'] ?? 1;  // default to manager's id

$res = $pdo->prepare("SELECT u1.user_type
                             , u1.user_name
                             , u1.branch
                             , u2.user_name as approver1
                             , u3.user_name as approver2
                        FROM user u1
                             LEFT JOIN user u2 ON u1.supervisor = u2.id
                             LEFT JOIN user u3 ON u2.supervisor = u3.id
                        WHERE CASE WHEN 1 < ? 
                                   THEN ? IN (u1.id, u1.supervisor, u2.supervisor, u3.supervisor)  -- show you and your minions
                                   ELSE u1.id IS NOT NULL                                          -- show all records if manager is logged in
                                   END
                        ORDER BY FIELD(u1.User_Type, 'Manager', 'ASM', 'Team Leader', 'Group Leader', 'Engineer'), u1.id
                        ");
$res->execute([ $login_id, $login_id ]);
$tdata = '';
foreach ($res as $r) {
    $tdata .= "<tr><td>" . join('</td><td>', $r) . "</td></tr>\n";
}

function idOptions($pdo, $current)
{
   $opts = '';
   $res = $pdo->query("SELECT id
                            , user_name
                            , user_type
                       FROM user
                       ORDER BY id     
                       ");
   foreach ($res as $r) {
       $sel = ($r['id']==$current) ? 'selected' : '';
       $opts .= "<option $sel value='{$r['id']}'>{$r['user_name']} ({$r['user_type']})</option>\n";
   }
   return $opts;
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<title>Example</title>
<meta charset="utf-8">
<style type='text/css'>
    body {
        font-family: verdana, arial, sans-serif;
    }
    table {
        border-collapse: collapse;
        width: 80%;
        margin: 50px auto;
    }
    th {
        background-color: black;
        color: white;
        padding: 8px 4px;
    }
    td {
        padding: 4px;
    }
    .header {
        padding: 16px;
    }
</style>
</head>
<body>
    <form class='header'>
        <h1>Sample</h1>
        Logged in user:
        <select name='id'>
            <?= idOptions($pdo, $login_id) ?>
        </select>
        <input type='submit'>
    </form>
    <hr>
    <table border='1'>
        <tr>
            <th>User Type</th>
            <th>User Name</th>
            <th>Branch</th>
            <th>Approver 1</th>
            <th>Approver 2</th>
        </tr>
        <?= $tdata ?>
    </table>
</body>
</html>

 

[Senthilkumar]

Dear Mr.Barand,

I got the solution what i want exactly. Thanks for your immediate support.

[Senthilkumar]

Dear Mr.Barand,

Can you change the code for using it on MySQL. Because I am using MySQL database for my project.

[mac_gyver]

the query and related code already is using the MySQL database. what makes you think it is not?