get all mlm downlines of an upline (php)

Question

I want to get all down-lines of a father in a binary tree, each father has left and right arms, each arm has left and right arms etc.. like the following image. In my database i have a table called users, each user has a father id and position which is L or R.

Here is my function .. but it still don't get all downlines. like the following image.

Answer 1

Two things stand out to me:

1. The $i argument and the use of $this->downline_id_arr.

Consider doing:

$children = array();
foreach($data as $row) {
    $child_id = $row->id;
    $children[$child_id] = array(/**/);
    $children = array_merge($children, $this->getAllDownline($child_id);
}
return $childen;

Now you don't need the $i variable or $this->downline_id_arr.

2. You're querying each node one by one.

Consider querying by level instead:

function getAllDownlines($fathers) {
    $data = "SELECT * FROM users WHERE father_id IN (/*fathers*/)";
    $new_father_ids = array();
    $children = array();
    foreach ($data as $child) {
        $children[$child->id] = array(/**/); // etc

        $new_father_ids[] = $child->id;
    }
    $children = array_merge($children, $this->getAllDownlines($new_father_ids);
    return $childen;
}

Typically less queries is faster so you should see better performance.