Prevent MySQL PDO from returning numbers as strings

Question

I am using PHP's PDO to query a MySQL database. It returns numbers and integers as strings, and is messing with my JSON.

Is there a better way of fixing it other than type casting the values row by row?

array(2) { 
  ["name"]=> string(11) "Preliminary" 
  ["sell_price"]=> string(6) "864.00"
}

Guess I wasn't suppose to edit the main message! Note that I am using PHP Version 5.3.14, and not using prepared statements. — user1032531, Aug 22 '12 at 16:41
Keep in mind that PHP is dynamically-typed (or something like that), so it won't make much of a difference either way what type your variables are. — Waleed Khan, Aug 22 '12 at 16:42
Yes, I pass the results to json_encode(), and then jqPlot takes the data and makes a plot. The results are different if the numbers are quoted. — user1032531, Aug 22 '12 at 16:49
@databyss: false - it supports numeric types: http://json.org/ — Marc B, Aug 22 '12 at 16:55
@Marc B ahh yes, I stand corrected :) I was pretty sure it just encapsulated all data in "" — databyss, Aug 22 '12 at 17:14
reference: http://stackoverflow.com/questions/1197005/how-to-get-numeric-types-from-mysql-using-pdo — diyism, Oct 16 '13 at 12:24
All the answers here are ugly hacks. For the correct solution, see [this answer](http://stackoverflow.com/a/20123337/229792). — jameshfisher, Apr 03 '14 at 14:42
@jameshfisher Excellent! Gentlemen, please upvote this comment! — Sliq, Sep 28 '14 at 19:38

score 11 · Accepted Answer · answered Aug 22 '12 at 16:50

11

If you are using php 5.3.3 or higher you can use JSON_NUMERIC_CHECK as the second argument to json_encode to do this.

See: http://us3.php.net/manual/en/function.json-encode.php

answered Aug 22 '12 at 16:50

Explosion Pills

188,624
52
326
405

3

Good answer. Thanks. Note this warning however which describes how a phone number like '+33123456789' will be converted to an integer. http://us3.php.net/manual/en/function.json-encode.php#106641 – user1032531 Aug 22 '12 at 16:57
5

All the answers here are ugly hacks. For the correct solution, see [this answer](http://stackoverflow.com/a/20123337/229792). – jameshfisher Apr 03 '14 at 14:42
`JSON_NUMERIC_CHECK` is not suitable in some cases. Like one given [here](http://php.net/manual/en/function.json-encode.php#106641). But what is the reason mysql driver returns integer as string? Isn't there any better way!! – Salman Sep 19 '14 at 07:13

score 3 · Answer 2 · edited May 23 '17 at 11:59

Although @Explosion Pills works in this case for @user1032531's needs, it's not addressing the 1st source of the problem.

As you can see in the original question, on the PHP side the value from sell_price is already a string, although, as the user said, in the database it's stored as a "number" (we don't know if it's a DECIMAL or a FLOAT).

Well, when using PDO with MySQL a proper config may be required in order to get "numbers" as "numbers", as user @jameshfisher pointed out in his comment. You can follow his link in order to get almost everything you need to get it working.

Appart from that, the accepted answer is working because without the use of the JSON_NUMERIC_CHECK json_encode will send everything as text no matter the type of the variable, and when using it it will convert the value to a type based on guesses rather than the type of the original var.

Prevent MySQL PDO from returning numbers as strings

2 Answers2