Json encode an entire mysql result set

Question

I want to get json with php encode function like the following

<?php 
  require "../classes/database.php";

  $database = new database();
  header("content-type: application/json");
  $result = $database->get_by_name($_POST['q']);   //$_POST['searchValue']

  echo '{"results":[';
  if($result)
  {
     $i = 1;
     while($row = mysql_fetch_array($result))
     {
        if(count($row) > 1) 
        {
           echo json_encode(array('id'=>$i, 'name' => $row['name']));
           echo ",";
        }
        else 
        {
           echo json_encode(array('id'=>$i, 'name' => $row['name']));
        }
        $i++; 
     }
  }
  else
  {
     $value = "FALSE";
     echo json_encode(array('id'=>1, 'name' => ""));  // output the json code
  }

  echo "]}";

i want the output json to be something like that

{"results":[{"id":1,"name":"name1"},{"id":2,"name":"name2"}]}

but the output json is look like the following

{"results":[{"id":1,"name":"name1"},{"id":2,"name":"name2"},]}

As you realize that there is comma at the end, i want to remove it so it can be right json syntax, if i removed the echo ","; when there's more than one result the json will generate like this {"results":[{"id":1,"name":"name1"}{"id":2,"name":"name2"}]} and that syntax is wrong too

Hope that everybody got what i mean here, any ideas would be appreciated

Do not produce JSON manually. Put all the data into a single data structure (an associative array, probably) and use `json_encode()` only once to produce the output out of this data structure. — axiac, Nov 10 '21 at 13:27
Any answer below that advises anything other than using `json_encode()` at the end of your processing is giving you bad advice. In modern PHP, you'd just `echo json_encode(['results' => $result->fetch_all(MYSQLI_ASSOC)]);`. ...and your `id` values should probably be coming from you db too. — mickmackusa, Nov 10 '21 at 13:29

Jeff Lambert · Accepted Answer · 2014-03-26T13:52:00.657

13

If I were you, I would not json_encode each individual array, but merge the arrays together and then json_encode the merged array at the end. Below is an example using 5.4's short array syntax:

$out = [];
while(...) {
  $out[] = [ 'id' => $i, 'name' => $row['name'] ];
}
echo json_encode($out);

edited Mar 26 '14 at 13:52

answered Sep 23 '11 at 16:20

Jeff Lambert

24,395
4
69
96

Though not super important in your answer, `$i` will not increment in this snippet. – mickmackusa Nov 10 '21 at 13:55
1

@mickmackusa Perhaps, but `...` will not compile either, and there's no telling what that is a stand-in for, either. – Jeff Lambert Nov 10 '21 at 16:03

score 9 · Answer 2 · answered Sep 23 '11 at 16:19

9

Do the json_encoding as the LAST step. Build your data structure purely in PHP, then encode that structure at the end. Doing intermediate encodings means you're basically building your own json string, which is always going to be tricky and most likely "broken".

$data = array();
while ($row = mysql_fetch_array($result)) {
   $data[] = array('id'=>$i, 'name' => $row['name']);
}
echo json_encode($data);

answered Sep 23 '11 at 16:19

Marc B

356,200
43
426
500

Though not super important in your answer, `$i` will not increment in this snippet. – mickmackusa Nov 10 '21 at 13:54

score 2 · Answer 3 · answered Sep 23 '11 at 16:20

2

build it all into an array first, then encode the whole thing in one go:

$outputdata = array();
while($row = mysql_fetch_array($result)) {
    $outputdata[] = $row;
}

echo json_encode($outputdata);

answered Sep 23 '11 at 16:20

Spudley

166,037
39
233
307

Cleanest answer – Mbotet Jan 31 '22 at 17:01

Json encode an entire mysql result set

3 Answers3

Linked

Related