Check this out recursive

[Cardale]

        public function getBonusChildren($userID)
        {
	if($userID != NULL)
	{
		$sql = 'SELECT COUNT(*) AS count, userID FROM jos_backoffice_users WHERE parentID= ' . $userID . ';';
		$stmt = conn::getInstance()->prepare($sql);
		$stmt->execute();
                	$obj = $stmt->fetchAll(PDO::FETCH_ASSOC);
		if(is_object($obj))
		{
			$obj->count += $this->getBonusChildren($obj->userID);
		}
	}
                return $obj->count;
}

 

I get no error.  It just always return null.  How can I get this done?

[Cardale]

Here is another version that only displays the first level.... any help is appreciated.

        public function getBonusChildren($userID)
        {
	// retrieve all children of $parent
	$sql = 'SELECT userID FROM jos_backoffice_users WHERE parentID ="'. $userID .'";';
	$stmt = conn::getInstance()->prepare($sql);
	$stmt->execute();

        	while($row = $stmt->fetchObject())
	{
                	$memStatus = checkUserMemStatus($row->userID);

                	if($memStatus == true)
                	{
                    		$memberCounter++;
			$this->getBonusChildren($row->userID);
                	}
	}
	return $memberCounter;
}

[Adam]

I don't understand why you're going to so much trouble for a count() of users? You can do it with the query you had already, minus the where condition:

 

SELECT count(userID) FROM jos_backoffice_users

 

Slightly off-topic..

 

Just to let you know, the way you're using prepared statements is wrong. The variables in a statement should be bound to the statement object separately, and then executed. This means you can then re-execute or bind new variables to the same statement, re-using the server's execution plan with a huge improvement in efficiency. The way you're doing it right now is constructing a new statement for each unique query, completely bypassing the point.

[ignace]

I think this will do what you want.

 

public function getBonusChildren($userID)
{
    // retrieve all children of $parent
    $sql = <<<SQL
SELECT userID, (SELECT count(userID) FROM jos_backoffice_users T2 WHERE T2.parentID = T1.userID) child_count
  FROM jos_backoffice_users T1
-- WHERE mem_status = ?
SQL;
    $rows = conn::getInstance()->query($sql)->fetchAll();
    return $rows;
}

[Cardale]

Not quite what I'm after.  The userID changes based on the children found.  We then find their children and count those as well.  So each user found can have their own set of children and this can continue for 8 levels.

[Cardale]

Haha I found my problem.  It would help if I added the value of the recursion to the return value.  Ha.  Thanks for your help though.