PHP money string conversion to integer error

Question

I have a small financial application with PHP as the front end and MySQL as the back end. I have ancient prejudices, and I store money values in MySQL as an integer of cents. My HTML forms allow input of dollar values, like "156.64" and I use PHP to convert that to cents and then I store the cents in the database.

I have a function that both cleans the dollar value from the form, and converts it to cents. I strip leading text, I strip trailing text, I multiply by 100 and convert to an integer. That final step is

$cents = (integer) ($dollars * 100);

This works fine for almost everything, except for a very few values like '156.64' which consistently converts to 15663 cents. Why does it do this?

If I do this:

$cents = (integer) ($dollars * 100 + 0.5);

then it consistently works. Why do I need to add that rounding value?

Also, my prejudices about storing money amounts as integers and not floating point values, is that no longer needed? Will modern float calculations produce nicely rounded and accurate money values adequate for keeping 100% accurate accounting?

Answer 1

If you want precision, you should store your money values using the DECIMAL data type in MySQL.

Answer 2

Your "prejudices" about floats will never be overcome - it's fundamental to the way they work. Without going into too much detail, they store a number based on powers of two and since not all decimal number can be presented this way, it doesn't always work. Your only reliable solution is to store the number as a sequence of digits and the location of the decimal point (as per DECIMAL type mentioned above).

I'm not 100% on the PHP, but is it possible the multiplication is converting the ints to floats and hence introducing exactly the problem you're trying to avoid?

Answer 3

Currency/money values should never be stored in a database (or used in a program) as floats.

Your integer method is fine, as is using a DECIMAL, NUMERIC or MONEY type where available.

Your problem is caused by $dollars being treated as a float and PHP doesn't have a better type to deal with money. Depending on when $dollars is being assigned, it could be being treated as a string or a float, but is certainly converted to a float if it's still a string for the * 100 operation if it looks like a float.

You might be better off parsing the string to an integer "money" value yourself (using a regex) instead of relying on the implicit conversions which PHP is doing.

Answer 4

Casting does not round() as in round-to-nearest, it truncates at the decimal: (int)3.99 yields 3. (int)-3.99 yields -3.

Since float arithmetic often induces error (and possibly not in the direction you want), use round() if you want reliable rounding.

Answer 5

It sounds like you need ceil().

Answer 6

The code you posted does the multiplication first, forcing a floating point calculation that introduces error, before converting the value to an integer. Instead, you should avoid floating point arithmetic entirely by reversing the order. Convert to integer values first, then perform the arithmetic.

Assuming previous code already validated and formatted the input, try this:

list($bills, $pennies) = explode('.', $dollars);
$cents = 100 * $bills + $pennies;

Your prejudice against floating point values to represent money is well founded because of truncation and because of values being converted from base-10 to base-2 and back again.

Answer 7

Instead of using

$cents = (integer) ($dollars * 100);

you may want to try to use:

$cents = bcmul($dollars, 100, 2);

Answer 8

When converting from float to integer, the number will be rounded towards zero (src).

Read the Floating point precision warning.

Answer 9

There's no point in storing money as integer if you enter it through a floating point operation (no pun intended). If you want to convert from string to int and be consistent with your "prejudice" you can simply use string functions.

You can use an arbitrary precision library to divide by 10 (they handle numbers internally as strings), e.g. bcdiv() or gmp_div_q(), but of course, you could have also used it from the beginning for all the math.

Or you can use plain string functions:

<?php
// Quick ugly code not fully tested
$input = '156.64';
$output = NULL;

if( preg_match('/\d+(\.\d+)?/', $input) ){
    $tmp = explode('.', $input);
    switch( count($tmp) ){
        case 1:
            $output = $tmp[0];
            break;

        case 2:
            $output = $tmp[0] . substr($tmp[1], 0, 2);
            break;

        default:
            echo "Invalid decimal\n";
    }
}else{
    echo "Invalid number\n";
}

var_dump($output);

?>

Answer 10

I use (int) instead of (integer), I suggest others to do the same.