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