0

How can I fetch data from pdo-mysql retaining native type column of mysql?

example: I retrieve some data from a table. This table contain id column that is a integer column. When fetch data from this table, php result array has that id field like a string, not integer.

array(3) { ["id"]=> string(2) "11" 
           ["name"]=> string(24) "test name"           
           ["surname"]=> string(2) "test suname"
          }

I know that with mysqlnd driver I can obtain this as default type in php.

I use php 5.3 and mysqlnd is enable:

shell$ php -i | grep -i mysql     

MySQL Support => enabled
Client API version => mysqlnd 5.0.8-dev - 20102224 - $Revision: 308673 $
mysql.allow_local_infile => On => On
mysql.allow_persistent => On => On
mysql.connect_timeout => 60 => 60
mysql.default_host => no value => no value
mysql.default_password => no value => no value
mysql.default_port => 3306 => 3306
mysql.default_socket => /var/mysql/mysql.sock => /var/mysql/mysql.sock
mysql.default_user => no value => no value
mysql.max_links => Unlimited => Unlimited
mysql.max_persistent => Unlimited => Unlimited
mysql.trace_mode => Off => Off
mysqli
MysqlI Support => enabled
Client API library version => mysqlnd 5.0.8-dev - 20102224 - $Revision: 308673 $
mysqli.allow_local_infile => On => On
mysqli.allow_persistent => On => On
mysqli.default_host => no value => no value
mysqli.default_port => 3306 => 3306
mysqli.default_pw => no value => no value
mysqli.default_socket => /var/mysql/mysql.sock => /var/mysql/mysql.sock
mysqli.default_user => no value => no value
mysqli.max_links => Unlimited => Unlimited
mysqli.max_persistent => Unlimited => Unlimited
mysqli.reconnect => Off => Off
mysqlnd
mysqlnd => enabled
Version => mysqlnd 5.0.8-dev - 20102224 - $Revision: 308673 $
PDO drivers => mysql, sqlite, sqlite2
pdo_mysql
PDO Driver for MySQL => enabled
Client API version => mysqlnd 5.0.8-dev - 20102224 - $Revision: 308673 $
pdo_mysql.default_socket => /var/mysql/mysql.sock => /var/mysql/mysql.sock

How can I fetch data with native type?

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
paganotti
  • 5,591
  • 8
  • 37
  • 49
  • In the end, it's a duplicate of [how to get numeric types from mysql using PDO](http://stackoverflow.com/questions/1197005/how-to-get-numeric-types-from-mysql-using-pdo) and the accepted answer is just great. – Álvaro González Apr 11 '12 at 10:38

1 Answers1

4

There's the PDO::ATTR_STRINGIFY_FETCHES parameter, which in theory controls this for numbers (as far as I know there's no way to get native objects with dates with any MySQL driver):

PDO::ATTR_STRINGIFY_FETCHES: Convert numeric values to strings when fetching. Requires bool.

You can change it with PDO::setAttribute(). However, drivers are always free to implement specific PDO features and it seems that MySQL ones do not support this.

Said that, some users have reported that stringified fetches are a side effect of running queries in emulated parameters modes, so you can always disable it:

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

Beware that enabling/disabling such mode has additional side effects, including:

  • Not being able to use named parameters more than once
  • Getting numeric parameters quoted (particularly, that breaks the LIMIT clause)
Álvaro González
  • 142,137
  • 41
  • 261
  • 360
  • I try setAttribute(\PDO::ATTR_STRINGIFY_FETCHES, false); for obtain fetch data in corresponding php datatypes but I obtain already id field as string and not as integer. – paganotti Apr 11 '12 at 10:19
  • Confirmed. I have the impression that the driver does not support this feature. In that case, you'll have to int_val() or float_val() yourself. – Álvaro González Apr 11 '12 at 10:35
  • 1
    I discover this: setAttribute(\PDO::ATTR_EMULATE_PREPARES, false); Try it. So now, I fetch data with native php datatype. – paganotti Apr 11 '12 at 11:01
  • 1
    Above comment is right, please modify the answer. Add that: `$PDO->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);` is also required. – Ciantic Nov 30 '12 at 12:37