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:
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!