Change level at id recursion

Question

For example, we are now logged on as user id 10, when our recursion code is executed we get a tree, here:

Level  ID  P_ID
  1    12   10
  1    13   10
  1    14   9
  2    15   12
  2    16   14
  2    17   14
  3    18   17

It should be like this:

 Level  ID  P_ID
  1    12   10
  1    13   10
  2    15   12

Because we are looking from the current id, i.e. for another id the construction logic is the same.

Recursion code:

    $res = $mysql->query("SELECT * FROM table"); 

$data = array();
while($row = mysqli_fetch_assoc($res)){
    $data['spids'][$row['id']]        = $row['sponsor_id'];

    if(isset($data['level'][$row['sponsor_id']])){
        $level = $data['level'][$row['sponsor_id']] + 1;
    } else {
        $level = 0;
    }

    $data['level'][$row['id']] = $level;
}

echo '
<table>
        <tr>
            <td>Level</td>
            <td>ID</td>
            <td>P_ID</td>
        </tr>';
foreach($data['level'] as $id=>$level){
    if ($level != 0) {
    echo '
    <tr>
        <td>'.$level.'</td>
        <td>'.$id.'</td>
        <td>'.$data['spids'][$id].'</td>

    </tr>';
    }
}
echo '
</table>';

Can anyone help how to change part of the function so that the selection of the tree is correct?

Answer 1

Here is your recursive process. See inline comments for explanation of steps. I've added your commented table data in place of the query and resultset generation so that I could test my code.

(You'll only need to delete the two hardcoded resultset and uncomment the query and retrieval lines.)

Code: (Demo)

function recursive_search($haystack,$needles,$level=1,&$result=[]){
    foreach($haystack as $k=>$row){
        //echo "\nchecking: {$row['P_ID']} against: ".implode(',',$needles);
        if(in_array($row['P_ID'],$needles)){  // look for qualifying rows
            //echo " found";
            $result[]=array_merge(['Level'=>$level],$row);                   // store qualifying row
            $new_needles[]=$row['ID'];        // store the search value for the next recursive call
            unset($haystack[$k]);             // reduce the haystack to improve efficiency and avoid infinite loop
        }
    }
    if(isset($new_needles)){
        recursive_search($haystack,$new_needles,$level+1,$result);  // recurse as long as there are new needles declared
    }
    return $result;
}

// $res = $mysql->query("SELECT id AS ID, sponsor_id AS P_ID, username AS Name FROM user");
// for($resultset=[]; $row=$res->fetch_assoc(); $resultset[]=$row);  // inspired by: http://php.net/manual/en/mysqli-result.fetch-assoc.php#112924
$resultset=[
    ['ID'=>'0','P_ID'=>NULL,'Name'=>'RMagen'],
    ['ID'=>'1','P_ID'=>NULL,'Name'=>'siokpol'],
    ['ID'=>'2','P_ID'=>NULL,'Name'=>'green'],
    ['ID'=>'3','P_ID'=>NULL,'Name'=>'test12'],
    ['ID'=>'5','P_ID'=>NULL,'Name'=>'Dist'],
    ['ID'=>'7','P_ID'=>NULL,'Name'=>'name'],
    ['ID'=>'8','P_ID'=>NULL,'Name'=>'sas'],
    ['ID'=>'9','P_ID'=>NULL,'Name'=>'dad'],
    ['ID'=>'10','P_ID'=>NULL,'Name'=>'name541'],
    ['ID'=>'11','P_ID'=>'1','Name'=>'ini'],
    ['ID'=>'12','P_ID'=>'9','Name'=>'dad2'],
    ['ID'=>'13','P_ID'=>'9','Name'=>'dad3'],
    ['ID'=>'14','P_ID'=>'9','Name'=>'dad4'],
    ['ID'=>'15','P_ID'=>'9','Name'=>'dad5'],
    ['ID'=>'16','P_ID'=>'9','Name'=>'dad6'],
    ['ID'=>'17','P_ID'=>'12','Name'=>'dad21'],
    ['ID'=>'18','P_ID'=>'12','Name'=>'dad22'],
    ['ID'=>'19','P_ID'=>'12','Name'=>'dad23'],
    ['ID'=>'20','P_ID'=>'12','Name'=>'dad24'],
    ['ID'=>'21','P_ID'=>'13','Name'=>'dad31'],
    ['ID'=>'22','P_ID'=>'13','Name'=>'dad32'],
    ['ID'=>'23','P_ID'=>'13','Name'=>'dad33'],
    ['ID'=>'24','P_ID'=>'14','Name'=>'dad41'],
    ['ID'=>'25','P_ID'=>'14','Name'=>'dad42'],
    ['ID'=>'26','P_ID'=>'14','Name'=>'dad43'],
    ['ID'=>'27','P_ID'=>'17','Name'=>'dad211'],
    ['ID'=>'28','P_ID'=>'17','Name'=>'dad212'],
    ['ID'=>'29','P_ID'=>'17','Name'=>'dad213'],
    ['ID'=>'30','P_ID'=>'18','Name'=>'dad221'],
    ['ID'=>'31','P_ID'=>'18','Name'=>'dad222'],
    ['ID'=>'32','P_ID'=>'18','Name'=>'dad223'],
    ['ID'=>'33','P_ID'=>'27','Name'=>'dad2111'],
    ['ID'=>'34','P_ID'=>'27','Name'=>'dad2112'],
    ['ID'=>'35','P_ID'=>'27','Name'=>'dad2113'],
    ['ID'=>'36','P_ID'=>'30','Name'=>'dad2211'],
    ['ID'=>'37','P_ID'=>'30','Name'=>'dad2212'],
    ['ID'=>'38','P_ID'=>'30','Name'=>'dad2213'],
    ['ID'=>'39','P_ID'=>NULL,'Name'=>'sas2'],
    ['ID'=>'40','P_ID'=>NULL,'Name'=>'sas3'],
    ['ID'=>'42','P_ID'=>'14','Name'=>'dad433']
];

//$idUser = 10; // YIELDS NO RECURSIVE SEARCH RESULTS
$idUser = 1;  // YIELDS ONE RECURSIVE SEARCH RESULT
//$idUser = 9;  // YIELDS MANY RECURSIVE SEARCH RESULTS
$array=recursive_search($resultset,[$idUser]);  // pass `$idUser` as an array element

echo "<table>";
    echo "<tr><td>Level</td><td>ID</td><td>P_ID</td><td>Name</td></tr>";
    foreach($array as $row){
        echo "<tr><td>{$row['Level']}</td><td>{$row['ID']}</td><td>{$row['P_ID']}</td><td>{$row['Name']}</td></tr>";
    }
echo "</table>";

Output:

<table>
    <tr>
        <td>Level</td>
        <td>ID</td>
        <td>P_ID</td>
        <td>Name</td>
    </tr>
    <tr>
        <td>1</td>
        <td>11</td>
        <td>1</td>
        <td>ini</td>
    </tr>
</table>