Lose 1 Unit When Inserting Number Into Mysql

PHP calculate as 110400 but it inserts 110399 into MySQL

I have a model TestFloat with the schema as below.


CREATE TABLE tbl_test_float (

  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,

  `amount` int(11) DEFAULT NULL

);

As I run the code below


$testFloat = new TestFloat();

$testFloat->amount = 36.8 * 3000;

echo "TestFloat 1: {$testFloat->amount}<br />";

$testFloat->save();


$testFloat = new TestFloat();

$testFloat->amount = 110400;

echo "TestFloat 2: {$testFloat->amount}<br />";

$testFloat->save();

it displays


TestFloat 1: 110400

TestFloat 2: 110400



but it insert different values of "amount" into MySQl (110399 vs 110400).

The sql log is as below


2014/06/05 01:32:28 [trace] [system.db.CDbCommand] Executing SQL: INSERT INTO `tbl_test_float` (`amount`) VALUES (:yp0). Bound with :yp0=110399

2014/06/05 01:32:28 [trace] [system.db.CDbCommand] Executing SQL: INSERT INTO `tbl_test_float` (`amount`) VALUES (:yp0). Bound with :yp0=110400



My question is: Is this a bug of Yii?

Could be related to float precision. Casting to float and back to integer may not result in the original value.

Yes, it is float precision problem.

In PHP:


var_export(36.8 * 3000);



displays 110399.99999999999

A work around is to cast it to string, then cast it back to float.


var_export((float) (string) 36.8 * 3000)

displays 110400

I haven’t dug into Yii code to find the cause and solution.

Alternatively, use an arbitrary precision maths library, like BCMath.

If you need precision, you shouldn’t be calculating with floats directly. Use a library such as the above and use strings to represent the numbers. In your case, it would look like this:




$result = bcmul('36.8', '3000', 1);



The last parameter determines how many decimal places of precision you’ll get in the result.