PHP PDO - Why is my fetchAll() converting all values to a string when using json_encode?

Question

I'm doing a select all SQL query on a table and running it in a prepared query in PHP. I'm then echoing the result inside a json_encode. The result is putting every value as a string, even the row ID which is an INT. How do you keep the original value types ?

    $sql = "SELECT * FROM `Type`";
    $stmt = $pdo->prepare($sql);
    $stmt->execute();
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    echo json_encode($result);

The output is as follows:

[{"ID":"1","Type":"Classic Starters","Description":""},{"ID":"2","Type":"Special Starters","Description":""}]

The desired output is as follows:

[{"ID":1,"Type":"Classic Starters","Description":""},{"ID":2,"Type":"Special Starters","Description":""}]

Thanks in advance <3

Possible duplicate of [PDO SQLSRV and PDO MySQL return strings when fetching int or float](https://stackoverflow.com/questions/38315580/pdo-sqlsrv-and-pdo-mysql-return-strings-when-fetching-int-or-float) — HPierce, Mar 25 '18 at 16:12
I changed my mind, this is a _way_ better duplicate target :( [PHP + PDO + MySQL: how do I return integer and numeric columns from MySQL as integers and numerics in PHP?](https://stackoverflow.com/questions/20079320/php-pdo-mysql-how-do-i-return-integer-and-numeric-columns-from-mysql-as-int) — HPierce, Mar 25 '18 at 16:18
Thanks for the replies. I've had this all working as expected previously when I hosted my API locally. I've migrated to AWS and ran into these problems. I wasn't aware of the reasons discussed (in your first reply), so thanks for that. Do you know if AWS allow you to play around with the settings discussed in your second link ? All the best — JamMan9, Mar 25 '18 at 16:21
`(int)$row['ID']` I would just cast it to an int. I don't like relying on obscure settings as it hampers portability. If you cast it then you are sure it's an int. You may be able to cast it in the SELECT part of the query https://www.w3schools.com/sql/func_mysql_cast.asp `SELECT CAST(ID as UNSIGNED)` — ArtisticPhoenix, Mar 25 '18 at 16:24
@JamLis, I'm not an AWS user myself. But I'd expect you'd be able to follow the same instructions for AWS. Good luck! — HPierce, Mar 25 '18 at 16:30

score 6 · Answer 1 · answered Jul 18 '19 at 05:02

6

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

this resolve my problem

answered Jul 18 '19 at 05:02

LitileXueZha

512
6
11

7

Try to explain more clearly why this is an answer to the question. – Jeroen Heier Jul 18 '19 at 05:12
Worked for me, despite the lack of details – VitorMM Feb 07 '21 at 20:17
1

Some more context here: https://stackoverflow.com/a/58830039/570570 – appel Mar 05 '21 at 14:56

score 0 · Answer 2 · answered Sep 17 '21 at 03:51

I faced the same problem with Doctrine, so basically the workaround is for PDO especially if you don't want or can not configure it to avoid conversion to strings.

Create a class

Class Entity {
    public int $ID;
    public string $Type;
    public string $Description;
}

Then pass the class name along with \PDO::FETCH_CLASS

$sql = "SELECT * FROM `Type`";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll(\PDO::FETCH_CLASS, Entity::class);

The $result will be something like this:

array:2 [
  0 => Entity {
    ID: 1
    Type: "Classic Starters"
    Description: ""
  }
  1 => Entity {
    ID: 1
    Type: "Special Starters"
    Description: ""    
  }
]

py-php · Answer 3 · 2023-03-28T19:35:27.953

0

I needed to use a "dynamic class name". So here an alternative to @Sergey Onishchenko's answer:

//I conditionally set the class name and sql statement.
$fetchClass='Entity';
$sql = "SELECT * FROM `Type`";

$stmt = $pdo->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_CLASS, $fetchClass); //I removed the backslash in front of PDO::FETCH_CLASS, because I don't know it's purpose and it also works wihout a backlash.

Instead of using Entity::class or a variable name, it also works directly with a string of course:

$result = $stmt->fetchAll(PDO::FETCH_CLASS, 'Entity');

edited Mar 28 '23 at 19:35

answered Mar 28 '23 at 19:15

py-php

1
1

What's the puropse of the backslash here? `$result = $stmt->fetchAll(\PDO::FETCH_CLASS, Entity::class);` I tried it without a backslash and it also works! – py-php Mar 28 '23 at 19:38
The backslash is to use the top level namespace. It's unnecessary if the current file has no namespace but needed if the current file IS in a namespace. – Andy Preston Apr 01 '23 at 11:50

PHP PDO - Why is my fetchAll() converting all values to a string when using json_encode?

3 Answers3