PHP JSON encode with commas and brackets

Question

I have a php script to create a json output getting data from mysql database:

<?php
include("myconnection.php");

//Creamos y ejecutamos la consulta
$sql = "SELECT * FROM myTable where value='1003'";
$result=mysql_query($sql);

while($row = mysql_fetch_array($result)){
    $value1 = $row['value1'];
    $value2 = $row['value2'];

$data =  json_encode(
  array("value1" => $value1, 
  "value2" => $value2));

echo $data;
}

?>

I obtain a result like this:

{"value1":"847534837","value2":"Regular"}{"value1":"847534838","value2":"Regular"}

I want to obtain a result with brackets and commas like this:

[{"value1":"847534837","value2":"Regular"},{"value1":"847534838","value2":"Regular"}]

I've tried to put some echo sentences like this:

echo "[";
while($row = mysql_fetch_array($result)){
$value1 = $row['value1'];
$value2 = $row['value2'];

$data =  json_encode(
  array("value1" => $value1, 
  "value2" => $value2)).",";

echo $data;
}
echo "]";

But the result finishes with ",]" like this:

[{"value1":"847534837","value2":"Regular"},{"value1":"847534838","value2":"Regular"},]

I would like some help.

Ray · Accepted Answer · 2017-01-12T19:44:03.303

Let json_encode() do the work for you, don't try to roll your own encoder!!!

To get the json string output you desire, just stick items you're currently encoding one at a time in another array and encode the who enchilada one time.

  $final_array = []; notation
  while($row = mysql_fetch_array($result)){
       $final_array[] = ["value1" => $row['value1'], 
                         "value2" => $row['value2']];
  }

 echo json_encode($final_array);

NOTE: I'm assuming you're using PHP >= 5.4 and have the [] notation, otherwise replace [] and ["value1" => $row['value1'], "value2" => $row['value2']] with array() and array("value1" => $row['value1'], "value2" => $row['value2']) respectively.

Deleting my answer using `rtrim` which answers the specific question because this is a better solution. — dstudeba, Jan 12 '17 at 20:00

score 0 · Answer 2 · answered Jan 12 '17 at 20:17

First, don't use mysql_* functions. Use PDO.

Second, there are two ways to deal with this:

For small amounts of data:

$result = $pdo->query('SELECT column1 as key1, ... FROM ...');
echo json_encode($result->fetchAll(PDO::FETCH_ASSOC));

This aproach will keep the data in memory four times at once: MySQL's buffer with the result, PDO's result set, serialized JSON string, and output buffer. For small amounts of data (up to 1 MB) it is fine and it is the fastest aproach.

For large amount of data, you can do this in small constant memory space. It is much more complicated, though.

First, you need to get rid of the fetchAll() call. But because there is no streaming JSON encoder in PHP, you need to write your own, which you tried. Then the next step is to use unbuffered query in the MySQL server, so the result set is not pre-generated in the database server. After all this, you can dump your whole database into JSON array without worrying about memory. But I quess you should stick to the two lines above.

The streaming JSON writer:

Let's assume you want array and you have iterable object and the individual objects in the collection are small, but there is a lot of them. Then you need to do this:

function write_json_array($array) {
  echo "[\n";
  $first = true;
  foreach ($array as $v) {
    if ($first) {
      $first = false;
    } else {
      echo ",\n";
    }
    echo json_encode($v);
  }
  echo "]";
}

Then you can use it like this:

$result = $pdo->query('SELECT column1 as key1, ... FROM ...');
$result->setFetchMode(PDO::FETCH_ASSOC);
write_json_array($result); // PDOStatement will do the trick.

PHP JSON encode with commas and brackets

2 Answers2