json_encode in PHP not returning correct results

Question

I am trying to input data into a grid which has CRUD operations its called Jquery JTable, but there seems to be some sort of error with the Json conversion from the MYSQL table to JSON, for the data to be displayed on the table it needs to look kinda like this:

{
    "Result":"OK",
    "Records":[
      {"PersonId":1,"Name":"Benjamin Button","Age":17,"RecordDate":"\/Date(1320259705710)\/"},
      {"PersonId":2,"Name":"Douglas Adams","Age":42,"RecordDate":"\/Date(1320259705710)\/"},
      {"PersonId":3,"Name":"Isaac Asimov","Age":26,"RecordDate":"\/Date(1320259705710)\/"},
      {"PersonId":4,"Name":"Thomas More","Age":65,"RecordDate":"\/Date(1320259705710)\/"}
    ]
}

But whenever I implement the json_encode function in php it gives me this:

{
    "Result":"OK",
    "Records":[
        {"0":"1","PersonId":"1","1":"Benjamin Button","Name":"Benjamin Button","2":"17","Age":"17","3":"2011-12-27 00:00:00","RecordDate":"2011-12-27 00:00:00"},
        {"0":"2","PersonId":"2","1":"Douglas Adams","Name":"Douglas Adams","2":"42","Age":"42","3":"2011-12-26 00:00:00","RecordDate":"2011-12-26 00:00:00"},
        {"0":"3","PersonId":"3","1":"Isaac Asimov","Name":"Isaac Asimov","2":"26","Age":"26","3":"2011-12-28 00:00:00","RecordDate":"2011-12-28 00:00:00"},
        {"0":"4","PersonId":"4","1":"Thomas More","Name":"Thomas More","2":"61","Age":"61","3":"2011-12-27 00:00:00","RecordDate":"2011-12-27 00:00:00"},
        {"0":"5","PersonId":"5","1":"Ihsan Oktay Anar","Name":"Ihsan Oktay Anar","2":"44","Age":"44","3":"2012-01-03 20:55:02","RecordDate":"2012-01-03 20:55:02"}
    ]
}

Here is my PHP code:

<?php

//Get records from database
$result = mysqli_query($conn, "SELECT * FROM people;");

//Add all records to an array
$rows = array();
while($row = mysqli_fetch_array($result))
{
    $rows[] = $row;
}

//Return result to jTable
$jTableResult = array();
$jTableResult['Result'] = "OK";
$jTableResult['Records'] = $rows;
print json_encode($jTableResult);

?>

the table is called people and it has four fields: personId, Name, Age and RecordDate.

Any ideas would be greatly appreciated.

You're using mysqli_fetch_array which returns index keys (0/1/2/etc...), you want to use mysqli_fetch_assoc() OR simply change it to this: mysqli_fetch_array($result, MYSQLI_ASSOC) — Darren
– Darren, Commented Aug 4, 2014 at 8:03
There is no point looping through the results as an array, if you're just going to store them separately as an array. You might as well just do $rows = mysqli_fetch_array($result); and use $rows; — iswinky
– iswinky, Commented Aug 4, 2014 at 8:04

TiMESPLiNTER · Accepted Answer · 2014-08-04 08:09:56Z

1

You probably want to try the mysqli_fetch_assoc() function.

Because according to the PHP documentation about mysqli_fetch_array():

Fetch a result row as an associative, a numeric array, or both

This is what you experiencing right now with your code because default fetching mode is MYSQLI_BOTH:

... "1":"Benjamin Button","Name":"Benjamin Button" ...

You could also use mysqli_fetch_array() but you have to adjust the fetch mode then:

mysqli_fetch_array($result, MYSQLI_ASSOC);

edited Aug 4, 2014 at 8:09

answered Aug 4, 2014 at 8:04

TiMESPLiNTER

5,9193 gold badges31 silver badges68 bronze badges

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

2 Comments

Darren Over a year ago

This also looks like a comment, just expand on it more splinter before they start down-voting you.

TiMESPLiNTER Over a year ago

Everything said so far.

Collectives™ on Stack Overflow

json_encode in PHP not returning correct results

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related