Why not PDO_MySQL return integer?

Question

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"
}

Answer 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.

Answer 2

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"
}