Update table record in stored procedure and return the id of that record

Question

In an Android word game for 2 players -

the game data is kept in the following table:

# desc games;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| gid      | int(11)      | NO   | PRI | NULL    | auto_increment |
| created  | int(11)      | NO   |     | NULL    |                |
| player1  | int(11)      | YES  |     | NULL    |                |
| player2  | int(11)      | YES  |     | NULL    |                |
| stamp1   | int(11)      | NO   |     | NULL    |                |
| stamp2   | int(11)      | NO   |     | NULL    |                |
| letters1 | varchar(7)   | NO   |     | NULL    |                |
| letters2 | varchar(7)   | NO   |     | NULL    |                |
| letters  | varchar(116) | NO   |     | NULL    |                |
| board    | varchar(225) | NO   |     | NULL    |                |
| style    | int(11)      | NO   |     | 0       |                |
+----------+--------------+------+-----+---------+----------------+

The player1 and player2 columns keep 2 different numerical ids of opposing players in a game.

The stamp1 and stamp2 are timestamps of the last moves (and are initially 0).

I am trying to write a stored procedure which would take a player uid and join a vacant game with just 1 other player or create a new game from scratch.

One special case is: if there is already a new game with player uid and she hasn't performed a move yet (i.e. the stamp1 is 0), then I just return the gid of that game (instead of creating yet another new game).

delimiter $$$

drop procedure if exists find_game;
create procedure find_game(IN uid integer,
                           IN letters1 varchar(7),
                           IN letters2 varchar(7),
                           IN letters varchar(116),
                           OUT gid integer)
begin
        start transaction;

        /* maybe there is a new game already, just waiting for the player's 1st move*/
        select gid into @gid from games where (player1 = uid and stamp1 = 0) or (player2 = uid and stamp2 = 0) limit 1;

        IF found_rows() = 0 THEN
                /* try to find games having just 1 player (with different uid) */
                update games set player2 = uid where player1 != uid and stamp1 > 0 and player2 = null;
                IF row_count() = 0 THEN
                        /* create new game with player1 = uid and stamp1 = 0*/
                        insert into games (created, player1, stamp1, stamp2, letters1, letters2, letters, board, style)
                        values (unix_timestamp(), uid, 0, 0, letters1, letters2, letters, space(225), 1);
                ELSE
                        /* how to find the gid of the updated record? */
                END IF;
        END IF;

        commit;
end
$$$

Unfortunately, there are 2 problems with the above code.

1) If I comment everything except the very 1st select statement above - I get null in gid, while when I run that at mysql prompt it works as expected:

# select gid from games where (player1 = 1 and stamp1 = 0) or (player2 = 1 and stamp2 = 0);
+-----+
| gid |
+-----+
|   1 |
|   2 |
|   3 |
+-----+

2) Please advise, how to find the gid of the updated game in the above case of row_count() > 0. Or maybe there is a better way?

UPDATE

I have followed the suggestion by Juan Carlos (thanks!) and am trying the following code to first select and then update:

delimiter $$$

drop procedure if exists find_game;
create procedure find_game(IN uid integer,
                           IN letters1 varchar(7),
                           IN letters2 varchar(7),
                           IN letters varchar(116),
                           OUT gid integer)
begin
        start transaction;

        /* maybe there is a new game already, just waiting for the player's 1st move*/
        select gid into @gid from games
        where (player1 = uid and stamp1 = 0) or (player2 = uid and stamp2 = 0) limit 1;

        IF select found_rows() = 0 THEN
                /* try to find games having just 1 player (with different uid) */
                select gid into @gid from games
                where (player1 != uid and stamp1 > 0 and player2 is null) limit 1;

                IF select found_rows() > 0 THEN
                        update games set player2 = uid where gid = @gid;
                ELSE
                        /* create new game with player1 = uid and stamp1 = 0*/
                        insert into games (created, player1, stamp1, stamp2, letters1, letters2, letters, board, style)
                        values (unix_timestamp(), uid, 0, 0, letters1, letters2, letters, space(225), 1);

                        select last_insert_id() into @gid;
                END IF;
        END IF;

        commit;
end
$$$

Unfortunately, I get the syntax error:

ERROR 1064 (42000): You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server 
version for the right syntax to use near 
'select found_rows() = 0 THEN

                select gid into @g' at line 13

Answer 1

if you already have this one, but this find if the player is already in a fresh new game.

 select gid into @gid 
 from games 
 where (player1 != uid and stamp1 = 0) 
    or (player2 = uid and stamp2 = 0) limit 1;

Why not change your update using something similar, where you look for games with an open seat.

 select gid into @gid 
 from games 
 where (player1 !=  uid and stamp1 > 0 and player2 is null);

 update games set player2 = uid where gid = @gid 

NOTE: why you search for stamp1 > 0?? Your insert create new games with stamp1 = 0, Your update wont find games with only 1 player