-1

When I send a value to MySQL in PHP like this:

$mysqli->query("update bank set cash = $cash");

It works fine for smaller numbers, but anything 100 trillion or larger yields unexpected results. Sometimes it updates the number in increments of 100, and sometimes not at all.

A prepared statement also has different, but unreliable results once the number gets larger than a couple billion:

$stmt->prepare("update bank set cash = ?");
$stmt->bind_param('i',$new_cash_amt);
$stmt->execute();
Nelson
  • 1,055
  • 1
  • 7
  • 4
  • 1
    `$mysqli->query('update player_stats set cash = cash + '.$amount.' where username = "cheater2"');` – Pankit Kapadia Dec 20 '12 at 09:08
  • what is size of int or datatype you have given in db? – Ganesh RJ Dec 20 '12 at 09:10
  • It looks like I fubar'd my question. I am getting a variable using mt_rand and then sending it to add_cash() and then it only updates in intermittent increments of 10 or 100. I wonder if it is a problem with type. Strangely, I don't get this problem when I run add_cash using other variables. I am working on updating my question. – Nelson Dec 20 '12 at 09:33

3 Answers3

3
  1. Use double quotes.
  2. use or die(mysql_error()); to see you bug.
  3. Stop using mysql* function, will be deprecated soon.

Fix:

$amount = 17;
$mysqli->query("
        update player_stats 
        set cash = cash + $amount 
        where username = 'cheater2'
") or die(mysql_error());
Glavić
  • 42,781
  • 13
  • 77
  • 107
  • This makes it work, but I don't know why. There is also the same problem when this is in a prepared statement. – Nelson Dec 20 '12 at 09:11
  • Well, darn. It works when I run the query directly, breaks when I run it through my game's add_cash function. This is getting stranger. It only breaks on values larger than 100 trillion. Updated question with add_cash function. – Nelson Dec 20 '12 at 09:20
1

You're using single quotes, which wont parse a php variable. It's looking for cash=cash+$amount as a string, not a variable holding data.

Sterling Archer
  • 22,070
  • 18
  • 81
  • 118
0

I'm answering my own question here.

It turns out that when you pass values like this:

$huge_number = 100000000000012345;
echo "The huge_number is: $huge_number";

It will print the following:

The huge_number is: 1.0000000000001E+17

The precise value is lost in this conversion. That is why the value increments in multiples of 100 sometimes, and not at all at other times.

As with the prepared statement, any values larger than a 32bit integer (since I put an 'i' as the type in the bound parameter) will get truncated and altered unexpectedly. I should have passed the new value as a 'd', like so:

$stmt->bind_param('d',$new_cash_amt); //This works correctly
Nelson
  • 1,055
  • 1
  • 7
  • 4