PHP: can't encode json with multiple rows

Question

I've spent a couple of hours looking through several the similar answers before posting my problem.

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

This is my query:

$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);
    }

That gets 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 only use this first half of the JSON...

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

... or if I only test 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".

What I want is to be able to output in one single [valid] JSON every row in the table.

Any suggestion will be very much appreciated.

I find your question to be too vague regarding the desired output. Should there be a `data` element that contains an indexed array of associative arrays? Or should there be indexed arrays -- each with a single element (`data`) which holds associative data? Personally, I see no benefit to `data` at all in the structure -- it just forms unnecessary array depth. — mickmackusa, Jun 01 '21 at 10:42

leemeichin · Accepted Answer · 2012-02-19T19:00:13.710

The problem is you're spitting out separate JSON for each row, as opposed to doing it all at once.

$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 minor change I've made is to store each row of the database as a new value in the $rows array. This means that when it's done, your $rows array contains all of the rows from your query, and thus you can get the correct result once it's finished.

The problem with your solution is that you're echoing valid JSON for one row of the database, but json_encode() doesn't know about all the other rows, so you're getting a succession of individual JSON objects, as opposed to a single one containing an array.

Thank you so much! All the solution works, and yours provides the most comprehensive explanation. Not only this is correct, but I completely overlooked the other examples do place the json_encode function outside of the while loop. I apologize for not noticing, and thank you for your time. — asraelarcangel, Feb 19 '12 at 20:22

score 3 · Answer 2 · answered Feb 19 '12 at 18:51

You need to change your PHP code into 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);

score 0 · Answer 3 · answered Jan 23 '15 at 22:43

I was trying the same in my PHP, so I came whit 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!
                        }

                }

score 0 · Answer 4 · answered Nov 16 '15 at 21:03

I must have spent 15 hours on this issue. Every variation discussed above was tried. Finally I was able to get the 'standard solution' working. The issue, very oddly, appears to be this:

When the interval is set beyond 14 hours, json appears to be 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";
}

The Alpha · Answer 5 · 2012-02-19T18:59:48.403

0

I think you should do

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

echo should be placed outside of the loop.

edited Feb 19 '12 at 18:59

answered Feb 19 '12 at 18:52

The Alpha

143,660
29
287
307

1

yes, but this will overwrite $rows['data'] every time the script goes through the while loop, so it won't work :) – Daan Feb 19 '12 at 18:55
1

Thanks, I didn't notice it just copy and pasted his code but fixed now. – The Alpha Feb 19 '12 at 19:01

PHP: can't encode json with multiple rows

5 Answers5

Linked

Related