SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    220
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    how to join these 2 tables and retrieve values?

    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

    PHP Code:
    $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. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    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. #3
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    220
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thank you! But how do i retrieve values? Using loop?

    Like this??

    PHP Code:
    $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. #4
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    The same as you did before, yes.

  5. #5
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    220
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Ok...will try to fit into real tables where there are more complicated.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •