Help with Select Count Function...

[kpowning]

I'm trying to allow this script to only count the number of referals by the users usersname and usersid, that have the following in the the users same database row (user.usergroupid=2).

Basically if there is a '3' rather than a '2' the user is not yet verified, so I don't want that certain user to be counted as a refered member. I only want members to be counted as a refered member if they only have the '2' in which is verified and registered member.

Here is my script at this point:

$max=2; //This number decides how many users to display
$referrers = $DB_site->query("SELECT COUNT(*) AS referrals, user.username, user.usergroupid, user.userid FROM user AS users LEFT JOIN user ON (users.referrerid = user.userid)
                   WHERE users.referrerid <> 0
                   GROUP BY users.referrerid
                   ORDER BY referrals DESC
                   LIMIT $max");
while ($referrer=$DB_site->fetch_array($referrers)) {
	$referreds = $DB_site->query("SELECT username FROM user WHERE referrerid = '$referrer[userid]' AND user.usergroupid=2");
	while ($referred = $DB_site->fetch_array($referreds)){
		if ($referrerlist) {
			$referrerlist.=", $referred[username] ";
		} else {
			$referrerlist = "$referred[username]";
		}
	}
	eval("\$reflistbits .= \"".gettemplate("reflistbit")."\";");
	$referrerlist="";
}
eval("dooutput(\"".gettemplate("reflist")."\");");

ANY HELP GREATLY APPRECIATED....

Thanks,
Kyle

[sarahk]

You don't say what problems you are having but your sql stands out, i've made a change to the limit statement

$referrers = $DB_site->query("SELECT COUNT(*) AS referrals, user.username, user.usergroupid, user.userid FROM user AS users LEFT JOIN user ON (users.referrerid = user.userid) 
                   WHERE users.referrerid <> 0 
                   GROUP BY users.referrerid 
                   ORDER BY referrals DESC 
                   LIMIT 0, $max");