Senthilkumar Posted March 1, 2023 Share Posted March 1, 2023 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted March 1, 2023 Share Posted March 1, 2023 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. Quote Link to comment Share on other sites More sharing options...
Senthilkumar Posted March 1, 2023 Author Share Posted March 1, 2023 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted March 1, 2023 Share Posted March 1, 2023 Hmm. I see there is some resemblance to the structure that you posted. Quote Link to comment Share on other sites More sharing options...
Barand Posted March 1, 2023 Share Posted March 1, 2023 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. Quote Link to comment Share on other sites More sharing options...
Senthilkumar Posted March 1, 2023 Author Share Posted March 1, 2023 I will modify the employee id later. I updated the table for your reference Quote Link to comment Share on other sites More sharing options...
Barand Posted March 1, 2023 Share Posted March 1, 2023 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> 1 Quote Link to comment Share on other sites More sharing options...
Solution Senthilkumar Posted March 3, 2023 Author Solution Share Posted March 3, 2023 Dear Mr.Barand, I got the solution what i want exactly. Thanks for your immediate support. Quote Link to comment Share on other sites More sharing options...
Senthilkumar Posted April 9, 2023 Author Share Posted April 9, 2023 Dear Mr.Barand, Can you change the code for using it on MySQL. Because I am using MySQL database for my project. Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted April 10, 2023 Share Posted April 10, 2023 the query and related code already is using the MySQL database. what makes you think it is not? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.