php mysql使用pdo在同一个表上连接查询

问题描述：

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.

这是我的表，其中ID是表的主键，SUPER_ID用于管理层次结构，例如此处，A是 B和B的超级用户是C的超级用户，而A是C的超级用户............ ID和SUPER_ID可能不是必需的顺序 strong> \ n p>

现在问题是当A登录时，他可以看到他自己以及B和C两者的详细信息，而B登录时他可以看到他自己和C的细节。 C只能看到自己的详细信息。 p>

我写了这个查询： p>

＆lt;？php $ sql =“ SELECT * from TABLE WHERE ID =：loginId OR ID IN （ SELECT ID FROM TABLE WHERE SUPER_ID =：SuperId ）“; $ stmt = $ conn-＆gt; prepare（$ sql）; $ stmt-＆gt; execute（array（'：logedIn'=＆gt; $ _SESSION ['sess_login_id']，'：SuperId'=＆gt; $ _SESSION ['sess_login_id']））; ？＆gt; code> pre> 当我以用户A身份登录时，此查询给出了A和B的结果。我应该写什么查询，以便我可以得到A，B和C的结果当我以用户A身份登录时？因为A是B的超级用户和C的超级用户。请帮忙。谢谢。 p> div>

答

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

答

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)

php mysql使用pdo在同一个表上连接查询

相关推荐