Why are my PHP MySQL numeric column results sometimes strings instead of integers or floats?

Returning Integers and Numerics as Types in PHP from MySQL

Despite setting PDO::ATTR_STRINGIFY_FETCHES to false, you may encounter issues where numeric columns are still returned as strings instead of integers or numerics. This behavior is often attributed to a driver implementation issue.

The mysqlnd Difference

The key to resolving this issue lies in ensuring that you are utilizing the mysqlnd driver for PHP. When viewing php -i, the absence of "mysqlnd" in the pdo_mysql section indicates that the native driver is being used instead.

Installing mysqlnd

On Ubuntu, install php5-mysqlnd instead of php5-mysql. To ensure a successful transition, follow these steps:

1. Remove the old driver: apt-get remove php5-mysql
2. Install the new driver: apt-get install php5-mysqlnd
3. Restart Apache: service apache2 restart

Verifying mysqlnd

Upon installing php5-mysqlnd, php -i should now explicitly mention "mysqlnd" in the pdo_mysql section.

PDO Settings

Set the following PDO attributes to ensure proper data handling:

• PDO::ATTR_EMULATE_PREPARES: false
• PDO::ATTR_STRINGIFY_FETCHES: false

Returned Values

With the correct driver and settings, numeric columns will be returned as appropriate PHP types:

• Floating-point types (FLOAT, DOUBLE): PHP floats
• Integer types (INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT): PHP integers
• Fixed-Point types (DECIMAL, NUMERIC): strings
  (Note: BIGINTs exceeding a 64-bit signed int will return as strings on 64-bit systems.)

For example, the following data:

+-----------+-----------+----------+-------------+--------------+
| integer_col | double_col | float_col | decimal_col | bigint_col    |
+-----------+-----------+----------+-------------+--------------+
| 1          | 1.55      | 1.5      | 1.20        | 18446744073709551615 |
+-----------+-----------+----------+-------------+--------------+

Will be returned as:

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 Why are my PHP MySQL numeric column results sometimes strings instead of integers or floats?. For more information, please follow other related articles on the PHP Chinese website!