How to get numeric types from MySQL using PDO?

Question

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?

Foul

See for answer https://mcmap.net/q/103229/-how-do-i-return-integer-and-numeric-columns-from-mysql-as-integers-and-numerics-in-php – Sciurine

Answer 1

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

Prevention

On Ubuntu try sudo apt-get install php5-mysqlnd – Dissepiment

I too had to do what @Dissepiment said here to have php use mysqlnd under the hood. +1 for his comment. – Bike

Fast forward to 2016, to have correct data types returned from MySQL, for PHP 7 on CentOS/RHEL: yum install php70w-mysqlnd – Cheeks

I'm baffled by this, especially for integers... Anybody has an explanation of this extremely strange (to me) default behaviour ? – Vierno

Answer 2

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... ;)

Gavage

If you know a field is going to be numeric, you can always add zero to the field. $values = $stm->fetch(); $values['numfield'] += 0; // Will force the value to be numeric. This should work across all php versions. – Panarabism

This should be the correct answer. I have mysqlnd enabled and still get numbers converted as strings. Just followed this answer and got the numbers correctly as they should – Invade

"it's good practice to use prepared statements" This is misleading. It's only good practice to know about prepared statements in extensive detail. There's no substitute for knowing what you're doing. From that, can you know when and when not to use it. Also in this case we're comparing client side prepared queries versus server side prepared queries. The reason to emulate prepared queries is that it can provide other features and major performance enhancements. Which is best depends very much on circumstances. My libraries all allow alternation so you can use one or the other based on need. – Derisive

Simple example, server side prepared queries require more round trips. They can also be leaky, they need to be destroyed, unless you want to persist them which is then one possible advantage except it wont work with dynamic queries and not well with non-persistent connections. Emulated in those cases reduces RTT. Emulated may also be used to support things such as LIMIT ?, ? which MySQL last time I checked didn't allow in prepared statements. In those cases some people might combine both layers. In this case emulation has a downside, loss of native types. – Derisive

Answer 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);

Os

Note that DECIMAL type will still be sent as string even with this configuration. Using mysqlnd version 5.0.10 here. – Peavy

@Peavy that is done on purpose to prevent the decimal from becoming an unreliable float in PHP. – Bathtub

Answer 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

Furtherance

I've been looking how to change to mysqlnd on HostEurope for a day, even bugged their support. You just saved me, man. – Monomer

three days wondering why mysqlnd was checked but PDO didnt recognized it. Thanks – Kidwell