CODEWITHSUNDEEP
phpmysql
Mansa
Mansa

Reputation: 2325

Getting 2 mysql queries into one?

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 :-)

Upvotes: 1

Views: 107

Answers (1)

Nelson Benítez León
Nelson Benítez León

Reputation: 50563

You can try the following:

 $sql1 = mysql_query("SELECT gp.fk_player_id,gp.last_draw_type,gp.player_turn, u.country,u.username,u.profileimg,
                    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 
                    INNER JOIN ".$prefix."_users u ON gp.fk_player_id = u.id
                    WHERE g.invite=0 AND g.deleteby != $currplayer 
                    AND gp.fk_game_id=$gameid ORDER BY g.lastdraw ASC");

Upvotes: 1

Related Questions