I'm migrating my PHP codes from mysql (deprecated in 5.5) to PDO_MySQL. However, mysql_fetch_row returns integer while PDOStatement::fetch returns strings for numbers. How can I make PDO behaves like the former one?
Result of mysql_fetch_row:
array(1) {
["id"]=>
int(1)
}
Result of PDOStatement::fetch:
array(1) {
["id"]=>
string(1) "1"
}
Set PDO::ATTR_EMULATE_PREPARES to false, if you really need it with loosely-typed PHP
If mysql_fetch_row returns you int for SUM either (I never cared to check) - then it does some magic like if (ctype_digit($val)) $row[$key] = (int)$val; - so you can do in your DBAL
As far as I understand the way prepared statements works, it uses the same packet structure for either sending and retrieving data, and this packet contains data type.
It looks like that server can return data in 2 formats - native and mysqlnd, depends on the request type. A latter one can be interpreted by client library to cast resulting value.
SELECT SUM(.... Also, as its name indicates, will advantages of prepared statements be invalid with this option?
mysql returns string on SUM(.. as well.For the records, PDO provides a feature that would alter this, PDO::ATTR_STRINGIFY_FETCHES:
Convert numeric values to strings when fetching. Requires
bool.
The rationale behind the setting is that database engines can handle very large numbers (Oracle, for instance, allows 38-digit numbers) and PHP cannot. Retrieving those numbers as strings is a method to keep safe and prevent data loss.
Unluckily, the MySQL driver does not support it:
<?php
$pdo = new PDO('mysql:host=localhost;dbname=test', 'test', 'test');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);
$sql = 'SELECT 1 AS integer_number, 3.14 AS floating_point';
$res = $pdo->query($sql);
while($row = $res->fetch(PDO::FETCH_ASSOC)){
var_dump($row);
}
array(2) {
["integer_number"]=>
string(1) "1"
["floating_point"]=>
string(4) "3.14"
}
PDO::ATTR_STRINGIFY_FETCHES, as everything is returned as a string data-type already, meaning this attribute is fairly useless with that driver.You can use json_encode and json_decode with JSON_NUMERIC_CHECK option to retrieve your array with numeric types:
$my_array = json_decode(json_encode( $my_array, JSON_NUMERIC_CHECK ));
