How to Ensure Correct Integer and Numeric Data Type Retrieval from MySQL in PHP?

How to Retrieve Integers and Numerics as Typed Data in PHP from MySQL

You may encounter a situation where a MySQL query returns string data types for integer and numeric columns in PHP, despite setting the "PDO::ATTR_STRINGIFY_FETCHES" option to false. This discrepancy stems from driver implementation issues.

Using the mysqlnd Driver

The solution lies in ensuring the correct driver is used, namely the mysqlnd driver. When viewing "php -i", you should see "mysqlnd" explicitly mentioned in the "pdo_mysql" section. If it's not there, follow these steps to install it on Ubuntu:

1. Remove the native MySQL driver:

   apt-get remove php5-mysql

2. Install the mysqlnd driver:

   apt-get install php5-mysqlnd

3. Restart Apache2:

   service apache2 restart

PDO Settings

Confirm that the PDO settings are suitable:

• PDO::ATTR_EMULATE_PREPARES should be false.
• PDO::ATTR_STRINGIFY_FETCHES should be false.

Returned Value Types

• Floating-point types (FLOAT, DOUBLE) are returned as PHP floats.
• Integer types (INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT) are returned as PHP integers.
• Fixed-Point types (DECIMAL, NUMERIC) are returned as strings.

For example, consider the following returned object:

    object(stdClass)[915]
      public 'integer_col' => int 1
      public 'double_col' => float 1.55
      public 'float_col' => float 1.5
      public 'decimal_col' => string '1.20' (length=4)
      public 'bigint_col' => string '18446744073709551615' (length=20)

The above is the detailed content of How to Ensure Correct Integer and Numeric Data Type Retrieval from MySQL in PHP?. For more information, please follow other related articles on the PHP Chinese website!