How to join these 2 tables and retrieve values?

Databases
1

I have a table recording players when entering a game room, and there will be many, many, many game rooms.

TABLE tbl_game_room

|++ player_id ++|++ game_id ++|
|_____2 ____|123|
|_____5 ____|123|
|_____6 ____|123|
|_____5 ____|456|
|_____7 ____|456|
|_____9 ____|456|

each game room is identified by respective game_id(123,456…)

Another generic table to store players’ scores

TABLE tbl_score

|++ player_id ++|++++ score +++|
|_____2 ____|4021|
|_____4 ____|1210|
|_____5 ____|6133|
|_____6 ____|4179|
|_____7 ____|7620|
|_____9 ____|8963|

How do I retrieve each player score in a particular game room using JOIN??

Example, WITHOUT JOIN clause, for game room 123, we have 3 players 2, 5, 6


$sql = mysql_query("SELECT player_id FROM tbl_game_room WHERE game_id='123'") or die();

while ($row = mysql_fetch_array($sql)) {
  
  $playerid = $row['player_id'];

  $result = mysql_query("SELECT score FROM tbl_score WHERE player_id='$playerid'") or die();

  list($score[$playerid]) = mysql_fetch_row($result);

}

So, for game room 123, we have all players’ scores in this room,

Player ID: 2
$score[2] = 4021

Player ID: 5
$score[5] = 6133

Player ID: 6
$score[6] = 4179

But how to do it in more efficient way without query inside a loop??

2 

SELECT 
  tbl_game_room.player_id,
  score
FROM
  tbl_game_room
INNER JOIN
  tbl_score
ON
  tbl_game_room.player_id = tbl_score.player_id
WHERE
  game_id = 123
3

Thank you! But how do i retrieve values? Using loop?

Like this??


$sql = "SELECT 
  tbl_game_room.player_id,
  score
FROM
  tbl_game_room
INNER JOIN
  tbl_score
ON
  tbl_game_room.player_id = tbl_score.player_id
WHERE
  game_id = 123";

$result = mysql_query($sql) or die();

while ($row = mysql_fetch_array($result)) {
  $playerid = $row['player_id'];
  $score[$playerid] = $row['score'];
}
4

The same as you did before, yes.

5

Ok…will try to fit into real tables where there are more complicated.