Reputation: 81
I have an array of permission groups, like Admin, Moderator etc... I want to query the SQL database and retrieve the amount of people with these groups... Here is my code:
$groups = $this->getGroupsUsingPermission("sabre.access");
foreach($groups as $g) {
$command = "SELECT COUNT(PERMISSION_GROUP) FROM users
WHERE PERMISSION_GROUP='".$g."';";
$query = mysqli_query($this->connection, $command) or die (mysqli_error());
$amountOfStaff = $amountOfStaff + mysqli_fetch_row($query)[0];
}
Is there a better way of doing this other than querying the database over and over?
UPDATE:
I mean like this:
groups:
member:
permissions:
- sabre.access
admin
permissions:
- sabre.admin
inheritance:
- member
Now, I have this working, along with the inheritance. It returns an array like:
member, admin (any other groups which have access to 'sabre.access' etc...)
So when I set someone's group like this:
ID, USERNAME, PERMISSION_GROUP
1 Kieron Owner
I want to count everyone who has any one of those groups which are in the array, you get me?
Upvotes: 0
Views: 75
Reputation: 219794
Use MySQL's IN()
to search all of the groups at once:
$groups = $this->getGroupsUsingPermission("sabre.access");
$group_ids = implode("','", $groups)
$command = "SELECT COUNT(PERMISSION_GROUP) FROM users WHERE PERMISSION_GROUP IN('".$group_ids."');";
$query = mysqli_query($this->connection, $command) or die (mysqli_error());
$amountOfStaff = $amountOfStaff + mysqli_fetch_row($query)[0];
Here I use implode()
to take the group IDs and make them a comma separated string. I then make that the value we pass to IN()
. You could also use FIND_IN_SET()
here as well.
$groups = $this->getGroupsUsingPermission("sabre.access");
$group_ids = implode(',', $groups)
$command = "SELECT COUNT(PERMISSION_GROUP) FROM users WHERE FIND_IN_SET(PERMISSION_GROUP, '".$group_ids."');";
$query = mysqli_query($this->connection, $command) or die (mysqli_error());
$amountOfStaff = $amountOfStaff + mysqli_fetch_row($query)[0];
Upvotes: 5
Reputation: 146409
Don't you want count of users in each group ?
$groups = $this->getGroupsUsingPermission("sabre.access");
$group_ids = implode(',', $groups)
$command = "SELECT PERMISSION_GROUP, COUNT(*) userCount FROM users
WHERE PERMISSION_GROUP IN (".$group_ids.")
Group By PERMISSION_GROUP";
$query = mysqli_query($this->connection, $command) or die (mysqli_error());
$amountOfStaff = $amountOfStaff + mysqli_fetch_row($query)[0];
Upvotes: 0