[SOLVED] Preventing simultaneous updates in MySQL

[longtone]

I want to run a competition where the first person to click on a link after it becomes active wins.

 

$result = mysql_query("SELECT*FROM table WHERE won = 1 AND id = '$id'") ;
if (mysql_num_rows($result) = 0) {
mysql_query("UPDATE table SET won = 1 WHERE id ='$id'" );
echo 'You're a winner!';
} else {
echo 'Bye loser';
}

 

should work, unless another entrant sneaks in between the two queries, checks that there is no winner and announces their win, leading to two or more winners.

 

I hink I can work out some logic to prevent this, but is there a simple way, like combining the two quries into one that is executed instantly?

[BillyBoB]

include a timestamp with the database entrant and then if it becomes a problem and they think they won just tell them that they were milliseconds away and to deal with the loss

[sasa]

mysql_num_rows($result) = 0 is newer true

[PFMaBiSmAd]

What you need to do is check the won value in the WHERE clause, so the UPDATE will only happen if the value is zero. Then use mysql_affected_rows() to find out if the row was updated.

 

mysql_query("UPDATE table SET won = 1 WHERE won = 0 AND id ='$id'" ); // I am guessing id is the "link" id

if(mysql_affected_rows() == 1)
{
echo 'You're a winner!';
} else {
echo 'Bye loser';
}

[longtone]

mysql_num_rows($result) = 0 is newer true

Oops!

I mean == , one day I'll stop doing that.

[longtone]

What you need to do is check the won value in the WHERE clause, so the UPDATE will only happen if the value is zero. Then use mysql_affected_rows() to find out if the row was updated.

 

 

Thanks, that's perfect.