I'm developing a 2 player Android game which makes use of a MySQL table called 'games' to keep track of the games and the players assigned to it. Its structure is as follows:
The logic I am using to connect the players is:
1) Look for incomplete games (i.e. where isGameComplete=0) and see if any of those have a player1 not set for it (i.e. where player1_id = null AND player2_id != null). If any found, assign joining player to that game by setting his id in place of player1_id.
2) Look for incomplete games (i.e. where isGameComplete=0) and see if any of those have a player2 set for it (i.e. where player2_id = null AND player1_id != null). If any found, assign joining player to that game by setting his id in place of player2_id.
3) If both above cases fail, then make a new record in games table and set player1_id = the joining player's id.
I made a stored procedure from PhpMyAdmin that I call via the server side script as soon as a player starts the game to carry out the above mentioned logic.
Begin
Set @gameId = -1;
Set @player1empty = -1;
Set @player2empty = -1;
SET @player1empty = (SELECT COUNT(*) FROM games WHERE player1_id is NULL AND player2_id is NOT NULL AND isGameComplete=0 LIMIT 1);
SET @player2empty = (SELECT COUNT(*) FROM games WHERE player2_id is NULL AND player1_id is NOT NULL AND isGameComplete=0 LIMIT 1);
CASE
WHEN @player1empty=1 THEN
UPDATE games SET player1_id = userId, id =(@gameId:=id ) WHERE player1_id is NULL AND isGameComplete=0 LIMIT 1;
Set joinedGame = @gameId;
WHEN @player2empty=1 THEN
UPDATE games SET player2_id = userId, id =(@gameId:=id ) WHERE player2_id is NULL AND isGameComplete=0 LIMIT 1;
Set joinedGame = @gameId;
ELSE
Insert into games (player1_id,gameType_id) Values (userId,8);
Set joinedGame = last_insert_id();
end CASE;
END
However, for some reason, a new player is always added in a new game record as Player1 even if there is a NULL in player2_id in an incomplete game. I've tried running the Count query separately and it does return the value 1 for both player 1 and player 2 null cases. Any help would be appreciated! And advise on how to avoid issues like this in the future. Thanks!