How to Bind Decimal/Double/Float Values with Pdo in PHP

What is the best way to bind decimal / double / float values with PDO in PHP?

AFAIK PDO::PARAM_STR is the way to go.

PDO::PARAM for type decimal?

There isn't any PDO::PARAM for decimals / floats, you'll have to use PDO::PARAM_STR.

PDO STRINGIFY_FETCHES and float values in php

Same problem as PHP PDO query returns inaccurate value for FLOAT fields

The next time i will use DECIMAL instead of FLOAT.

PHP PDO query returns inaccurate value for FLOAT fields

As documented under Floating-Point Types (Approximate Value) - FLOAT, DOUBLE:

MySQL performs rounding when storing values, so if you insert 999.00009 into a FLOAT(7,4) column, the approximate result is 999.0001.

Because floating-point values are approximate and not stored as exact values, attempts to treat them as exact in comparisons may lead to problems. They are also subject to platform or implementation dependencies. For more information, see Section C.5.5.8, “Problems with Floating-Point Values”

For maximum portability, code requiring storage of approximate numeric data values should use FLOAT or DOUBLE PRECISION with no specification of precision or number of digits.

Therefore, upon inserting 1.8 into your database, MySQL rounded the literal to 001.8000 and encoded the closest approximation to that number in binary32 format: i.e. 0x3FE66666, whose bits signify:

Sign           : 0b0

Biased exponent: 0b01111111
               =   127 (representation includes bias of +127, therefore exp = 0)

Significand    : 0b[1.]11001100110011001100110
                    ^ hidden bit, not stored in binary representation
               =   [1.]7999999523162841796875

This equates to:

 (-1)^0 * 1.7999999523162841796875 * 2^0
=         1.7999999523162841796875

This is the value that is returned by MySQL to the client. It would appear that AdoDB then inspected the column's datatype and rounded the result accordingly, whereas PDO does not.

If you want exact values, you should use a fixed point datatype, such as DECIMAL.

Inserting PHP float/decimal values into MySQL

You are telling php to cast $match_id and $rating to integer. You should use:

$sql->bind_param("id", $match_id, $rating);

instead of

$sql->bind_param("ii", ...

Numeric value out of range for a DOUBLE(5,5)

If you read the documentation, you see That Double 5,5 means, that you have defined the Maximum Digits to 5 and this 5 are after the decimal Point.

Official Doc:

MySQL permits a nonstandard syntax: FLOAT(M,D) or REAL(M,D) or DOUBLE
PRECISION(M,D). Here, (M,D) means than values can be stored with up to
M digits in total, of which D digits may be after the decimal point.
For example, a column defined as FLOAT(7,4) will look like -999.9999
when displayed. MySQL performs rounding when storing values, so if you
insert 999.00009 into a FLOAT(7,4) column, the approximate result is
999.0001.

What you need to store your value is Double (10,5)

---

Related Topics