I am building a multi-level referral system that has downlines. In the referral table there is a row for only the id of the parent. Now i need to write a query that loops the table selecting all the children, grandchildren, great grandchildren, etc of an upline. For example, I want to select the id of all children of an upline and then use the id of the children and select the downlines of the children and continue like that until the last generation or downlines of the last child is selected.
My challenge is that since I do not know how many great grand children or how many legs that parent will have, i am having challenges constructing my query and writing the script to take care of that.
This is the code i have written so far, i want to get the IDs of all of the direct and indirect downlines, put them in an array and then loop through the array and get their details from the user table.
function cumulative_commission() {
global $con;
$user = $_SESSION['user_id'];
$group_members = array();
$sql1 = "SELECT * FROM `rpc` WHERE `user_id` = '$user' AND `confirmed` = 1";
$select = mysqli_query($con, $sql1);
if(mysqli_num_rows($select)>0){
$group_members[] = $user;
}
$sql = "SELECT rpc.*, users.* FROM rpc ";
$sql .= "LEFT JOIN users ON users.id = rpc.user_id ";
$sql .= "WHERE rpc.top_id = '$user' AND confirmed =1";
$query = mysqli_query($con,$sql);
if(mysqli_num_rows($query)>0){
while($data = mysqli_fetch_assoc($query)){
$group_members[]=$data['user_id'];
}
}
return $group_members;
}