How to Properly Retrieve Integer and Numeric Data Types from MySQL Using PHP?

How to Retrieve Integer and Numeric Data Types Correctly from MySQL Using PHP

When querying a MySQL database using PHP, it's crucial to ensure that integer and numeric data types are returned as such, rather than as strings. This is essential for data integrity and compatibility with other services.

Unfortunately, by default, the PHP-MySQL native driver casts all query results to strings. This issue has been the subject of much debate, with some claiming it's an implementation limitation and others arguing it's incorrect. However, the key factor to consider is the driver being used.

The Solution: Using the MySQLnd Driver

To resolve this issue, it's necessary to switch from the native MySQL driver to the mysqlnd driver. The mysqlnd driver supports returning integer and numeric types correctly.

How to Install and Check for mysqlnd

On Ubuntu, you can install the mysqlnd driver using the following commands:

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

To verify that mysqlnd is being used, check the output of php -i. The pdo_mysql section should now include a reference to mysqlnd.

PDO Settings

Additionally, ensure that the following PDO settings are properly configured:

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

Returned Values

Once the mysqlnd driver is in use and the appropriate PDO settings are applied, query results will be returned according to the following rules:

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

Example

The following example illustrates the correct behavior of integer and numeric data retrieval:

$pdo = new PDO(...);
$stmt = $pdo->prepare('SELECT * FROM table');
$stmt->execute();
$result = $stmt->fetch(PDO::FETCH_OBJ);

var_dump($result);

This would output an object with the following properties:

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 Properly Retrieve Integer and Numeric Data Types from MySQL Using PHP?. For more information, please follow other related articles on the PHP Chinese website!