How to Retrieve Integer and Numeric Columns as Their Native Types from MySQL in PHP?

How do I return integer and numeric columns from MySQL as integers and numerics in PHP?

When working with MySQL databases in PHP, ensuring that integer and numeric columns are correctly retrieved and represented is crucial. The PDO extension in PHP provides a way to do this, but it can be problematic if the MySQL native driver is not being used.

Why integer columns are returned as strings

The MySQL native driver lacks support for returning numeric values as integers and numerics. This means that, by default, all columns are retrieved as strings. Even with the PDO flag PDO::ATTR_STRINGIFY_FETCHES set to false, the driver's behavior does not change.

How to fix it

The solution is to switch to the mysqlnd driver for PHP. This driver supports returning integer and numeric values in their native PHP types.

Installation

To install the mysqlnd driver on Ubuntu, you can use the following steps:

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

Verification

To verify that the mysqlnd driver is being used, run php -i. The output should now include "mysqlnd" in the pdo_mysql section.

PDO settings

Ensure that the following PDO settings are set correctly:

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

Returned values

With the mysqlnd driver, the following return types are used:

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

Example

After switching to the mysqlnd driver, you will be able to retrieve integer and numeric values correctly:

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

$result = $pdo->query('SELECT * FROM table');
$row = $result->fetch(PDO::FETCH_OBJ);

echo $row->integer_col; // 1 (int)
echo $row->double_col; // 1.55 (float)
echo $row->decimal_col; // '1.20' (string)
echo $row->bigint_col; // '18446744073709551615' (string)

The above is the detailed content of How to Retrieve Integer and Numeric Columns as Their Native Types from MySQL in PHP?. For more information, please follow other related articles on the PHP Chinese website!