I would like to perform a subquery within this query:
$ranking = $pdo->query("SELECT * FROM usuarios_emblemas GROUP BY usuario ORDER BY count(usuario) DESC LIMIT 3");
It would be to block the list of users with banned = true, ie I want to do this same query above but that does not list users with banned = true.
Could you help me?
Edit: The "banned" column is in the "users" table.
You have to use JOIN to interconnect the two tables and make the comparison:
$ranking = $pdo->query("
SELECT * FROM usuarios a
INNER JOIN usuarios_emblemas b ON a.usuario = b.usuario
WHERE a.banido <> true
GROUP BY a.usuario
ORDER BY count(a.usuario) DESC
LIMIT 3
");
As you requested in the comments, follow the SELECT to get the authors:
$ranking = $pdo->query("
SELECT * FROM usuarios a
INNER JOIN topicos_comentarios b ON a.usuario = b.autor
WHERE NOT a.banido
GROUP BY a.usuario
ORDER BY count(a.usuario) DESC
LIMIT 3
");
Only give join to the user table:
SELECT
e.*
FROM usuarios_emblemas e
inner join usuarios u on u.id = e.usuario and not u.banido
GROUP BY e.usuario ORDER BY count(e.usuario) DESC LIMIT 3
There are N ways to select two or more tables, I'll cite two examples:
Example 1:
$ranking = $pdo->query("SELECT ue.id, ue.nome, u.banido FROM usuarios_emblemas ue, usuarios u where (ue.id_usuario = u.id) and (u.banido = 0)/*Continuação*/");
Example 2:
$ranking = $pdo->query("SELECT ue.id, ue.nome, u.banido FROM usuarios_emblemas ue LEFT JOIN usuarios u on (ue.id_usuario = u.id) where (u.banido = 0)/*Continuação*/");
Tip: If you like to use an asterisk (*) and have many fields in the tables, you will have a slower result because "select" will bring all the unnecessary fields to the query.