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:

$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 :slight_smile:

Didn’t test it

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 :slight_smile: Don’t know why I didn’t think of this :-/ Works like a charm :slight_smile: Thanks