How to produce a valid json output with array of json objects

Question

I created a mySQL database with phpMyAdmin in my local server. In this database I stored the names and the location of my friends (along with an id as a primary key for the database). I wrote and run the following php script to retrieve these data from the database and project them on my local web server (XAMPP):

<?php

$dbServername = 'localhost';
$dbUsername = 'root';
$dbPassword = '';
$dbName = 'Friends';

$conn = mysqli_connect($dbServername, $dbUsername, $dbPassword, $dbName);

header('Content-Type: application/json');

$sql = 'SELECT * FROM friends;';
$result = mysqli_query($conn, $sql);
$resultCheck = mysqli_num_rows($result);

if ($resultCheck > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
        echo json_encode($row, JSON_PRETTY_PRINT);

    }
}

However, in this way I take this output:

{
    "id": "1",
    "name": "David Belton",
    "location": "New Haven"
}{
    "id": "2",
    "name": "Alex Danson",
    "location": "New York"
}

which is not a valid json output overall. I would like to have the following output:

[{
        "id": "1",
        "name": "David Belton",
        "location": "New Haven"
    }, {
        "id": "2",
        "name": "Alex Danson",
        "location": "New York"
  }]

(which is also a valid json output)

How can I do this?

timiTao · Accepted Answer · 2018-03-14 12:08:36Z

1

This is happening because you're echoing out each object in isolation from the others, inside your loop.

while ($row = mysqli_fetch_assoc($result))
    echo json_encode($row, JSON_PRETTY_PRINT); //<-- echo out an isolated JSON object

Instead, just store everything in an array until you're done then echo out the JSON once, not iteratively.

$arr = [];
while ($row = mysqli_fetch_assoc($result)) $arr[] = $row;
json_encode($arr, JSON_PRETTY_PRINT);

edited Mar 14, 2018 at 12:08

timiTao

1,4113 gold badges22 silver badges36 bronze badges

answered Mar 14, 2018 at 12:03

Mitya

34.7k15 gold badges70 silver badges125 bronze badges

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

3 Comments

Jaquarh Over a year ago

Surely the result of mysqli_fetch_assoc($result) would be the same as $arr so possibly an easier solution is to simply json_encode(mysqli_fetch_assoc($result), JSON_PRETTY_PRINT); ? But +1 for explanation.

Mitya Over a year ago

The point is to store the retrieved associative arrays in an outer array, then JSON encode that outer array. That produces the output the OP wants.

Outcast Over a year ago

Thank you for your response. Perhaps I would like it a bit less dense but it is the right one so it's ok.

Eric Amshukov · Accepted Answer · 2018-03-14 12:07:24Z

1

Store the result in the array, then echo the encoded/formatted array:

<?php

$dbServername = 'localhost';
$dbUsername = 'root';
$dbPassword = '';
$dbName = 'Friends';

$conn = mysqli_connect($dbServername, $dbUsername, $dbPassword, $dbName);

header('Content-Type: application/json');

$sql = 'SELECT * FROM friends;';
$result = mysqli_query($conn, $sql);
$resultCheck = mysqli_num_rows($result);

$arr = array();

if ($resultCheck > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
        array_push($arr, $row);
    }
}

echo json_encode($arr, JSON_PRETTY_PRINT);

answered Mar 14, 2018 at 12:07

Eric Amshukov

2453 silver badges10 bronze badges

Comments

IcedAnt · Accepted Answer · 2018-03-14 12:17:20Z

1

Or, instead of fetching results one-by-one, just use mysqli_fetch_all

if ($resultCheck > 0)
{
    $row = mysqli_fetch_all($result);
    echo json_encode($row, JSON_PRETTY_PRINT);
}

which is actually faster and uses less memory: http://php.net/manual/en/mysqli-result.fetch-all.php

edited Mar 14, 2018 at 12:17

answered Mar 14, 2018 at 12:08

IcedAnt

4543 silver badges12 bronze badges

3 Comments

Outcast Over a year ago

This is pretty cool(upvote). However keep in mind that this returns an array of arrays and not a simple array.

IcedAnt Over a year ago

Thanks! You're right, this is an array of arrays but in your accepted answer, $arr is also an array of arrays

Outcast Over a year ago

Haha, I did not realise this even if tested it...also I used finally the second answer...

timiTao · Accepted Answer · 2018-03-14 12:17:33Z

1

Create an array and push those records in while loop. At the end echo json_encode(); so the output will be in proper JSON format

$records = array();
if ($resultCheck > 0) {
    while ($row = mysqli_fetch_assoc($result)) {

        $records[] = $row;
    }
}
echo json_encode($records, JSON_PRETTY_PRINT);

edited Mar 14, 2018 at 12:17

timiTao

1,4113 gold badges22 silver badges36 bronze badges

answered Mar 14, 2018 at 12:03

Pankaj Makwana

3,0486 gold badges33 silver badges49 bronze badges

Collectives™ on Stack Overflow

How to produce a valid json output with array of json objects

4 Answers 4

3 Comments

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related