0

I have below table with child and parent id's

users

userid   name    parent_user
1        abc      6
2        xyz      1    
3        ppp      2    
4        mmm      5

I want to get result like below. e.g. i need to show all team member of userid 1, then result should be userid 1,2,3.

userid   name   
1        abc      
2        xyz       
3        ppp

First ,I had tried to get it with below query but it's showing only 1 and 2 userid not 3

SELECT *
FROM users
WHERE userid = 1
UNION
SELECT * 
FROM users
WHERE parent_user IN 
    (SELECT userid FROM users WHERE userid = 1)

Then i had tried to get it with php, but it's also not working. is there any other way to get it. i had tried many solutions alreday on stack but nothing is working for me.

$sql = $conn->prepare("SELECT * FROM users");   
$sql->execute();
$resultDETAILS = $sql->get_result();

while($row = mysqli_fetch_array($resultDETAILS, MYSQLI_ASSOC)) 
{
    echo $sqlTest = "SELECT * FROM users where parent_user = $row['userid']  or userid = 1";    
    $resultATTENDS = $conn->query($sqlTest); 
    while($row = mysqli_fetch_array($resultATTENDS, MYSQLI_ASSOC)) 
    {
        
        $id[] =  $row['userid'];
    }   
    
    
}

print_r($id);
userdev2222
  • 23
  • 5
  • 2
    Use recursive CTE in the query. – Akina Jun 10 '22 at 06:42
  • Back in 2003, there was an interesting article I came across regarding storing hierarchical in a database and utilizing a technique where you add left and right index columns to your table: https://www.sitepoint.com/hierarchical-data-database There's three pages and a nice diagram on the second page. If the left was 3 and the right was 4, this would indicate a node with no children. If the left was 3 and the right was 6, this would indicate there's one child (left: 4, right: 5). And in this manner you can easily detect what surrounds what, etc. – Kevin Y Jun 10 '22 at 07:05
  • These days though, if I went about this, I'd probably utilize an extra table as a pivot table for establishing relationships regarding what belongs to what. – Kevin Y Jun 10 '22 at 07:09
  • @Akina can you please let me know how i can use recursive CTE in my query – userdev2222 Jun 10 '22 at 07:15
  • You can see the CTE method here: https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query – Kevin Y Jun 10 '22 at 07:18
  • [WITH (Common Table Expressions)](https://dev.mysql.com/doc/refman/8.0/en/with.html) – Akina Jun 10 '22 at 07:50
  • i had already checked this but it's not working in mysql v 5.0.1.That's why looking for solution in php – userdev2222 Jun 10 '22 at 08:33
  • i had used recursive function to achieve this from ref https://stackoverflow.com/questions/11497202/get-all-child-grandchild-etc-nodes-under-parent-using-php-with-mysql-query-resu – userdev2222 Jun 10 '22 at 11:00

0 Answers0