How can I get PDO::FETCH_ASSOC to return integers as integers?

Question

        $fetchedData = $query->fetchAll(PDO::FETCH_ASSOC);

returns

0 = {array} [8]
 candidate_id = "2"
 candidate_name = "Christine Hill"
 company_id = "1"
 end_date = "2016-09-30"
 home_city = "Salisbury"
 job_id = "8"
 skill_id = "3"
 start_date = "2016-01-01"

although some fields (candidate_id, company_id, job_id, skill_id) are declared as int(11) in the MySql database.

I want to treat them as integer in PHP, and return them as so in JSON over my AJAX interface.

Am I doing something wrongly? Is there some way to avoid repeated casts in PHP and have PDO return the same type as in the database?

It's PHP. Why do you care? It'll deal with them as numbers if necessary. — tadman, Jan 22 '20 at 19:48
If you need the JSON to have numbers - you could use `JSON_NUMERIC_CHECK` in the encoding. — Nigel Ren, Jan 22 '20 at 19:50
Check @NigelRen comment. PHP DB libraries always return strings AFAIK. — AbraCadaver, Jan 22 '20 at 19:54
The only time I had run into this need was dealing with xmlrpc, and a strict app. Just ended up building a pre-caster on the data before it heads to xmlrpc. But thankfully JSON is so much nicer, and `JSON_NUMERIC_CHECK` does the job well (as Nigel mentioned). — IncredibleHat, Jan 22 '20 at 20:00
Thanks (and upvote), all, for such quick & helpful replies. @NigelRen I think that yours should be posted as answer, which I will accept, in order to help others in future — Mawg says reinstate Monica, Jan 22 '20 at 20:10
yup, just double checked it & it's fine for me, as this is an AJAX API. Maybe, as @tadman says, it's a non-issue for non-AJAX PHP. Would an answer hurt? Or should I just delete the question (with thanks)? — Mawg says reinstate Monica, Jan 22 '20 at 20:18
As far as I know PDO is completely ignorant of the schema, it just wraps around the database connection, so you need to do your own casting. If you're concerned about this thing you need to use an abstraction that can fetch that data and convert as necessary. . [Doctrine](http://www.doctrine-project.org/), [Propel](http://propelorm.org/), [RedBeanPHP](https://redbeanphp.com/), and [Eloquent](https://laravel.com/docs/master/eloquent) are examples of tools that can help manage this better. — tadman, Jan 22 '20 at 21:23
Just cast them in run time using (int) or intval(row['job_id']) — Tom Shaw, Jan 22 '20 at 23:10
@tadman PDO is, but mysqlnd (through mysql binary protocol), on the contrary, is quite enlightened about the field types. And PDO just conveys whatever it takes from them. — Your Common Sense, Jan 23 '20 at 07:16
@TomShaw imagine you've got some automated GraphQL or REST responder that returns whatever is requested. Adding such a manual casting to a perfectly automated tool is a pain. — Your Common Sense, Jan 23 '20 at 07:20

score -1 · Answer 1 · answered Jan 23 '20 at 07:03

In MySQL, these are equivalent:

INSERT INTO t (job_id) VALUES("8");
INSERT INTO t (job_id) VALUES(8);

That is, the quotes are harmless. Ditto for most other MySQL contexts, and most PHP contexts.

As for JSON, what is the recipient of the JSON going to do with the value?

When I must turn it into a number, this is a concise way to do so. (And it is cryptic enough to be a clue to the reader.)

$job_id = 0+$job_id;

How can I get PDO::FETCH_ASSOC to return integers as integers?

1 Answers1