3

I am guessing this has came up before, but I couldn't find the answer to my question. Here is a little code snippet:

    $stmt = $this -> db -> query("
        SELECT
          `Field`
        FROM
          `Table`
        WHERE
          (`ID` = 33608)");
    var_dump($stmt -> fetch());

And this is the result I get:

    array(1) { ["Field"]=> float(1.7999999523163) }

However, the data in the MySQL database is 1.8. The type of the field is float(7,4). $this->db is a PDO object. I have recently migrated to PDO (from AdoDB), and this code was working fine before. I am not sure what went wrong here. Could you point me in the right direction? Thanks!

Improve this question
3
  • I've been unable to reproduce it. Do you pass any particular setting in the PDO constructor? Commented Jun 11, 2013 at 16:11
  • The MySQL driver for PDO returns float or string depending on the PDO::ATTR_EMULATE_PREPARES setting, but I still get either float(18) or string(7) "18.0000" :-? Commented Jun 11, 2013 at 16:25
  • This is what I use to initialize PDO (sorry, I don't know how to add a code block here): try { $pdo = new Db("mysql:host=".DBHOST.";dbname=".DBNAME.";charset=utf8", DBUSER, DBPASS); $pdo -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $pdo -> setAttribute(PDO::ATTR_EMULATE_PREPARES, false); $pdo -> setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); } catch (PDOException $e) { die('DB Error'); } Commented Jun 11, 2013 at 18:43

2 Answers 2

Reset to default
4

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.

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

Comments

1

You have to use DECIMAL field type instead of FLOAT if you want accurate values.
And PDO has nothing to do with it. It's rather related to how computers works in general.

Improve this answer

1 Comment

Using DECIMAL will have the undesirable side effect that PDO will return the value as STRING no matter if you have ATTR_EMULATE_PREPARES set to FALSE and you are using the proper mysqlnd driver. So, depending on his needs, he's better stuck with FLOAT.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.