When dealing with fixed point numbers, you have to be very careful – especially if you develop with PHP and MySQL. In this article, obstacles and subtleties of working with the PHP BCMath extension, MySQL fixed point expression handling and persisting fixed point data from PHP to MySQL are described. Despite the occurring barriers we try to figure out how to work with fixed point numbers and not to lose a digit.

## Troubles with BCMath

BCMath documentation says:

For arbitrary precision mathematics PHP offers the Binary Calculator which supports numbers of any size and precision, represented as

strings.

So BCMath function parameters should be represented as strings. Passing numeric values to `bcmath`

can lead to wrong results, the same precision loss as when we treat double value as string

### Case 1

```
echo bcmul(776.210000, '100', 10) . PHP_EOL;
echo bcmul(776.211000, '100', 10) . PHP_EOL;
echo bcmul(776.210100, '100', 10) . PHP_EOL;
echo bcmul(50018850776.210000, '100', 10) . PHP_EOL;
echo bcmul(50018850776.211000, '100', 10) . PHP_EOL;
echo bcmul(50018850776.210100, '100', 10) . PHP_EOL;
```

Results are:

```
77621.00
77621.100
77621.0100
5001885077621.00
5001885077621.100
5001885077621.00 //here we can see precision loss
```

**Never pass numeric values to BCMath functions, only string values that represent numbers**. Even when not dealing with floating points, BCMath can output strange results:

### Case 2

```
echo bcmul('10', 0.0001, 10) . PHP_EOL;
echo bcmul('10', 0.00001, 10) . PHP_EOL;
echo 10*0.00001 . PHP_EOL;
```

Results are:

```
0.0010
0 // thats really strange!!!
0.0001
```

The reason for this is that BCMath converts its arguments to strings, and there are cases in which a number’s string representation has exponential notation.

### Case 3

`echo bcmul('10', '1e-4', 10) . PHP_EOL; //outputs 0 as well`

PHP is a weakly typed language and in some cases you can’t control input in a strict way – you want to process as many requests as possible.

For example we can “fix” *Case 2* and *Case 3* by applying `sprintf`

transformation:

```
$val = sprintf("%.10f", '1e-5');
echo bcmul('10', $val, 10) . PHP_EOL;
// gives us 0.0001000000
```

but applying the same transformation can break *Case 1* “proper” behaviour:

```
$val = sprintf("%.10f", '50018850776.2100000000');
echo bcmul('10', $val, 10) . PHP_EOL;
echo bcmul('10', 50018850776.2100000000, 10) . PHP_EOL;
500188507762.0999908450 //WRONG
500188507762.10 //RIGHT
```

So the `sprintf`

solution is not suitable for BCmath. Assuming all user inputs are strings, we can implement a simple validator, catching all exponential notation numbers and converting them properly. This technique is done in php-bignumbers, so we can safely pass in arguments like `1e-20`

and `50018850776.2101`

without losing precision.

```
echo bcmul("50018850776.2101", '100', 10) . PHP_EOL;
echo bcmul(Decimal::create("50018850776.2101"), '100', 10) . PHP_EOL;
echo bcmul(Decimal::create("1e-8"), '100', 10) . PHP_EOL;
echo bcmul("1e-8", '100', 10) . PHP_EOL;
echo bcmul(50018850776.2101, '100', 10) . PHP_EOL;
echo bcmul(Decimal::create(50018850776.2101), '100', 10) . PHP_EOL;
// Result
// 5001885077621.0100
// 5001885077621.0100
// 0.00000100
// 0
// 5001885077621.00
// 5001885077621.00982700
```

But the last two lines of the example show us that floating point caveats cannot be avoided by input parsing (which is completely logical – we can not deal with PHP internal double representation).

## BCMath final guidelines

Never use floating point numbers as fixed point operation arguments. String conversion does not help, because we can not manage the precision loss in any way.

When using BCMath extension operations, be careful with arguments in exponential representation. BCMath functions do not process exponential arguments (i.e. ‘1e-8’) correctly, so you should convert them manually. Be careful, do not use `sprintf`

or similar conversion techniques, because it leads to precision loss.

You can use the php-bignumbers library which handles input arguments in exponential form and provides users with fixed point math operations functions. However, its performance is worse than that of the BCMath extension, so it’s a kind of compromise between a robust package and performance.

## MySQL and fixed point numbers

In MySQL, fixed point numbers are handled with the `DECIMAL`

column type. You can read the official MySQL documentation for data types and precision math operations.

The most interesting part is how MySQL handles expressions:

Handling of a numeric expression depends on the kind of values the expression contains:

If any approximate values are present, the expression is approximate and is evaluated using floating-point arithmetic.

If no approximate values are present, the expression contains only exact values. If any exact value contains a fractional part (a value following the decimal point), the expression is evaluated using DECIMAL exact arithmetic and has a precision of 65 digits. The term “exact” is subject to the limits of what can be represented in binary. For example, 1.0/3.0 can be approximated in decimal notation as .333…, but not written as an exact number, so (1.0/3.0)*3.0 does not evaluate to exactly 1.0.

Otherwise, the expression contains only integer values. The expression is exact and is evaluated using integer arithmetic and has a precision the same as BIGINT (64 bits).

If a numeric expression contains any strings, they are converted to double-precision floating-point values and the expression is approximate.

Here is a short example that demonstrates fractional part cases:

```
mysql> CREATE TABLE fixed_point (
-> amount NUMERIC(40,20) NOT NULL
-> ) engine=InnoDB, charset=utf8;
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO fixed_point (amount) VALUES(0.2);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT amount, amount + 0.1, amount + 1e-1, amount + '0.1' FROM fixed_point;
+------------------------+------------------------+---------------------+---------------------+
| amount | amount + 0.1 | amount + 1e-1 | amount + '0.1' |
+------------------------+------------------------+---------------------+---------------------+
| 0.20000000000000000000 | 0.30000000000000000000 | 0.30000000000000004 | 0.30000000000000004 |
+------------------------+------------------------+---------------------+---------------------+
1 row in set (0.00 sec)
```

It may seen quite straightforward, but let’s look at how to deal with it within PHP.

## Precision math in PHP & MySQL

So now we have to persist our fixed point values from PHP into MySQL. The right way is to use prepared statements and placeholders within our queries. Then we do parameter binding and everything is safe and secure.

```
$amount_to_add = "0.01";
$stmt = $dbh->prepare("UPDATE fixed_point SET amount = amount + :amount");
$stmt->bindValue("amount", $amount_to_add);
$stmt->execute();
```

When we bind a value to a statement placeholder, we can specify its type by the `bindValue`

third argument. Possible types are represented by constants `PDO::PARAM_BOOL`

, `PDO::PARAM_NULL`

, `PDO::PARAM_INT`

, `PDO::PARAM_STR`

, `PDO::PARAM_LOB`

and `PDO::PARAM_STMT`

. So the problem is that the PHP PDO extension does not have a decimal parameter type for binding. As a result, all math expressions in queries are treated as floating point expressions, not as fixed point expressions.

```
$dbh = new PDO("mysql:host=localhost;dbname=test", "root", "");
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "
CREATE TABLE IF NOT EXISTS fixed_point (
amount DECIMAL(43,20)
)
";
$dbh->query($sql);
$dbh->query("DELETE FROM fixed_point");
$dbh->query("INSERT INTO fixed_point VALUES(0.2)");
$amount_to_add = "0.1";
$stmt = $dbh->prepare("UPDATE fixed_point SET amount = amount + :amount");
$stmt->bindValue("amount", $amount_to_add);
$stmt->execute();
$stmt = $dbh->prepare("SELECT amount FROM fixed_point");
$stmt->execute();
var_dump($stmt->fetchColumn());
//output is string(22) "0.30000000000000004000"
```

If we want to take the advantage of prepared statements and work with fixed point numbers, the best way is to perform all math operations in PHP and save results to MySQL.

```
$amount_to_add = "0.1";
$stmt = $dbh->prepare("SELECT amount FROM fixed_point");
$stmt->execute();
$amount = $stmt->fetchColumn();
$new_amount = bcadd($amount, $amount_to_add, 20);
$stmt = $dbh->prepare("UPDATE fixed_point SET amount=:amount");
$stmt->bindValue("amount", $new_amount);
$stmt->execute();
$stmt = $dbh->prepare("SELECT amount FROM fixed_point");
$stmt->execute();
$amount_after_change = $stmt->fetchColumn();
echo $amount_after_change . PHP_EOL;
```

## Conclusion

We’ve reached the following conclusions:

- Never use floating point numbers as fixed point operations arguments in BCMath PHP extension funcitons. Only strings.
- BCMath extension does not work with string numbers in exponential representation
- MySQL supports fixed point number expressions, but all operands have to be in decimal format. If at least one agrument is in exponential format or string, it is treated as floating point number and the expression is evaluated as floating point number.
- PHP PDO extension does not have
`Decimal`

parameter type, so if you use prepared statements and binding parameters in SQL expressions that contain fixed point operands – you won’t get precise results. - To perform precise math operations in PHP+MySQL applications you can choose two ways. The first one is to process all operations in PHP and persist data to MySQL only with
`INSERT`

or`UPDATE`

statements. In this case you can use prepared statements and parameter binding. The second one is to build SQL queries manually (you can still use prepared statements, but you have to escape parameters by yourself) so all SQL math expressions are in decimal number representation.

My personal favorite approach is the first one: all math operations in PHP. I agree that PHP and MySQL may be not the best choice for applications with precision math, but if you chose this technology stack, it’s good to know that there is a way to deal with it the right way.

## Frequently Asked Questions (FAQs) about PHP BCMath Precision Loss Cases

### What is the BCMath extension in PHP?

The BCMath extension in PHP is a binary calculator that supports numbers of any size and precision, represented as strings. This extension is particularly useful when dealing with large numbers or high precision arithmetic, where the native PHP functions may not provide accurate results. The BCMath functions can perform addition, subtraction, multiplication, division, modulus, power, and comparison operations, among others.

### How can I install the BCMath extension in PHP?

The BCMath extension is usually included by default in most PHP installations. However, if it’s not present, you can install it by recompiling PHP with the ‘–enable-bcmath’ configuration option. Alternatively, if you’re using a package manager like apt-get or yum, you can install the php-bcmath package.

### Why am I experiencing precision loss with PHP BCMath?

Precision loss in PHP BCMath can occur due to several reasons. One common reason is the incorrect usage of the scale parameter, which determines the number of decimal places in the result. If the scale is set too low, the result may be rounded, leading to precision loss. Another reason could be the use of non-integer values, as BCMath functions only support integer arithmetic.

### How can I prevent precision loss in PHP BCMath?

To prevent precision loss in PHP BCMath, you should always set the scale parameter to a value that is high enough to accommodate the precision of your calculations. Additionally, you should avoid using non-integer values, as BCMath functions only support integer arithmetic. If you need to perform calculations with non-integer values, you can convert them to integers by multiplying with a power of 10.

### Can I use BCMath functions with floating-point numbers?

No, BCMath functions only support integer arithmetic. If you need to perform calculations with floating-point numbers, you can convert them to integers by multiplying with a power of 10. For example, to multiply 1.23 by 4.56, you can convert them to integers as 123 and 456, perform the multiplication, and then divide the result by 10000.

### What is the difference between BCMath and GMP?

BCMath and GMP are both extensions in PHP that support arbitrary precision arithmetic. However, there are some differences between them. BCMath functions operate on numbers represented as strings, while GMP functions operate on numbers represented as resources. Additionally, GMP supports more operations than BCMath, including bitwise operations and random number generation.

### How can I compare two numbers using BCMath?

You can compare two numbers using the bccomp function in BCMath. This function returns 0 if the two numbers are equal, 1 if the first number is larger, and -1 if the second number is larger. The scale parameter can be used to specify the number of decimal places to compare.

### How can I convert a floating-point number to a string for use with BCMath?

You can convert a floating-point number to a string using the number_format function in PHP. This function returns a string representation of the number with a specified number of decimal places. The resulting string can then be used with BCMath functions.

### Can I use BCMath functions in a loop?

Yes, you can use BCMath functions in a loop. However, you should be aware that BCMath functions are slower than native PHP arithmetic operations. Therefore, if you’re performing a large number of calculations in a loop, you may want to consider using native PHP operations for performance reasons.

### Are there any alternatives to BCMath for high precision arithmetic in PHP?

Yes, there are several alternatives to BCMath for high precision arithmetic in PHP. These include the GMP extension, which supports a wider range of operations than BCMath, and the Decimal extension, which provides a decimal arithmetic class that supports arbitrary precision and correct rounding.

Aleksey is a professional Software Engineer from Ukraine with Master's degree in Applied Math. He worked on different positions from junior frontend developer to head of development department. Now he is working in product company on Bitcoin projects and teaching Computer Science in National Technical University in Kharkov, Ukraine. He likes contributing to open source projects and to learn a new things (Coursera and Edx are his favourite places)