How do I return integer and numeric columns from MySQL as integers and numerics in PHP?

Question

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'…

Accepted 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". The `pdo_mysql` section will have something like this:

```
pdo_mysql

PDO Driver for MySQL => enabled Client API version => 5.1.72
```

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):

- Remove the old driver: `apt-get remove php5-mysql`

- Install the new driver: `apt-get install php5-mysqlnd`

- Restart apache2: `service apache2 restart`

How do I check that the driver is being used?

Now `php -i` will mention "mysqlnd" explicitly in the `pdo_mysql` section:

```
pdo_mysql

PDO Driver for MySQL => enabled
Client API version => mysqlnd 5.0.10 - 20111026 - $Id:      e707c415db32080b3752b232487a435ee0372157 $
```

PDO settings

Ensure that `PDO::ATTR_EMULATE_PREPARES` is `false` (check your defaults or set it): `$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);`

Ensure that `PDO::ATTR_STRINGIFY_FETCHES` is `false` (check your defaults or set it): `$pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);`

Returned values

- Floating-point types (FLOAT, DOUBLE) are returned as PHP floats.

- Integer types (INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT †) are returned as PHP integers.

- Fixed-Point types (DECIMAL, NUMERIC) are returned as strings.

† 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)

```
    object(stdClass)[915]
      public 'integer_col' => int 1
      public 'double_col' => float 1.55
      public 'float_col' => float 1.5
      public 'decimal_col' => string '1.20' (length=4)
      public 'bigint_col' => string '18446744073709551615' (length=20)
```

How do I return integer and numeric columns from MySQL as integers and numerics in PHP?

Solution

Problem

Related problems