PHP How to check if you can afford it?

Question

Hello.

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

Answer 1

You must to get this data and check in PHP side before you do some updates. In PHP you should check if this value is not less than 0 after minus operation.

Answer 2

You need to check if points is equal or more than cost.

Answer 3

SELECT points>=:cost AS canafford
FROM member_profile
WHERE user_id=:userid

Answer 4

add a second where clause "AND points >= $scCost" and check to see how many rows were modified. One row = they could afford it. Zero rows, they could not.

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

Jacob

Answer 5

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.

Answer 6

You could check the users original points amount by doing a SQL query

$id = mysql_real_escape_string($userid);
$sql = "SELECT points FROM member_profile WHERE userid = $id";
$results = mysql_query($sql);
$row = mysql_fetch_array($results);
if($row['points'] > $scCost) {
// Update Row 
} else {
// Throw error 
}