Select in 2 tables

Question

PHP PDO Mysql The question is the following, I have two tables, one calls groups and the other membro_groups, I will give a summary in the tables, I can take the groups and show how many users each group has, but to show how many users have in each group I need to give a rowCount() in the member table, because in the member table it is basically this (id, idgroup, idusuario) so if I have 10 users in a group I will have 10 rows so I get the rowcount result which is the number of members, but I want to show the 5 groups with more members, How am I supposed to do that? If the data were all in the same table ex, group 1 has 30 members, I would give an order by members desc 5 and it would work, but how are the records in another table how am I going to do that? i can’t just order by in the other table, because there are records not in a single row, each member has a row in a group, I did it as follows.

function that picks up the groups(but it sorts by id I want to sort by which has more users)

public function ultimosGrupos($limite){
    try {
        $query = "SELECT * FROM groups ORDER BY id ASC LIMIT :limite";
        $this->Select = $this->Conn->prepare($query);
        $this->Select->bindParam(':limite' , $limite, PDO::PARAM_INT);
        $this->Select->execute();
        if($this->Select->rowCount() > 0){
            return $this->Select->fetchAll(PDO::FETCH_ASSOC);
        }else{
            return FALSE;
        }
    } catch (PDOException $exc) {
        exibeMensagens("Erro ao consultar. {$exc->getMessage()}", WS_ERROR);
    }
}

Now you have the function to count how many user records you have in each group

public function users_por_grupo($id){
        try {
            $query = "SELECT * FROM group_memberships WHERE group_id = :id";
            $this->Select = $this->Conn->prepare($query);
            $this->Select->bindParam(':id' , $id, PDO::PARAM_INT);
            $this->Select->execute();
            return $this->Select->rowCount();
        } catch (PDOException $exc) {
            exibeMensagens("Erro ao consultar. {$exc->getMessage()}", WS_ERROR);
        }
    }

tabela grupo (
id
nome
dono
created
)

tabela membrosgrupo(
id
idgrupo
idusuario
)

Answer 1

Hello, Martins.

No need to use row_count or anything, you can use the SQL functions itself, such as COUNT a GROUP BY and make a relationship between the two tables using LEFT JOIN. This way, you can only query and access all the data you want already formatted. To rescue the TOP 5 groups with more members, would look like this.

SELECT
  grupos.nome,
  COUNT(*) as total_membros
FROM
  grupos
LEFT JOIN
  membros
ON
  grupos.id = membros.idgrupo
GROUP BY
  grupos.nome
ORDER BY
  total_membros DESC
LIMIT 5;

This will turn you into a table, where the first column is the name of the Group and the second the number of members present in each of them.

See working on JDOODLE: https://www.jdoodle.com/a/106M