Need help with mysql query...

[yungbloodreborn]

I'm working on building an online game, and I need a little bit of assistance with the query to join people to games.

 

The important part of the player table is:

player (

regcode varchar(32) primary key,

gameid bigint

)

 

And the important part of the game table is:

game (

gameid bigint auto_increment primary key,

player1 varchar(32),

player2 varchar(32)

)

 

I would like to put the following code into a single query:

$sql = mysql_query("select gameid from player where regcode=\"$mycode\"");
$row = mysql_fetch_array($sql);
if($row['gameid'] == 0) {
  $sql = mysql_query("select regcode from player where game=0 and not regcode=\"$mycode\" limit 1");
  $row = mysql_fetch_array($sql);
  $opcode = $row['regcode'];
  mysql_query("insert into game (player1, player2) values (\"$mycode\", \"$opcode\")");
  $sql = mysql_query("select gameid from game where player1=\"$mycode\" and player2=\"$opcode\"");
  $row = mysql_fetch_array($sql);
  $gameid = $row['gameid'];
  mysql_query("update player set gameid=$gameid where regcode=\"$mycode\" or regcode=\"$opcode\"");
}

 

I need to make sure that all this happens in one step so that each player can only be part of a single game. Would I need to use transactions? Or how would I need to do this?

 

If I just use php as written, and the site is busy, someone else may try to grab me as a player at the same time I'm trying to get a different player, and the database would be confused.

[fenway]

You can use an UNIQUE index constraint.

[yungbloodreborn]

I'm not sure how a unique constraint would help... so I got into transactions, and that looks like it is working. I'll let you know how it goes...

[gizmola]

I'm not sure how a unique constraint would help... so I got into transactions, and that looks like it is working. I'll let you know how it goes...

 

Mysql transactions only work if all the tables support transactions.  Typically people use the innodb engine for this. 

 

However, transactions would only be part of the solution, because you also would need to place a lock on the player row using SELECT ... FOR UPDATE.  Transactions only insure that all your changes are completed.  They won't prevent one session erroneously  overwriting the data of a previous session.  This is extremely hard to test, but with your code you could have 2 sessions select the same opponent -- the first session would insert into game, then update the player and end the transaction.  The 2nd session would insert into game, and update the exact same player that is currently assigned to the 1st game, overwriting the other sessions's update.  A transaction does not prevent this -- only a lock will.

 

[fenway]

I'm not sure how a unique constraint would help... so I got into transactions, and that looks like it is working. I'll let you know how it goes...

If you have a table called player_game, each player_id should only appear once, correct?  That's a UNIQUE constraint.