PHP: cannot json encode with multiple lines

Question

I spent a couple of hours looking at a few similar answers before posting my problems.

I am retrieving data from a table in my database and I want to encode it in JSON. However, the output of json_encode () is only valid if the table has one row. If more than one line exists, the test at http://jsonlint.com/ returns an error.

This is my request:

$result = mysql_query($query); $rows = array(); //retrieve and print every record while($r = mysql_fetch_assoc($result)){ $rows['data'] = $r; //echo result as json echo json_encode($rows); } 

This gives me the following JSON:

 { "data": { "entry_id":"2", "entry_type":"Information Relevant to the Subject", "entry":"This is my second entry." } } { "data":{ "entry_id":"1", "entry_type":"My Opinion About What Happened", "entry":"This is my first entry." } } 

When I run the test at http://jsonlint.com/ , it returns this error:

  Parse error on line 29: ..."No comment" }}{ "data": { ---------------------^ Expecting 'EOF', '}', ',', ']' 

However, if I use this first half of JSON ...

 { "data": { "entry_id":"2", "entry_type":"Information Relevant to the Subject", "entry":"This is my second entry." } } 

... or if I check only the second half ...

 { "data":{ "entry_id":"1", "entry_type":"My Opinion About What Happened", "entry":"This is my first entry." } } 

... the same test will return "Valid JSON".

I want to be able to output in one single [valid] JSON to each row of the table.

Any suggestion would be much appreciated.

Answer 1

The problem is that you spit out a separate JSON for each line, and not for all this.

 $result = mysql_query($query); $rows = array(); //retrieve and print every record while($r = mysql_fetch_assoc($result)){ // $rows[] = $r; has the same effect, without the superfluous data attribute $rows[] = array('data' => $r); } // now all the rows have been fetched, it can be encoded echo json_encode($rows); 

The little change I made is to save each row of the database as a new value in the $rows array. This means that when this is done, your $rows array contains all the rows from your query, and thus you can get the correct result after it is completed.

The problem with your solution is that you are using JSON echo for one row of the database, but json_encode() not aware of all the other rows, so you get a sequence of separate JSON objects, as against one containing the array.

Answer 2

You need to change your PHP code to something like this:

 $result = mysql_query($query); $rows = array(); //retrieve every record and put it into an array that we can later turn into JSON while($r = mysql_fetch_assoc($result)){ $rows[]['data'] = $r; } //echo result as json echo json_encode($rows); 

Answer 3

I think you should

 $rows = array(); while($r = mysql_fetch_assoc($result)){ $rows[]['data'] = $r; } echo json_encode($rows); 

echoes should be placed out of loop.

Answer 4

I tried the same in my PHP, so I came up with this ...

 $find = mysql_query("SELECT Id,nombre, appaterno, apmaterno, semestre, seccion, carrera FROM Alumno"); //check that records exist if(mysql_num_rows($find)>0) { $response= array(); $response["success"] = 1; while($line = mysql_fetch_assoc($find)){} $response[] = $line; //This worked for me } echo json_encode($response); } else { //Return error $response["success"] = 0; $response["error"] = 1; $response["error_msg"] = "Alumno could not be found"; echo json_encode($response); } 

And in my Android class ...

 if (Integer.parseInt(json.getString("success")) == 1) { Iterator<String> iter = json.keys(); while (iter.hasNext()) { String key = iter.next(); try { Object value = json.get(key); if (!value.equals(1)) { JSONObject jsonArray = (JSONObject) value; int id = jsonArray.getInt("Id"); if (!db.ExisteAlumo(id)) { Log.e("DB EXISTE:","INN"); Alumno a = new Alumno(); int carrera=0; a.setId_alumno(id); a.setNombre(jsonArray.getString("nombre")); a.setAp_paterno(jsonArray.getString("appaterno")); a.setAp_materno(jsonArray.getString("apmaterno")); a.setSemestre(Integer.valueOf(jsonArray.getString("semestre"))); a.setSeccion(jsonArray.getString("seccion")); if(jsonArray.getString("carrera").equals("C")) carrera=1; if(jsonArray.getString("carrera").equals("E")) carrera=2; if(jsonArray.getString("carrera").equals("M")) carrera=3; if(jsonArray.getString("carrera").equals("S")) carrera=4; a.setCarrera(carrera); db.addAlumno(a); } } } catch (JSONException e) { // Something went wrong! } } 

Answer 5

I had to spend 15 hours on this problem. All options discussed above were considered. Finally, I was able to get a "standard solution". The problem, oddly enough, is as follows:

When the interval is set in 14 hours, json seems unable to parse it. There must be a limit to JSON.

 $sql= "SELECT cpu_name, used, timestamp FROM tbl_cpu_use WHERE timestamp>(NOW() - INTERVAL 14 HOUR) ORDER BY id"; $result=mysql_query($sql); if ($result){ $i=0; $return =[]; while($row = mysql_fetch_array($result, MYSQL_NUM)){ $rows[] = $row; } echo json_encode($rows); }else{ echo "ERROR"; }