how to get numeric types from mysql using PDO

Question

I'm using PDO and mysql, for some reason when getting values from the DB 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 db?

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) mysql**nd** (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...

Answer 2

If you're referring to MySQL's type system, I think you will want to have a look at the PDOStatement->bindValue() function. This call has an optional third parameter that accepts an explicit datatype: http://us2.php.net/manual/en/pdostatement.bindparam.php#pdostatement.bindparam.examples

Answer 3

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