php mysql join query on same table using pdo

Question

This is my table in which ID is table's primary key and SUPER_ID is used to manage hierarchy for example here, A is super user of B and B is super user of C while A is super-super user of C............ ID AND SUPER_ID MAY NOT NECESSARY BE SEQUENTIAL

Now question is when A is logged in, He can see details of his own and of Both B and C While B is logged in He can see details of His own and C only. And C can see his own details only.

I have written this query:

<?php
$sql="SELECT * from TABLE 
WHERE 
ID =:loginId OR                                                  
ID IN 
(
SELECT ID FROM TABLE 
WHERE SUPER_ID =:SuperId
)";
$stmt = $conn->prepare($sql);
$stmt->execute(array(':logedIn' => $_SESSION['sess_login_id'] , ':SuperId' => $_SESSION['sess_login_id']) );
?>

This query gives me results of A and B when i logged in as User A. What query should I write so that I can get results of A , B and C when I logged in as User A? Because A is a super user of B and super-super user of C. Please help. Thank You.

Sorry i forget to mention, there is only three level hierarchy. So no scope of this to happen. — Ketan Joshi
– Ketan Joshi, Commented Nov 21, 2015 at 9:05
If SUPER_ID is always sequentially larger then the query can be simplified to (something like) select * from TABLE where ID = loginiId or SUPER_ID > SuperId — Tigger
– Tigger, Commented Nov 21, 2015 at 9:24
"SUPER_ID is always sequentially larger then the query" is not necessary. As it is editable from front end so any user is assigne under any user according to his role(there are three roles); Otherwise your solution is good! — Ketan Joshi
– Ketan Joshi, Commented Nov 21, 2015 at 9:43

Paul Spiegel · Accepted Answer · 2015-11-25 21:20:12Z

2

If you have only three hierarchy levels, you can do it like this:

SELECT DISTINCT u.*
FROM user loggedin
LEFT JOIN user children      ON children.SUPR_ID      = loggedin.ID
LEFT JOIN user grandchildren ON grandchildren.SUPR_ID = children.ID
JOIN user u ON u.ID IN (loggedin.ID, children.ID, grandchildren.ID)
WHERE loggedin.ID = :loginId

http://sqlfiddle.com/#!9/3d93c/7

edited Nov 25, 2015 at 21:20

answered Nov 21, 2015 at 11:44

Paul Spiegel

31.9k5 gold badges48 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ketan Joshi Over a year ago

Thank you sir.. it works. if you can explain the query it will be grateful. I am not much expert in queries.

Paul Spiegel Over a year ago

I renamed the table aliases. Hope that helps you to understand it. Replace SELECT DISTINCT u.* with SELECT * to see what MySQL have done in the background.

user5542121 · Accepted Answer · 2015-11-21 10:23:06Z

2

http://sqlfiddle.com/#!9/ad6a88/2/0 First get SUPR_ID from the user, then check which SUPR_ID is bigger or same.

SELECT * FROM `TABLE` WHERE SUPR_ID >= (SELECT SUPR_ID FROM `TABLE` WHERE ID=:loginId)

answered Nov 21, 2015 at 10:23

user5542121

1,06312 silver badges28 bronze badges

3 Comments

Ketan Joshi Over a year ago

ID and SUPER_ID not necessary be sequential.

user5542121 Over a year ago

If SUPER_ID is not sequential. how do you determine which one is higher in hirarchy?.

Ketan Joshi Over a year ago

There is no such specification but there is one field in same table which defines role, say P > Q > R. So cant have child, P can have child as Q roles user or R roles user and Q can only have R roles user as a child.

Collectives™ on Stack Overflow

php mysql join query on same table using pdo

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related