PHP fetchAll PDO::FETCH_OBJ and json_encode returns invalid JSON

Question

I try to fetch some data from my MySQL database by using PDO::FETCH_OBJ and json_encode but it doesn't work since I added an DATE-field in the MySQL table.

This is what my code looks like:

$app->get('/api/teams', function(Request $request, Response $response) {
$sql = "SELECT * FROM teams";

try {
    $db = new db();

    $db = $db->connect();

    $stmt = $db->query($sql);
    $teams= $stmt->fetchAll(PDO::FETCH_OBJ);
    $db = null;
    echo json_encode($teams);

   } catch(PDOException $e) {
       echo '{"error": {"text": '.$e->getMessage().'}';
   }
});

Before I added the DATE-field, it worked perfectly fine and it was returning an array with a bunch of objects. Now it returns no data at all. Even though, when I forexample do call /api/teams/25 - I receive the correct data, even with the date-data. So it"only" fails when I want to receive all teams/data at once.

Can someone tell me whats wrong and how to fix it?

I can also tell that I tried to do this:

foreach($teams as $team){
    echo json_encode($team);
}

But this returned invalid JSON data, which was a bunch of Objects without comma separation

EDIT

My DB SCHEMA looks like this,

id (int(11), primary_key, auto_increment)
team ( varchar(255) )
country ( varchar(255) )
league ( varchar(255) )
creation_day ( date )

** EDIT 2 **

The invalid JSON which the foreach-example returns, looks like this:

{"id":"27", "Bayern München", "country":"Germany", "league": "Bundesliga, "creation_day": "2016-10-14"} {"id":"28", "Borussia Dortmund", "country":"Germany", "league": "Bundesliga, "creation_day": "2016-10-14"}

..and so on

You need to post an example of the "bad" data, otherwise we can't tell you what's wrong with it. — Sammitch
– Sammitch, Commented Sep 25, 2017 at 20:45

tadman · Accepted Answer · 2017-09-25 21:03:34Z

2

The problem is you're not emitting one JSON document, but several that are just smashed together. That's not valid. Just do this:

echo json_encode($teams);

answered Sep 25, 2017 at 21:03

tadman

212k23 gold badges237 silver badges266 bronze badges

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

5 Comments

ST80 Over a year ago

ehhh... but thats what I do?

tadman Over a year ago

You do that inside a loop, you produce N JSON documents, not a singular valid JSON document. Generally when producing JSON results you're allowed one call and one call only to json_encode, so if you need to restructure your data to make it work in JSON you do that before making the call. In your case you seem to have an array so it's fine to pass it in directly, no need to iterate.

ST80 Over a year ago

in the post, I wrote "I also tried"... ´echo json_encode($teams)´ returns nothing - since I added the Date-field in the MySQL table

tadman Over a year ago

It's not clear why it didn't work the first time, because if your iterator version works then it absolutely should.

tadman Over a year ago

As a quick test, try and comment out any code not related to producing JSON results, then enable bits until you find the exact failure.

Mike S · Accepted Answer · 2017-09-26 23:15:37Z

PDO::FETCH_OBJ: returns an anonymous object with property names that correspond to the column names returned in your result set

http://php.net/manual/en/pdostatement.fetch.php

I suspect your use of PDO::FETCH_OBJ is the source of your problem. I would suggest you change that to PDO::FETCH_ASSOC if you're just looking to output the entire result as JSON.

So your code example would look like:

$app->get('/api/teams', function(Request $request, Response $response) {
$sql = "SELECT * FROM teams";

try {
    $db = new db();

    $db = $db->connect();

    $stmt = $db->query($sql);
    $teams= $stmt->fetchAll(PDO::FETCH_ASSOC); // <-- This changes
    $db = null;
    echo json_encode($teams);

   } catch(PDOException $e) {
       echo '{"error": {"text": '.$e->getMessage().'}';
   }
});

The reason I believe this is your issue is because JSON requires a certain format so if you were not getting any output from your echo then chances are your JSON was invalid. If you want to test you can check what you receive back from json_encode:

json_encode

Returns a JSON encoded string on success or FALSE on failure.

http://php.net/json_encode

Collectives™ on Stack Overflow

PHP fetchAll PDO::FETCH_OBJ and json_encode returns invalid JSON

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related