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

I have the exactly same question, but for MS SQL Server. Is there a strongly typed library for that, akin to mysqlnd? The [latest drivers](https://www.microsoft.com/en-us/download/details.aspx?id=20098) I could find return all strings. — GSerg, Jan 09 '16 at 11:31

score 132 · Accepted Answer · edited Jun 07 '16 at 06:25

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)

Note that even with this version of mysqlnd enabled, DECIMALS will still be sent as strings... (although FLOATS will be returned as floats!) :/ Source: I tested it. — Pere, Nov 04 '14 at 12:41
Also note that PDO::ATTR_STRINGIFY_FETCHES has nothing to do with it - I tried and it has no effect on the result, and in [this post](http://stackoverflow.com/questions/1197005/how-to-get-numeric-types-from-mysql-using-pdo) user @Josh Davis says it's unrelated to MySQL. — Pere, Nov 04 '14 at 12:55
Thanks, I've investigated further and updated with notes about the DECIMAL type. — stephenfrank, Nov 04 '14 at 14:53
@pere this is the right behaviour. DECIMAL is not a numeric type. It's a string format for numbers. So you can return: `0.30` as `"0.30"` instead of `0.3`. Decimal are used for this... so this is right behaviour — Max Cuttins, Mar 28 '18 at 18:10
why is it a damn compromise? I need `PDO::ATTR_EMULATE_PREPARES` to use a named parameter more then once — Gaby_64, Dec 16 '19 at 19:44

score 5 · Answer 2 · answered Dec 11 '18 at 18:39

This accepted answer works, and seems to be the most popular answer on Google for this question. My issue is that I need to deploy an application to multiple environments and I don't always have the ability to get the right driver installed, plus I need decimals to be numeric, not strings. So I created a routine to type case before JSON encoding, which is easily modified to suit. It's kinda like nuking it from orbit.

First, use "show columns from" to get the columns from the table. mysql query "SHOW COLUMNS FROM table like 'colmunname'":questions

$query = 'SHOW COLUMNS FROM ' . $table; //run with mysqli or PDO

Then get the types into an array indexed by the column name to make it easy to iterate over. Assumes results set from show columns is in a variable named $columns_from_table. http://php.net/manual/en/function.array-column.php

$column_types = array_column( $columns_from_table, 'Type', 'Field');

Next we want to remove the parenthetical from the type, which is going to be something like varchar(32) or decimal(14,6)

foreach( $column_types as $col=>$type )
{
    $len = strpos( $type, '(' );
    if( $len !== false )
    {
        $column_types[ $col ] = substr( $type, 0, $len );
    }
}

Now we have an associated array with the column name as the index and the formatted type as the value, for example:

Array
(
    [id] => int
    [name] => varchar
    [balance] => decimal
    ...
)

Now, when you do your select from your table you can iterate over the results and cast the value to the appropriate type:

foreach( $results as $index=>$row )
{
    foreach( $row as $col=>$value )
    {
        switch( $column_types[$col] )
        {
            case 'decimal':
            case 'numeric':
            case 'float':
            case 'double':

                $row[ $col ] = (float)$value;
                break;

            case 'integer':
            case 'int':
            case 'bigint':
            case 'mediumint':
            case 'tinyint':
            case 'smallint':

                $row[ $col ] = (int)$value;
                break;
        }

    }
    $results[ $index ] = $row;
}

The switch statement can be easily modified to suit, and could include date functions, etc. For example, in my case a 3rd party is pushing currency values into the database as a decimal, but when I grab that data and need to return it as JSON, I need those to be numbers, not strings.

Tested with PHP 7, 7.2 and JQuery 2, 3.

score 2 · Answer 3 · answered Sep 03 '21 at 09:30

2

In php-8.1.0RC1/ are native types even for EMULATE_PREPARES, https://github.com/php/php-src/tree/c18b1aea289e8ed6edb3f6e6a135018976a034c6 So in future it should be possible to omit PDO::ATTR_EMULATE_PREPARES => false, option.

answered Sep 03 '21 at 09:30

Adam Mátl

116
4