Johnny Johnny - 7 months ago 48
PHP Question

php json_encode() adds quotes to json retrieved from mysql

I have a mysql table with few columns, one of them is 'detailed' containing json string - which contains 6 keys.

{
"x": [
-0.02,
-0.04,
-0.05
],
"y": [
-0.01,
0,
0,
-0.01
],
"z": [
0.04,
0,
-0.03,
-0.01
],
"roll": [
0.5,
0.6,
0.6
],
"pitch": [
-3.4,
-3.3,
-3.3
],
"yaw": [
224.2,
224.2,
224.2
] }


Then in php i select three columns, which one of them is said json column.

$sql = "SELECT date, speed, detailed FROM info_table";
$result = $conn->query ( $sql );

if ($result-> num_rows ) {

while ( $row = $result->fetch_object() ) {
$rows[] = $row;

}
}
echo json_encode($rows);


In JavaScript i make an AJAX call to retrieve those values , and then parse them.

data = JSON.parse(xmlhttp.responseText);


So far so good, JSON object is returned BUT when i try to get into nested property eg.

data[1].detailed.x[1]


it gives me undefined because everything after 'detailed' is treated as string not as object.

I know what causes this, in php when i echo the result of json_encode i get:


{"date":"2016-04-22
14:50:24","speed":"0","detailed":"{\"x\":[-0.02,-0...] (...REST OF
OUTPUT...) }"}


When i delete bolded quotes around curly brackets, JSON.parse() JavaScript correctly sees this nested value as object not string.

My question is, how to retrieve said JSON column from mySQL and then just echo it in PHP so I don't have to encode it again in PHP - which adds said quotes around curly brackets.

Answer

If you already have json in db then you need to decode it before you encode the whole thing:

$sql = "SELECT date, speed, detailed FROM info_table";
$result = $conn->query($sql);

if ($result->num_rows) {

    while ($row = $result->fetch_object()) {
        $row->detailed = json_decode($row->detailed);
        $rows[] = $row;

    }
}
echo json_encode($rows);