how to count members in 15 level deep for each level in php

Question

I am badly in need of counting every member on each level I have 15 level deep Commission model so I am able to count the first level but not able to count the whole team a person have in his / her down line. I want to count how many members on each level. like on 1st level 10 members, second level 55 members, third level 35 members etc. I need to count the whole number of members for Parent id on 1st level, 2nd level ... up to 15th level. I am able to count on the first level like

$result = mysqli_query($con, "SELECT count(*) FROM affiliateuser where referedby = '" . $_SESSION['username'] . "' AND active = 1");

if you need any clarification please comment I will explain. I am sharing my first 2 Level code here. Please check the code below.

started Fetching 1st level of downline users

$totalref = 0;
$totalrefear = 0;
$query = "SELECT fname,email,doj,active,username,level,pcktaken FROM affiliateuser where referedby = '" . $_SESSION['username'] . "'";
$result = mysqli_query($con, $query);
while($row = mysqli_fetch_array($result)) {
    $ac = "$row[active]";
    $countusername = "$row[username]";
    $countuserlevel = "$row[level]";
    $pcktook = "$row[pcktaken]";
}

started Fetching 2nd level of downline users

$totalrefear = 0;
$query = "SELECT fname,email,doj,active,level,username FROM affiliateuser where referedby = '" . $_SESSION['username'] . "'";
$result = mysqli_query($con, $query);
while($row = mysqli_fetch_array($result)) {
    $ac = "$row[active]";
    $countusername = "$row[username]";
    $countuserlevel = "$row[level]";
    $query2level = "SELECT fname,email,doj,active,level,username,pcktaken FROM affiliateuser where referedby = '$countusername'";
    $result2level = mysqli_query($con, $query2level);
    while($row2 = mysqli_fetch_array($result2level)) {
        $ac2 = "$row2[active]";
        $countusername2 = "$row2[username]";
        $countuserlevel2 = "$row2[level]";
        $pcktook = "$row2[pcktaken]";
    }
}

I am trying with this query for counting users in the 2nd level of the parent. But it's counting me whole database users. I just want to count 2nd level users.any body help me solve it, please?

$queryridd =mysqli_query($con, "SELECT COUNT(Id) AS countt, level AS Lebel from affiliateuser WHERE level = '$countuserlevel' GROUP BY level");

Answer 1

Assuming that you have a parent_id field, you count how many records have parent_id equal to the one you're looking at. To get the next level down, you just repeat for each of the records you just got.

Now... how do you do that?

First, your database needs to be planned correctly. There are many ways to make a tree structure, from the simple parent - child using recursion, to materialized path, to nested sets. Each has advantages and disadvantages. I personally like materialized path; but the depth of the tree for your application makes some variation of parent-child more likely.

So what does the database structure look like?

Superheroes
----------
id         <--\
parent_id  ---/
name

So if you have a hierarchy like so:

                           1 Steven Rogers 
                           /             \
                  2 Bruce Banner       3 Wally West
                  /          \
       4 Peter Parker     5 Jay Garrick
           /        \
6 Barry Allen   7 Reed Richards

Your table would look like so:

       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

To find who is directly below Steve Rogers,

$sth = $dbh->prepare("select id,name from superheroes where parent_id=?");
$sth->execute(array(1));
$result = $sth->fetchAll();

To find who is directly below Peter Parker,

$sth = $dbh->prepare("select id,name from superheroes where parent_id=?");
$sth->execute(array(4));
$result = $sth->fetchAll();

So great, that gets one level. How do I get the next level? This is a job for a function. (Actually, a class would be much better, since scope is so much easier to deal with.)

class CountDownLine {

    public function __construct($database) {

        $this->db = $database;
    }

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

        $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). '?';

        $this->$db->prepare("select id from superheroes where parent_id in ($placeholders)");
        $this->$db->->execute(array($parent_ids));

        while($row=$this->$db->fetch()) {

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

        return $results;
    }

}

Here's how it works:

$DL = new CountDownLine(new Database); // inject your favorite database abstraction

$id = 1; // ie, id of Steve Rogers
$depth = 2;  // get the counts of his downline, only 2 deep.

$downline_array = $DL->getDownline($id, $depth=2);

// => $downline_array[1] = [2,3]
// => $downline-array[2] = [4,5]

$count_of_first_level = $DL->countLevel($downline_array[1]);
$count_of_second_level = $DL->countLevel($downline_array[2]);

Note: This is to show the concept. I did not test it on a database. You'll have to adapt it to work with your method of database access.

Answer 2

Please use if to check the levels before proceeding to another level

<?php

$query = "SELECT fname,email,doj,active,level,username FROM affiliateuser where referedby = '" . $_SESSION['username'] . "'";
$result = mysqli_query($con, $query);
while($row = mysqli_fetch_array($result)) {
    $ac = "$row[active]";
    $countusername = "$row[username]";
    $countuserlevel = "$row[level]";
    // use if to check the level
    if($countuserlevel == 2) { // use if to check the level

        $query2level = "SELECT fname,email,doj,active,level,username,pcktaken FROM affiliateuser where referedby = '$countusername'";
        $result2level = mysqli_query($con, $query2level);
        while($row2 = mysqli_fetch_array($result2level)) {
            $ac2 = "$row2[active]";
            $countusername2 = "$row2[username]";
            $countuserlevel2 = "$row2[level]";
            $pcktook = "$row2[pcktaken]";
        }

        $queryridd = mysqli_query($con, "SELECT COUNT(Id) AS countt, level AS Lebel from affiliateuser WHERE level = '$countuserlevel' GROUP BY level");
        while($rowbb = mysqli_fetch_array($queryridd)) {
            $countingUsers2 = "$rowbb[0]";
        }

    }
} // use if to check the level
?>
<td class='text-left'><?php echo $countingUsers2; ?></td>

Answer 3

You can easily get the number of children per level for a given user.

<?php
/*
* $parent_id = ID of user
* $deep = amount of depth levels
*/

function countChildrenPerLevelDeep( $parent_id, $deep = 15 ) {
    $parent_id = [$parent_id];
    $level = 1;
    $arr = [];
    $i = 1;
    do {

        if ( $parent_id ) {
            $query = 'SELECT * FROM customer WHERE parent_id=' . implode( $parent_id, ',' );

            if ( $result = $mysqli->query( $query ) ) {
                /* total children on this level */
                $arr['level_' . $level] = $result->fetch_row();

                $parent_id = [];
                /* fetch associative array */
                while ( $row = $result->fetch_assoc() ) {
                    $parent_id[] = $row['id'];
                }

                /* free result set */
                $result->close();
            } else {
                $arr['level_' . $level] = 0;
                $parent_id = [];
            }
        } else {
            $arr['level_' . $level] = 0;
            $parent_id = [];
        }

        $level++;
        $i++;
    }
    while ( $i <= $deep );

    return $arr;
}
?>

Exemple of result

Array
(
    [level_1] => 1
    [level_2] => 2
    [level_3] => 1
    [level_4] => 0
    [level_5] => 0
    [level_6] => 0
)