Get all the members below me in an mlm module in PHP and MySQL-CodePudding

I have a database, as shown in this, any user can join with any sponsor ID, and they will get their own user ID which can lead the tree further. I want to get details of a particular user_id, say, '2'. I am expecting the output in which the user_id will be (3,4 since 3 is directly linked with 2 and 4 is linked directly with 3 which is linked with 2). Similarly if I select user_id 1, then I would get the resultant user_id (2,3,4,5, where 2 and 5 are directly linked to user_id 1 and 3,4 are indirectly linked to 1).

I have tried almost every possible while loop format, but I just can't get the output. Here is the final bit of my code, because I have deleted most of it:

<?php
include 'config.php';
$current_user='1';

$all = mysqli_query($con,"SELECT * FROM users");
while($all_array = mysqli_fetch_array($all)){
    $all_sponsors = $all_array['sponsor_id'];
}

$below_details = mysqli_query($con,"SELECT * FROM users WHERE sponsor_id ='$current_user'");
while ($below_array = mysqli_fetch_array($below_details)){
    $below_users = $below_array['user_id']; 
    }
?>

Any kind of help is appreciated. Feel free to ask me the details if there is any confusion in the question.

CodePudding user response：

You can use a recursive CTE to retrieve all the members below a given id -

WITH RECURSIVE pyramid AS (
    SELECT u.* FROM users u WHERE sponsor_id = 1 /* starting sponsor_id */
    UNION ALL
    SELECT u.* FROM pyramid p JOIN users u ON p.id = u.sponsor_id
)
SELECT * FROM pyramid;