How to get 5 LEVEL hierarchy users from database using PHP and MYSQL

Question

I want to fetch the child user ID based on the parent ID. I have found a solution how to count members in 15 level deep for each level in php Tried Answer https://stackoverflow.com/a/45535568/23864372 but am getting some errors.

I have created a class -

<?php 
Class Team extends Database { 

 private $dbConnection;

 function __construct($db)
 {
  $this->dbConnection = $db;
 }

 public function getDownline($id, $depth=5) {

        $stack = array($id);
        for($i=1; $i<=$depth; $i++) {

            // create an array of levels, each holding an array of child ids for that level
            $stack[$i] = $this->getChildren($stack[$i-1]);
        }

        return $stack;
    }

    public function countLevel($level) {

         // expects an array of child ids
         settype($level, 'array');

         return sizeof($level);
    }

    private function getChildren($parent_ids = array()) {

        $result = array();
        $placeholders = str_repeat('?,', count($parent_ids) - 1). '?'; 
        $sql="select id from users where pid in ($placeholders)";
        $stmt=$this->dbConnection->prepare($sql);
        $stmt->execute(array($parent_ids));

        while($row=$stmt->fetch()) {

            $results[] = $row->id;
        }

        return $results;
    }
}

I am using the class like this -

$id = 1;
$depth = 2;  // get the counts of his downline, only 2 deep.
$downline_array = $getTeam->getDownline($id, $depth=2);

I am getting errors - In line

  $placeholders = str_repeat('?,', count($parent_ids) - 1). '?'; 

Fatal error: Uncaught TypeError: count(): Argument #1 ($value) must be of type Countable|array, int given in

and second

Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in line

$sql="select id from users where pid in ($placeholders)";
        $stmt=$this->dbConnection->prepare($sql);

I want to fetch the child user ID in 5 levels . PHP VERSION 8.1

Database table

superheroes
       -----------
id   parent_id   name
1    0           Steven Rogers
2    1           Bruce Banner
3    1           Wally West
4    2           Peter Parker
5    2           Jay Garrick
6    4           Barry Allen
7    4           Reed Richards

              

Answer 1

There are a few small problems:

• $result = array(); is a typo and should be $results = array();
• $stack = array($id); needs to be $stack = array([$id]); because when you write $stack[$i-1] then it takes one value out of that array, and without this change that value from within the array is just 1, the first time you run the code.
• $results[] = $row->id; should be $results[] = $row["id"]; because you are fetching rows as arrays from the database, not as objects.

Here's the rewritten class:

Class Team extends Database { 

 private $dbConnection;

 function __construct($db)
 {
  $this->dbConnection = $db;
 }

 public function getDownline($id, $depth=5) {

        $stack = array([$id]);
        for($i=1; $i<=$depth; $i++) {

            // create an array of levels, each holding an array of child ids for that level
            $stack[$i] = $this->getChildren($stack[$i-1]);
        }

        return $stack;
    }

    public function countLevel($level) {

         // expects an array of child ids
         settype($level, 'array');

         return sizeof($level);
    }

    private function getChildren($parent_ids = array()) {

        $results = array();
        $placeholders = str_repeat('?,', count($parent_ids) - 1). '?'; 
        $sql="select id from users where pid in ($placeholders)";
        $stmt=$this->dbConnection->prepare($sql);
        $stmt->execute($parent_ids);

        while($row=$stmt->fetch()) {

            $results[] = $row["id"];
        }

        return $results;
    }
}

Demo: https://phpize.online/sql/mysql80/431280e176462b7e8001fab0f5b483e6/php/php81/e22e981727ccf23c655b5dbc2160c3d6/