Yii Framework Forum: Lose 1 Unit When Inserting Number Into Mysql - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Lose 1 Unit When Inserting Number Into Mysql Rate Topic: -----

#1 User is offline   umbalaconmeogia 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 7
  • Joined: 24-November 12

Posted 04 June 2014 - 11:40 AM

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?
0

#2 User is offline   CeBe 

  • Advanced Member
  • Yii
  • Group: Yii Dev Team
  • Posts: 478
  • Joined: 16-July 10
  • Location:Berlin. Germany

Posted 04 June 2014 - 12:21 PM

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

#3 User is offline   umbalaconmeogia 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 7
  • Joined: 24-November 12

Posted 07 June 2014 - 07:07 PM

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

#4 User is offline   Keith 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,485
  • Joined: 04-March 10
  • Location:UK

Posted 08 June 2014 - 11:57 AM

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

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users