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 — HPierce
– HPierce, Commented Mar 25, 2018 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? — HPierce
– HPierce, Commented Mar 25, 2018 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
– JamMan9, Commented Mar 25, 2018 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 w3schools.com/sql/func_mysql_cast.asp SELECT CAST(ID as UNSIGNED) — ArtisticPhoenix
– ArtisticPhoenix, Commented Mar 25, 2018 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
– HPierce, Commented Mar 25, 2018 at 16:30

LitileXueZha · Accepted Answer · 2019-07-18 05:02:39Z

6

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

this resolve my problem

answered Jul 18, 2019 at 5:02

LitileXueZha

6569 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jeroen Heier Over a year ago

Try to explain more clearly why this is an answer to the question.

VitorMM Over a year ago

Worked for me, despite the lack of details

appel Over a year ago

Some more context here: stackoverflow.com/a/58830039/570570

Sergey Onishchenko · Accepted Answer · 2021-09-17 03:51:59Z

0

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: ""    
  }
]

answered Sep 17, 2021 at 3:51

Sergey Onishchenko

7,9595 gold badges51 silver badges52 bronze badges

Comments

py-php · Accepted Answer · 2023-03-28 19:35:27Z

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, 2023 at 19:35

answered Mar 28, 2023 at 19:15

py-php

11 bronze badge

2 Comments

py-php Over a year ago

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!

Andy Preston Over a year ago

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.

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related