PHP + PDO + MySQL: how can I return integer and number columns from MySQL as integers and numbers in PHP?

Question

I saw how this question was repeated several times when the stack overflowed, but none of them investigated the problem (or at least in a way that is useful to me).

The problem is that the database query must return integer data types in PHP for whole columns. Instead, the query returns each column as a string type.

I ensured that "PDO :: ATTR_STRINGIFY_FETCHES" if false, just to make sure that the results are not passed to the string.

The answers I saw:

• It's impossible.
  • No, it works on Mac OS X installed by PHP / MySQL
• Enter all your values ​​in your code.
  • No i won't do it
• Don’t worry about it, PHP is free to print.
  • My data is output as JSON and consumed by many other services, some require data in the correct format

From my research, I understand that this is a problem with the implementation of drivers.

Many sources claim that the native MySQL driver does not support returning numeric types. This is not like it works on Mac OS X. If they don't want to say that "the native MySQL driver on Linux does not support this function."

This means that there is something special about the driver / environment that I installed on Mac OS X. I tried to spot the differences to apply the fix, but I am limited to my knowledge of how to test these things.

Differences:

• PHP on OS X has been compiled and installed via Home Brew
• PHP on Ubuntu was installed via "apt-get install php5-dev"
• PHP on OS X connects to a MySQL server also running on OS X
  • Server Version: 5.1.71-log Source Distribution
• PHP on Ubuntu connects to the 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

  MySQL PDO Driver => 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

  MySQL PDO driver => 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, 

Any help and experience would be appreciated :) I will definitely go there if I find an answer.

Answer 1

The solution is to make sure that you are using the mysqlnd driver for php.

How do you know that you are not using mysqlnd?

When viewing php -i , "mysqlnd" is not mentioned. The pdo_mysql section will have something like this:

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

How to 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 another package: php5-mysqlnd . I found that this is available using ppa: ondrej / php5-oldstable .

To switch to the new driver (on Ubuntu):

• Uninstall the old driver:
  apt-get remove php5-mysql
• Install the new driver:
  apt-get install php5-mysqlnd
• Restart apache2:
  service apache2 restart

How to verify that a driver is being used?

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

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

PDO Settings

Make sure PDO::ATTR_EMULATE_PREPARES is false (check the default settings or set it):
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

Make sure PDO::ATTR_STRINGIFY_FETCHES is false (check the default settings or set it):
$pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);

Return values

• Floating point types (FLOAT, DOUBLE) are returned when PHP floats.
• Integer types (INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT †) are returned as PHP integers.
• Fixed point types (DECIMAL, NUMERIC) are returned as strings.

† BIGINT with a value greater than the 64-bit signed int (9223372036854775807) will be returned as a string (or 32 bits in 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) 

Answer 2

This accepted answer works and seems to be Google's most popular answer to this question. My problem is that I need to deploy the application in several environments, and I do not always have the ability to install the driver I need, plus I need decimal numbers to be numeric, not string. Therefore, I created a routine to enter the case register before the JSON encoding, which is easily modified to meet the requirements. It's like dropping a core from orbit.

First, use the Show Columns From command to get the columns from the table. MySQL query "SHOW COLUMNS FROM a table such as 'colmunname'": questions

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

Then put the types in an array indexed by the column name to make it easier to repeat. It is assumed that the result set from the columns of the show 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'); 

Then we want to remove the bracket from the type, which will 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 a linked array with the column name as index and formatted type as value, for example:

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

Now, when you make a choice from the 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 meet the requirements and can include date functions, etc. For example, in my case, the third party puts the currency values ​​in the database as a decimal number, but when I get this data and I need to return it as JSON, I need it to be numbers, not strings.

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