PHP saving precision float into MYSQL ends up with rounded resulting field

Question

I've read all over about arithmetic regarding floating point numbers, but I'm just trying to accurately store the darn things.

I have a mysql field with the type of DECIMAL (40,20).

I am saving a value in php of 46457.67469999996. After updating the record with this value, the end result is 46457.67470000000000000000. Not sure why it's being rounded at all just being saved to the database.

The value is not being converted to a string or anything beforehand. The field value that is passed into PDO is the value I expected to be saved and it is returned as a float... Perhaps it's because I'm saving a PHP float to a mysql decimal type where the rounding is occurring?

What am I missing here?

EDIT: Added example code that has the issue

// Query placeholder variables.  Hard-coded for the example test
$query_vars = array(
    ":vendor_id"                    => 33154,
    ":year"                         => 2018,
    ":coop_committed_dollar"        => 46457.67469999996,
    ":coop_committed_dollar_update" => 46457.67469999996
);

$statement = "  INSERT INTO vendor_data_yearly
                (vendor_id, year, coop_committed_dollar) VALUES
                (:vendor_id, :year, :coop_committed_dollar)
                ON DUPLICATE KEY UPDATE
                    coop_committed_dollar = :coop_committed_dollar_update;";
$query = $connection->conn->prepare($statement);
$query->execute($query_vars);

When I run this, the resulting value of coop_committed_dollar is 46457.67470000000000000000. This code is legit all I am doing.

Possible solution

// Note that I am casting the string using the BC Math library.
// I dunno how to just initialize the number (lame documentation), so I'm adding 0 to it.
$number = "46457.674699999967";
$number = bcadd("46457.674699999967", 0, 20);

$query_vars = array(
    ":vendor_id"                    => 33154,
    ":year"                         => 2018,
    ":coop_committed_dollar"        => $number,
    ":coop_committed_dollar_update" => $number
);

$statement = "  INSERT INTO vendor_data_yearly
                (vendor_id, year, coop_committed_dollar) VALUES
                (:vendor_id, :year, :coop_committed_dollar)
                ON DUPLICATE KEY UPDATE
                    coop_committed_dollar = :coop_committed_dollar_update;";
$query = $conn->prepare($statement);
$query->execute($query_vars);

This results in the number as expected in the DB.

ONLY SOLUTION I FOUND TO WORK CORRECTLY

The data I am working with is passed in via ajax. I had to take a few steps to get this to work correctly.

Use ini_set('precision', 20);
Manually set the data in question to be a string BEFORE sending it via ajax so PHP would not round it, extended with extra floating point madness, padd it, etc.

I found that PHP would just not let me reliably work with large numbers coming from a variable set outside the script's scope (ajax). Once PHP got it's hands on the number, it would do what it had to do in order to make it make sense as a float.

If anyone has a better solution for this particular scenario I'm all ears and eyes :)

No I haven't. From my understanding, decimal should be used if it's a monetary value. Perhaps that's totally incorrec though: code.rohitink.com/2013/06/12/… — Gurnzbot
– Gurnzbot, Commented Jul 31, 2018 at 17:38
If it's for a monetary value then decimal is certainly correct. I just tried manually adding your value to a decimal 40,20 column and it remained accurate and was not rounded. Perhaps the PHP code you are using is doing something you're not expecting. — Dave
– Dave, Commented Jul 31, 2018 at 17:41
No. I have added some code to my question that results in the rounded value as I described. Hopefully it shows what I am doing properly for you guys. — Gurnzbot
– Gurnzbot, Commented Jul 31, 2018 at 17:58

Evan de la Cruz · Accepted Answer · 2018-07-31 18:42:14Z

2

The problem is that PHP's precision is not allowing you to store the exact number you think you are storing. When you set ":coop_committed_dollar" => 46457.67469999996 PHP is actually storing it as a different value, depending on the precision.

The solution is to store the value in PHP as a string instead of a float.

Since your question is: "what am I missing", I will try to provide an answer. Basically it comes down to storing floats internally using binary representation. Since 46457.67469999996 cannot be exactly in binary (it ends up with an infinite number, similar to 33% (.3333...) in base-10), the closest rounding is used based on PHP's precision (set in php ini).

I was given a great explanation in this question that I asked a while back.

In your particular case, it also seems that the value that you are sending via AJAX is being stored as a float when parsed by PHP on the server-side. You want it to be stored as a string instead. If you're using json_decode, add this option: JSON_BIGINT_AS_STRING.

edited Jul 31, 2018 at 18:42

answered Jul 31, 2018 at 18:15

Evan de la Cruz

1,9761 gold badge13 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Gurnzbot Over a year ago

Can you elaborate on how to utilize this library with my question in mind? That library requires all numbers to be initialized as strings. How would I handle this if the number is coming via AJAX and starts out as a number. PHP would lose the precision since I can't make it a string... right?

Evan de la Cruz Over a year ago

It will typecast to a string automatically in PHP and retain its value. So 46457.67469999996 will become "46457.67469999996"

Evan de la Cruz Over a year ago

I answered that (about string typecasting) hastily. I actually don't know. I am going to try to go play with it. I provided my answer based on an answer I got a while ago, and I dont remember how it all works tbh.

Evan de la Cruz Over a year ago

Because yeah you're right. If PHP cant store the number accurately how could it possibly cast it to a string correctly. I wonder if, when parsing the AJAX, you can parse it as a string instead of a number. After-all, ajax is a text-based protocol. It only becomes a number when PHP parses the AJAX.

Evan de la Cruz Over a year ago

Even if a JSON numeric value is unquoted, eg: {"SomeValue": 100}, the "100" part is transmitted as a string regardless. The parser on the other end will interpret the fact that it is not quoted as meaning it is a number. That's what the JSON_BIGINT_AS_STRING is for. So being quoted or not quoted is really just a hint for the parser. Ajax is always transmitted as text (string). fwiw.

|

Collectives™ on Stack Overflow

PHP saving precision float into MYSQL ends up with rounded resulting field

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related