Why Are My MySQL Numeric Values Returned as Strings in PHP, and How Can I Fix It?

Solving the Discrepancy in Returning Integer and Numeric Values from MySQL in PHP

When retrieving data from MySQL using PHP, it can be puzzling to encounter type inconsistencies where integer and numeric columns are being returned as strings instead of their intended data types.

The Driver Issue

It is crucial to determine the underlying driver being used for database connectivity. The problem often stems from using the native MySQL driver, which lacks the ability to return numeric types as integers.

Identifying the MySQLnd Driver

To ascertain if you're using the native MySQL driver, inspect the output of php -i. If no mention of "mysqlnd" is present in the pdo_mysql section, you're likely using the native driver.

Switching to the MySQLnd Driver

The solution lies in switching to the mysqlnd driver, which supports returning numeric types correctly. On Ubuntu, this can be achieved by:

sudo apt-get remove php5-mysql
sudo apt-get install php5-mysqlnd
sudo service apache2 restart

PDO Settings

Once the mysqlnd driver is installed, ensure the following PDO settings are set correctly:

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);

Returned Values

After implementing the above steps, MySQL query results will return numeric types as expected:

• Floating-point types (FLOAT, DOUBLE) as PHP floats
• Integer types (INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT) as PHP integers
• Fixed-Point types (DECIMAL, NUMERIC) as strings (note: BIGINTs exceeding 64 bits will also return as strings)

An example of the returned values would be:

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 MySQL Numeric Values Returned as Strings in PHP, and How Can I Fix It?. For more information, please follow other related articles on the PHP Chinese website!