John Davidson

Message:

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);   

    

---

My Answer:
> You can achieve this by using a recursive query in MySQL. Here is an example query that will give you all the child nodes under any given parent node:

sql
WITH RECURSIVE cte AS (
SELECT userid, name, parent_user
FROM users
WHERE userid = 1
UNION ALL
SELECT u.userid, u.name, u.parent_user
FROM users u
JOIN cte ON u.parent_user = cte.userid
)
SELECT * FROM cte;


This query will recursively fetch all the child nodes under the parent node with `userid = 1`. You can replace `userid = 1` with any other parent node you want to get the child nodes for.

You can execute this query in PHP using the MySQLi extension or PDO. Here is an example using MySQLi:

php
$sql = "WITH RECURSIVE cte AS (
SELECT userid, name, parent_user
FROM users
WHERE userid = 1
UNION ALL
SELECT u.userid, u.name, u.parent_user
FROM users u
JOIN cte ON u.parent_user = cte.userid
)
SELECT * FROM cte";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo "userid: " . $row["userid"]. " - Name: " . $row["name"]. "
";
}
} else {
echo "0 results";
}


This PHP code will execute the recursive query and print out the results. Make sure to replace `userid = 1` with the desired parent node ID.