The problem is that a DB query should return integer data types in PHP for integer columns. Instead the query returns every column as a string type.
I've ensured that "PDO::ATTR_STRINGIFY_FETCHES" if false just to make sure results aren't being cast to string.
Answers that I've seen:
- It can't be done
- Nope, it's working on Mac OS X installed PHP/MySQL
- Type cast all your values in your code
- Nope, I won't be doing that
- Don't worry about it, PHP is loosely typed
- My data is output as JSON and is consumed by many other services, some require the data in the correct format
From my research I understand that this is a driver implementation issue.
Many sources claim that the MySQL native driver does not support returning numeric types. This doesn't seem true since it works on Mac OS X. Unless they mean to say that "the MySQL native driver on Linux doesn't support the feature".
This implies that there is something special about the driver/environment I have installed on Mac OS X. I've been trying to identify the differences in order to apply a fix but I'm limited by my knowledge of how to check these things.
The differences:
- PHP on OS X was compiled and installed via Home Brew
- PHP on Ubuntu was installed via "apt-get install php5-dev"
- PHP on OS X is connecting to a MySQL server also running on OS X
- Server version: 5.1.71-log Source distribution
- PHP on Ubuntu is connecting to a Rackspace Cloud Database
- Server version: 5.1.66-0+squeeze1 (Debian)
Ubuntu environment
-
Version: 10.04.1
-
PHP 5.4.21-1+debphp.org~lucid+1 (cli) (built: Oct 21 2013 08:14:37)
-
php -i
pdo_mysql
PDO Driver for MySQL => enabled
Client API version => 5.1.72
Mac OS X environment
-
10.7.5
-
PHP 5.4.16 (cli) (built: Aug 22 2013 09:05:58)
-
php -i
pdo_mysql
PDO Driver for MySQL => enabled
Client API version => mysqlnd 5.0.10 – 20111026 – $Id: e707c415db32080b3752b232487a435ee0372157 $
PDO flags used
PDO::ATTR_CASE => PDO::CASE_NATURAL,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_ORACLE_NULLS => PDO::NULL_NATURAL,
PDO::ATTR_STRINGIFY_FETCHES => false,
PDO::ATTR_EMULATE_PREPARES => false,
Best Answer
The solution is to ensure that you are using the mysqlnd driver for php.
How do you know that you're not using mysqlnd?
When viewing
php -i
, there will be no mention of "mysqlnd". Thepdo_mysql
section will have something like this:How do you install it?
Most installation guides for L/A/M/P suggest
apt-get install php5-mysql
but the native driver for MySQL is installed by a different package:php5-mysqlnd
. I found that this was available with the ppa:ondrej/php5-oldstable.To switch to the new driver (on Ubuntu):
apt-get remove php5-mysql
apt-get install php5-mysqlnd
service apache2 restart
How do I check that the driver is being used?
Now
php -i
will mention "mysqlnd" explicitly in thepdo_mysql
section:PDO settings
Ensure that
PDO::ATTR_EMULATE_PREPARES
isfalse
(check your defaults or set it):$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
Ensure that
PDO::ATTR_STRINGIFY_FETCHES
isfalse
(check your defaults or set it):$pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);
Returned values
† BIGINTs with a value greater than a 64 bit signed int (9223372036854775807) will return as a string (or 32 bits on a 32 bit system)