[SOLVED] PHP How to check if you can afford it?

PHP How to check if you can afford it?

I have this:

    $scCost = $row["gpsc"];
    mysql_query("
        UPDATE member_profile 
        SET points = points-$scCost 
        WHERE user_id = '".mysql_real_escape_string($userid)."'
    ") or die(mysql_error());

That takes do the user´s points - scCost.

How do i check if the user can afford it or not? So, if the user has 30 and the $scCost is 40..

Solution

You can do this atomically by adding an extra condition to the query and using mysql_affected_rows():

$scCost = $row["gpsc"];
$user_id = mysql_real_escape_string($user_id);
$sql = <<<END
UPDATE member_profile
SET points = points - $scCost
WHERE user_id = $user_id
AND points >= $scCost
END;
mysql_query($sql);
if (mysql_affected_rows() > 0) {
  // they can afford it
}

This is substantially better than doing a SELECT followed by an UPDATE, which introduces a race condition.

Caveat: mysql_affected_rows() returns the number of rows that were changed. This is important to understand. If you pass 0 cost into this query you end up with:

UPDATE member_profiles SET points = points - 0 ...

mysql_affected_rows() will always return 0 in that instance because no rows where changed. So if 0 cost is a valid case, you need to filter this and not bother running the query at all.

Also this works well if you're updating one row but it gets a little more difficult if you want to modify several rows at once. Then you get into questions like:

What if some "rows" can afford it but others not?
Do you want them all to fail?
How do you report on which ones couldn't afford it?
How do you do all this atomically?

You may be best off doing one UPDATE at a time even though this normally isn't the recommended approach and certainly won't scale to thousands of updates at once.