I'm developing a web page for a friend that allows participants of a sports event to "claim" a slot in a 24 hour tennis marathon. The marathon has room for 24 x 4 participants.
My idea was to have a table with the columns "hour" [0-23], "time" and "name", and pre-fill that table with 4 rows for each hour that have empty time and names columns.
Then, when someone fills in the form stating his name and the hour he wants to play, I'd update the table using something like
UPDATE slots SET time = NOW(), name = "John Doe"
WHERE hour=14 AND name=""
BUT AT MOST 1 ROW
and then I'd check PHP's mysql_affected_rows() to see if the slot was claimed successfully. Because if 5 people are trying to enroll for the same hour simultaneously, one of the attempts will have to fail.
I can't find a way to do this. So, my design is probably incorrect? Any suggestions how to tackle this? Thanks