How to get numeric types from MySQL using PDO?

I'm using PDO and MySQL, for some reason when getting values from the database that are int type, the PDOStatement is returning a string representation of the number and not a value of numeric type. How do I prevent this from happening?

I noticed there is a attribute of the PDO class: PDO::ATTR_STRINGIFY_FETCHES that is supposed to take care of this but, when trying to modify it, it throws an error saying the attribute is not valid for MySQL driver.

Is it normal to get strings instead of numbers when consulting a database?

Solution 1:

To answer your last question first, "yes," unfortunately it's normal to receive numbers as strings. As the manual quoted by Pascal says, mysqlnd (PHP 5.3) will return native data types from prepared statements, provided you turn off the prepared statement emulation from PDO.

new PDO($dsn, $user, $pass, array(
    PDO::ATTR_EMULATE_PREPARES => false
))

PDO::ATTR_STRINGIFY_FETCHES is unrelated to MySQL.

If you look at the bright side, it's good practice to use prepared statements anyway, so... ;)

Solution 2:

I don't think having "numbers" can be done in PHP 5.2 :-(

In PHP 5.3, it becomes possible, if I remember correctly, when you are using the new (new as in PHP >= 5.3) mysqlnd (MySQL Native Driver) driver.

Well, after more digging through my bookmarks I found this article about mysqlnd : PDO_MYSQLND: The new features of PDO_MYSQL in PHP 5.3

It says this (quote) :

Advantages of using mysqlnd for PDO

mysqlnd returns native data types when using Server-side Prepared Statements, for example an INT column is returned as an integer variable not as a string. That means fewer data conversions internally.

But this is PHP 5.3 only (provided your version of PHP 5.3 is compiled with mysqlnd (and not old libmysql)), and seems to only be the case for prepared statements :-(

Sorry...

A solution would be to have, on the PHP-side, a mapping-system (like an ORM -- see Doctrine ; just as an example of ORM : I don't know if it does what you're asking) to convert results coming from the DB to PHP datatypes...

And yes, this is bad if you want to use operators like === and !==, which are type-sensitive...

Solution 3:

Pascal's answer is correct. I had some trouble it making it all work. Here is what you need to do.

First, make sure you have mysqlnd installed and activated. Look at php --info. In the pdo_mysql section, it should look like:

pdo_mysql

PDO Driver for MySQL => enabled
Client API version => mysqlnd 5.0.8-dev - 20102224 - $Revision: 321

If instead of seeing mysqlnd in the Client API version, you see a line like...

Client API version => 5.5.29

...then you don't have mysqlnd installed and going. Take care of that first.

Second, PDO uses emulated prepared statements by default for all MySQL connections. Ridiculous, but there it is. And you will get native data types only if you use real prepared statements (called "server-side prepared statements" in the linked blog post, which is now here). So you must set PDO::ATTR_EMULATE_PREPARES to false, like so:

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

Lastly, make sure that you have not set PDO::ATTR_STRINGIFY_FETCHES to true.

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

Solution 4:

You might have mysqlnd installed, but it doesn't mean it is ready for use by the regular PDO extension (pdo_mysql), this is more often the case on shared hosting, so make sure to enable the nd_pdo_mysql extension, and also to disable other extension pdo_mysql as it might create an conflict.

@screenshot