How to do sub-consultation with Pdo

Asked

Viewed 76 times

1

I would like to perform a sub-query within that 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, I want to do the same query above but not list users with banned=true.

Could you help me?

Edit: The "banned" column is in the "users" table".

php pdo query select

3 answers

3

Just give a join with 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

2


You have to use the JOIN to link 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 catch 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
");

  • No. The banned column is in another table, called users.

  • What is the name of the other table? And which column connects the two tables?

  • What do you mean? I don’t understand the question.

  • In this table usuario_emblemas, probably has the id table usuarios or some other field that connects the two tables, what is the name of that field? If you do not have a field for this purpose it will not be possible to make the link.

  • If I understood the question, it would be the user field. The query I am using counts how many values there are with the user=x.

  • Check the images: Table users: https://prnt.sc/fs0vor || Table users_badges: http://prntscr.com/fs0w0y

  • That’s right, I changed my answer, make sure it works the way you expect it to.

  • You could help me again with this query: $ranking = $Pdo->query("SELECT * FROM topicos_comments GROUP BY author ORDER BY Count(author) DESC LIMIT 3");

  • I would be very grateful.

  • I’m not getting :x

  • The field autor table topicos_comentarios is the same thing as the countryside usuario table usuarios ?

  • Yes. It’s the same.

  • 1

    This amended the reply

  • The second example did not work. Banned users continue to be listed.

  • Structure of the topical table_comments: https://prnt.sc/fs1l1r

  • Values: https://prnt.sc/fs1lbq

  • Try with the change I’ve made now. Strange not to work, the SELECT is the same, only changes the table of JOIN

  • This time he showed nothing.

  • Let’s go continue this discussion in chat.

Show 14 more comments

2

There are N ways to select two or more tables, I will 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 asterisk (*) and have many fields in the tables, you will get a slower result as "select" will bring all unnecessary fields to the query.

Browser other questions tagged php pdo query select

You are not signed in. Login or sign up in order to post.