I am trying to do ordering on query with subquery for a name
from uuid
. Let’s have those two tables in MySQL:
bans:
id | uuid | time | reason |
---|---|---|---|
1 | c6b8eade-7db9-345b-b838-8f8361552cf5 | 1642369382 | Test |
2 | 0138c279-c5fa-3acd-adaa-8edb7b4569d8 | 1642384565 | Spam |
3 | 3c01262c-a3c3-3133-ba43-92a9ded01c27 | 1631876477 | Hax |
users:
id | uuid | name |
---|---|---|
45 | c6b8eade-7db9-345b-b838-8f8361552cf5 | John |
100 | 0138c279-c5fa-3acd-adaa-8edb7b4569d8 | Mike |
35 | 3c01262c-a3c3-3133-ba43-92a9ded01c27 | Norman |
With this query, I can select whole ban
record + fetch user’s name
from users
table:
SELECT bans.*, (SELECT users.name FROM users WHERE users.uuid = bans.uuid) as name FROM bans ORDER BY :column;
But I cannot order by name
. How to change this query so I can do ordering by users.name
? I tried to wrap my SQL select into another select, but it does not work and I am unable to resolve it 😀
Advertisement
Answer
That is a JOIN query if I ever saw one.
You cannot pass a table or column name as a parameter in either PDO or MYSQLI. The database cannot compile and optimise code with unknown columns or tables in it
SELECT b.*, u.name FROM bans b LEFT JOIN users u ON u.uuid = b.uuid ORDER BY u.name;