Kieron Wiltshire
Kieron Wiltshire

Reputation: 81

Most efficient way to query this in MySQL

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

Answers (2)

John Conde
John Conde

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

Charles Bretana
Charles Bretana

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

Related Questions