Why are MySQL numeric columns returned as strings in PHP, and how can I ensure they are returned as integers or floats?

How to Return Integer and Numeric Columns from MySQL as PHP Integers and Numerics

Problem Statement

Some PHP-based applications may encounter a problem where the results from MySQL queries are returned as strings instead of as the correct integer or numeric data types. This occurs despite setting the PDO attribute PDO::ATTR_STRINGIFY_FETCHES to false to prevent string casting.

Driver Considerations

It's crucial to note that the MySQL driver plays a significant role in determining whether numeric types are returned as integers or numerics. Some drivers, such as the MySQL native driver on Linux, do not fully support this feature.

However, the mysqlnd driver, which is available for Linux distributions through third-party repositories, does support this capability. Ensuring that the mysqlnd driver is being used in your PHP environment is essential.

Installing mysqlnd Driver on Ubuntu

To switch from the native MySQL driver to mysqlnd on Ubuntu, follow these steps:

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

Verifying Driver Usage

After installing mysqlnd, confirm that it's being used by running php -i. Look for the following entry in the pdo_mysql section:

PDO Driver for MySQL => enabled
Client API version => mysqlnd 5.0.10 - 20111026 - $Id: e707c415db32080b3752b232487a435ee0372157 $

PDO Settings

Ensure that the following PDO attributes are set correctly:

• PDO::ATTR_STRINGIFY_FETCHES: Set to false to prevent string casting.
• PDO::ATTR_EMULATE_PREPARES: Set to false to improve performance.

Returned Value Types

Once these settings are configured, MySQL will return numeric types as follows:

• 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 (bigints with values exceeding 64 bits may also return as strings)

For example, the following PHP code will output the expected data types:

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

$stmt = $pdo->prepare("SELECT * FROM table");
$stmt->execute();

$row = $stmt->fetch(PDO::FETCH_OBJ);

echo "<pre class="brush:php;toolbar:false">";
print_r($row);
echo "

";

Output:

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 MySQL numeric columns returned as strings in PHP, and how can I ensure they are returned as integers or floats?. For more information, please follow other related articles on the PHP Chinese website!