How to Ensure Integer and Numeric Data Types Are Returned Correctly from MySQL in PHP?

How to Retrieve Integer and Numeric Data Types as Integers and Numerics in PHP from MySQL

When querying MySQL using PHP, it's common to encounter an issue where integer and numeric columns are returned as string types. Despite configuring "PDO::ATTR_STRINGIFY_FETCHES" to false, this problem persists.

Solutions

1. Check for mysqlnd Driver:
   Ensure you are using the mysqlnd driver instead of the native MySQL driver. In PHP's "php -i" output, look for the pdo_mysql section. The absence of "mysqlnd" indicates that the native driver is being used.
2. Install the mysqlnd Driver (Ubuntu):
   Remove the native driver with "apt-get remove php5-mysql" and install the mysqlnd driver with "apt-get install php5-mysqlnd."
3. Confirm mysqlnd Usage:
   After restarting Apache, check if "mysqlnd" is mentioned in the pdo_mysql section of PHP's "php -i" output.

4. Configure PDO:
   Ensure that the following PDO attributes are set:

   • PDO::ATTR_EMULATE_PREPARES: false
   • PDO::ATTR_STRINGIFY_FETCHES: false

Returned Types

When using the mysqlnd driver, integer and numeric column values are returned as the following PHP data types:

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

Example

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

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

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

echo $result->integer_col; // int
echo $result->double_col; // float
echo $result->decimal_col; // string
echo $result->bigint_col; // string (since value exceeds 64-bit signed int)

Output:

1
1.55
1.20
18446744073709551615

The above is the detailed content of How to Ensure Integer and Numeric Data Types Are Returned Correctly from MySQL in PHP?. For more information, please follow other related articles on the PHP Chinese website!