Mysql returning all integers as string in PHP

Question

Whenever I am making a query, PHP is bringing the data from the MYSQL database as string, and this is affecting data conversion when I send the customer a JSON response.

For example, in the table users have the fields id, name and role_id. When I return the JSON of this data, instead of getting:

{ "id" : 1, "name": "Wallace", "role_id" : 12 }

I’m getting:

{"id" : "1", "name": "Wallace", "role_id" : "12" }

That is, somehow the data returned from MYSQL is being treated all as string.

I could do a CAST, but doing it all the time could be a pain in the ass.

How could I solve this problem?

Could be a problem with MYSQL or the PHP extension responsible for communicating with it?

See the structure of the table below:

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `role_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

Answer 1

When making the conversion to JSON with the json_encode, you can pass the parameter JSON_NUMERIC_CHECK, it is available from the version 5.3.3, it basically checks whether that value can be considered numerical(I believe with the function is_numeric), and, if so, present JSON as a.

See these examples:

Common use with numerical values:

$arr = array('a' => 1, 'b' => 2, 'c' => 3, 'd' => 4, 'e' => 5);
echo json_encode($arr, JSON_NUMERIC_CHECK); //{"a":1,"b":2,"c":3,"d":4,"e":5}

Use with string values that can be converted to numeric type.

$arr = array('a' => "1", 'b' => "2", 'c' => "3", 'd' => "4", 'e' => "5");
echo json_encode($arr, JSON_NUMERIC_CHECK); //{"a":1,"b":2,"c":3,"d":4,"e":5}

See more about the json_encode in official php documentation.

Using the native Mysql driver:

By default, all database/PHP operations will return all numeric values as a string to get around this in MySql, from PHP 5.3.0, you can use Mysql Native Driver, it is a PHP extension written as part of the project, where it is under the license of the same.

Using the mysqlnd and the mysqli, you can ask to return the numeric values with their correct types by passing the following option:

$mysqli->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, true);

Already using the PDO, it will automatically do the conversions, but all queries must be prepared before on the server-side.

You can see the comparison between the mysqlnd and the libmysqlclient here.