Linked Questions

Popular Questions

MySQL Stored Procedure not invoking correct CASE

Asked by At

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:

'games' table structure

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!

Related Questions