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
– axiac, Commented Nov 10, 2021 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
– mickmackusa ♦, Commented Nov 10, 2021 at 13:29

Jeff Lambert · Accepted Answer · 2014-03-26 13:52:00Z

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, 2014 at 13:52

answered Sep 23, 2011 at 16:20

Jeff Lambert

24.7k4 gold badges72 silver badges98 bronze badges

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

2 Comments

mickmackusa Over a year ago

Though not super important in your answer, $i will not increment in this snippet.

Jeff Lambert Over a year ago

@mickmackusa Perhaps, but ... will not compile either, and there's no telling what that is a stand-in for, either.

Marc B · Accepted Answer · 2011-09-23 16:19:30Z

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, 2011 at 16:19

Marc B

362k44 gold badges433 silver badges508 bronze badges

1 Comment

mickmackusa Over a year ago

Though not super important in your answer, $i will not increment in this snippet.

Spudley · Accepted Answer · 2011-09-23 16:20:32Z

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, 2011 at 16:20

Spudley

169k39 gold badges240 silver badges309 bronze badges

1 Comment

Mbotet Over a year ago

Cleanest answer

Collectives™ on Stack Overflow

Json encode an entire mysql result set

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related