PaulF PaulF - 1 year ago 90
MySQL Question

php mySql Update Set is rounding decimal numbers down

I have a DB with a column called "Price"

Type is DECIMAL, Length/Values is set to 15,4 and Default is 0.0000

When running the following:

$table[$x]['MSRP'] = 10.5000;
$table[$x]['Materialid'] = 1744000004;

$mysql['updateprice'] = $mysql['conn']->prepare("UPDATE oc_product SET price = ? WHERE sku = ?");
$mysql['updateprice']->bind_param("ii", $table[$x]['MSRP'],$table[$x]['Materialid']); //s=string i=integer d=double b=blob

The number saved comes out as "10.000"

This happens no matter which number i use.

For example,

12.7 becomes 12.0000

12.7000 becomes 12.0000

13.1 becomes 13.0000

13.1000 becomes 13.0000

When using phpmyadmin to insert the number, it works without any problems.

Ive tried using floatval and not had any luck with this.

How can I make this save exactly as given without modifying the structure of the db?

Answer Source

You're binding the variables as integers. You need to bind them as doubles, as you have noted in your commented code

$mysql['updateprice']->bind_param("di", $table[$x]['MSRP'],$table[$x]['Materialid']); 
//s=string i=integer d=double b=blob
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download