MyBB Community Forums

The "# of Users" column in the groups page in admincp doesn't count secondary's.

admin/modules/users/groups.php

1172	$query = $db->query("
1173	   SELECT g.gid, COUNT(u.uid) AS users
1174	   FROM ".TABLE_PREFIX."users u
1175    LEFT JOIN ".TABLE_PREFIX."usergroups g ON (g.gid=u.usergroup)
1176	   GROUP BY g.gid
1177	");
1178	while($groupcount = $db->fetch_array($query))
1179	{
1180	   $primaryusers[$groupcount['gid']] = $groupcount['users'];
1181	}
1182
1183	switch($db->type)
1184	{
1185	  case "pgsql":
1186	  case "sqlite3":
1187	  case "sqlite2":
1188	    $query = $db->query("
1189	        SELECT g.gid, COUNT(u.uid) AS users
1190	        FROM ".TABLE_PREFIX."users u
1191         LEFT JOIN ".TABLE_PREFIX."usergroups g ON (','|| u.additionalgroups|| ',' LIKE '%,'|| g.gid|| ',%')
1192	       WHERE g.gid != '0' AND g.gid != NULL GROUP BY g.gid
1193	    ");
1194	    break;
1195	default:
1196    $query = $db->query("
1197      SELECT g.gid, COUNT(u.uid) AS users
1198	     FROM ".TABLE_PREFIX."users u
1199       LEFT JOIN ".TABLE_PREFIX."usergroups g ON (CONCAT(',', u.additionalgroups, ',') LIKE CONCAT('%,', g.gid, ',%'))
1200	     WHERE g.gid != '0' AND g.gid != NULL GROUP BY g.gid
1201	  ");
1202	}
1203 while($groupcount = $db->fetch_array($query))
1204	{
1205	$secondaryusers[$groupcount['gid']] = $groupcount['users'];
1206	}

....

1272	$numusers = $primaryusers[$usergroup['gid']];
1273	$numusers += $secondaryusers[$usergroup['gid']];

Doesn't look wrong to me?

Actually I think,

LEFT JOIN ".TABLE_PREFIX."usergroups g ON (CONCAT(',', u.additionalgroups, ',') LIKE CONCAT('%,', g.gid, ',%'))

should be

LEFT JOIN ".TABLE_PREFIX."usergroups g ON (CONCAT(',', u.additionalgroups, ',') LIKE '%'+CONCAT(',', g.gid, ',')+'%')

Ryan that produces SQL error for me.

SQL Error:
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '+CONCAT(',', g.gid, ',')+'%') WHERE g.gid != '0' AND g.gid != NULL GROUP BY ' at line 3
Query:
SELECT g.gid, COUNT(u.uid) AS users FROM mybb_users u LEFT JOIN mybb_usergroups g ON (CONCAT(',', u.additionalgroups, ',') LIKE '%'+CONCAT(',', g.gid, ',')+'%') WHERE g.gid != '0' AND g.gid != NULL GROUP BY g.gid

Yeh, I was guessing with the concatenation (was at school at the time). I can look it up on the MySQL site, but it might be faster if someone already knows?

I am not a CONCAT pro. I used it a couple times but that's a pretty complex query. I think I will let you mess with that one.

Oh nevermind, I don't know what I was thinking. That query looks fine. Are you sure the count is wrong?

It says 0 for all my custom usergroups. When you click "Show Users" it displays them all fine. They are just not part of the count on the groups page.

For the custom groups that are primaries it's fine. It's only the custom groups that are additional usergroups that are the problem.

I double checked the 30 some-odd groups we have on ncaabbs.com and they appear to be showing accurate counts. If you run the plain query via mysql or phpMyAdmin what are the result sets?