SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Get mysql query joined outside while loop

    I have a multiplayer game site where a user get his list of games like this every 20 second:
    PHP Code:
    $sql1 mysql_query("SELECT gp.fk_game_id, gp.player_tiles, gp.infoPop, gp.chatbadge, g.lastdraw FROM ".$prefix."_gameplayer gp
                            INNER JOIN "
    .$prefix."_games g ON gp.fk_game_id = g.game_id
                        WHERE gp.fk_player_id = 
    $currplayer AND g.invite=0 AND g.deleteby != $currplayer ORDER BY g.lastdraw ASC");  

    while(
    $row1 mysql_fetch_assoc($sql1)){

        
    $gameid $row1['fk_game_id'];

        
    // GET CURRENT GAME OPPONENT ID AND BOARD DATA //
        
    $sql mysql_query("SELECT gp.fk_player_id
                                  ,gp.last_draw_type
                                  ,gp.player_turn
                                  ,u.country
                                  ,u.username
                                  ,u.profileimg
                            FROM "
    .$prefix."_gameplayer gp
                                INNER JOIN "
    .$prefix."_users u
                                    ON gp.fk_player_id = u.id
                             WHERE gp.fk_player_id!=
    $currplayer AND gp.fk_game_id=$gameid");
        
    $row mysql_fetch_assoc($sql); 
    If a player has 30 games it uses alot of resources due to the mysql_query inside the while loop.

    Every game is stored in 2 db tables in 3 rows.

    A games table row which holds the game data and 2 gameplayer tabnle rows, one for the current player and one for the opponent.

    It is the row for the opponent which I have to run a second query for.

    Is it possible to join this row into the first query so I only run one query every 20 seconds for a player?

    The table schemas is as follows:

    Table games
    game_id - int(11)
    invitetime - timestamp
    lastdraw - timestamp
    timetodraw - datetime
    bag_tiles - text
    table_tiles - text
    new_tiles - text
    invite - int(11)
    random - int(11)
    active - int(11)
    finished - int(11)
    deleteby - int(11)
    warn1 - int(11)
    warn2 - int(11)

    Table gameplayer
    id - int(11)
    fk_game_id - int(11)
    fk_player_id - int(11)
    player_tiles - text
    player_draws - int(11)
    first_draw - int(11)
    player_turn - int(11)
    last_draw_type - int(11)
    player_passes - int(11)
    swapped - int(11)
    player_win - int(11)
    player_points - int(11)
    infoPop - int(11)
    chatbadge - int(11)

    Table users
    id - int(11)
    email - varchar(255)
    username - varchar(255)
    password - varchar(50)
    profileimg - varchar(25)
    country - int(11)
    register - date
    date - timestamp

    Hope this makes sense and hoping for help :-)

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Didn't test it
    Code MySQL:
    SELECT 
        gp.fk_game_id
      , gp.player_tiles
      , gp.infoPop
      , gp.chatbadge
      , g.lastdraw
      , gp2.fk_player_id
      , gp2.last_draw_type
      , gp2.player_turn
      , u.country
      , u.username
      , u.profileimg
    FROM ".$prefix."_gameplayer gp
    INNER JOIN ".$prefix."_games g 
    ON gp.fk_game_id = g.game_id
    INNER JOIN ".$prefix."_gameplayer gp2
    ON  gp.fk_game_id = gp2.fk_game_id
    AND gp.fk_player_id != gp2.fk_player_id
    INNER JOIN ".$prefix."_users u
    ON gp2.fk_player_id = u.id
    WHERE gp.fk_player_id = $currplayer 
    AND   g.invite=0 
    AND   g.deleteby != $currplayer 
    ORDER BY g.lastdraw

  3. #3
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Didn't test it
    Code MySQL:
    SELECT 
        gp.fk_game_id
      , gp.player_tiles
      , gp.infoPop
      , gp.chatbadge
      , g.lastdraw
      , gp2.fk_player_id
      , gp2.last_draw_type
      , gp2.player_turn
      , u.country
      , u.username
      , u.profileimg
    FROM ".$prefix."_gameplayer gp
    INNER JOIN ".$prefix."_games g 
    ON gp.fk_game_id = g.game_id
    INNER JOIN ".$prefix."_gameplayer gp2
    ON  gp.fk_game_id = gp2.fk_game_id
    AND gp.fk_player_id != gp2.fk_player_id
    INNER JOIN ".$prefix."_users u
    ON gp2.fk_player_id = u.id
    WHERE gp.fk_player_id = $currplayer 
    AND   g.invite=0 
    AND   g.deleteby != $currplayer 
    ORDER BY g.lastdraw
    Oh my god :-) Don't know why I didn't think of this Works like a charm :-) Thanks


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
  •