Query Math

[russellmac]

I have an innoDB that gets updated often(several times a second) recently I've noticed that when a row gets updated, sometimes instead of doing the math i have defined outside the query it is doubling the exsiting row. for example

$newnumber = $user->points + $amount;
$result = mysql_query("UPDATE `users` SET `points` = '".$newnumber."' WHERE `id`='".$user->id."'");

 

normally it works fine. Say they had 5000 points and $amount was 6 then it would update to 5006 however sometimes it updates to 10012!

I'm thinking that it is possible that two queries maybe running into each other or something and getting added at the same time??

Would it be better to do the math inside the query like this?

$result = mysql_query("UPDATE `users` SET `points`=`points`+'".$amount."' WHERE `id`='".$user->id."'");

[Ken2k7]

yeah, but if $amount is a number, you should remove the single quotes around it. Same for id.

 

$result = mysql_query("UPDATE `users` SET `points`=`points`+ ".$amount." WHERE `id`= ".$user->id);

?

[russellmac]

cool thanks, so you think that will eliminate those double row updates by doing the math inside the query? Also, what does removing the single quotes effect? speed?

 

Again, thanks for your help

[PFMaBiSmAd]

Your page is probably being requested twice by the browser. Are you doing any duplicate request detection in your code?

[russellmac]

Actually no i dont think so, can you point me in the direction as to where I can read up on it?

[russellmac]

also i wanted to add, it really puzzels me as to why it doubles for I would think the last query update would just rewrite the first, if a row is locked i dont see how 2 can combine and get writen that way into the row.

[PFMaBiSmAd]

I don't think something basic like that can be found by searching, you just set a session variable once your code has processed the request and then test at the start of your code is that session variable is already set and skip the processing if it is.

 

<?php
session_start();
// determine if the form processing code has been executed once
$processed = isset($_SESSION['processed']) ? TRUE : FALSE;

if(!$processed) {
    // do your normal form processing here

    // at the point that the form data has been completely and correctly processed, set the session variable to indicate not to process it again during the current session -
    $_SESSION['processed'] = TRUE;
}
?>

[russellmac]

OK i will give that a try thanks. Also had a follow up question about Ken2k7 suggestion to remove the sing quotes... would it even be better to just remove all quotes from the variable like this?

$result = mysql_query("UPDATE `users` SET `points`=`points`+$amount WHERE `id`= ".$user->id);

I tried it and it still processed ok, I was just wondering what the benefit of it is.

[PFMaBiSmAd]

Single-quotes cause data to be treated as a string. Numbers in a string are first converted to a floating point number, then they are used as a number, thereby wasting processing time.

[russellmac]

OK great, so would using no quotes vs double quotes with a number make any difference?