Jump to content

Filter the data table based on the User Login


Senthilkumar
Go to solution Solved by Senthilkumar,

Recommended Posts

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 

image.thumb.png.325c139c420c2582a9315e06ecb1f1ad.png

 

and the database is like 

image.thumb.png.bd31e1a9f6f337c6191701d72e00825b.png

 

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

image.png.50e7b8c27ccbe7e384191f61855c8f53.png

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

image.png.a9fb420bd91f94e2f51cb41315a3f044.png

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

Link to comment
Share on other sites

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>

 

  • Like 1
Link to comment
Share on other sites

  • 1 month later...
This thread is more than a year old. Please don't revive it unless you have something important to add.

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.