Can't find a good solution to select from 2 different tables

Question

so i have this problem where i need to select from 2 different tables in the same MySQL query,

here's the query:

SELECT * FROM minigame_players WHERE lobby='$game_id'

this works perfectly, but i would like it to also select permissions from the table users where username = the returned value from the query below. As an example, let's say that the query below returns the value John, then i would like to (in the same query) select permissions from users where username = John - i have tried looking it up from multiple websites but i can't find a good solution.

you should research `join` queries - also, you're open to SQL injection — treyBake, Oct 15 '19 at 14:17
the variable `$game_id` has already been put through the mysql string escape command, so it's safe — Alex KV, Oct 15 '19 at 14:18
Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) You should consider using [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's instead of concatenated values — RiggsFolly, Oct 15 '19 at 14:19
how could an SQL injection occur if i have escaped the variable, could you give an example of an input that would cause an SQL injection? — Alex KV, Oct 15 '19 at 14:19
Show us the schema for the 2 tables, or at least tell us which column links both these tables — RiggsFolly, Oct 15 '19 at 14:20
@AlexKV show us how you make it "safe" - much easier to go from there :) — treyBake, Oct 15 '19 at 14:20
I can show you that soon, but i'd really need to get this solved as soon as possible, i don't understand how JOIN works - it's too complicated for me :( — Alex KV, Oct 15 '19 at 14:22

score 0 · Answer 1 · answered Oct 15 '19 at 14:26

You are looking for a simple JOIN:

SELECT mg.username, us.role 
FROM minigame_players mg
INNER JOIN users us ON us.username = mg.username 
WHERE mg.lobby= :game_id

Note: use prepared statement and parameterized queries at all time. Consider this famous SO post for more details.

Yecodeo · Answer 2 · 2019-10-15T14:31:07.247

-3

use a subQuery like this may help, i could suggest you using mysql JOIN too if you mapped these tables.

you can also use UNION, but you should have the same number of columns in these tables.

SELECT  (
  SELECT *
  FROM permissions
  where user = ?
 ) AS permission,
 (
  SELECT * 
  FROM minigame_players 
  WHERE lobby = ?
 ) AS player
)

use prepared statement to prevent sql injection.

edited Oct 15 '19 at 14:31

answered Oct 15 '19 at 14:24

Yecodeo

361
3
14

"where user = toto" are we assuming that the username is toto or is toto a variable or something in MySQL? I'm not so experienced when it comes to MySQL.. – Alex KV Oct 15 '19 at 14:25
toto was a value, but i forget to write script as prepared statement, perhaps you should share your tables schema or php code – Yecodeo Oct 15 '19 at 14:34

Can't find a good solution to select from 2 different tables

2 Answers2