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.

1. Use ini_set('precision', 20);
2. 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 :)

Answer 1

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.