fran35 fran35 - 7 months ago 22
SQL Question

Retrieve json string stored in mysql field

I have a table, which I need to echo as json to js. Structure is:

enter image description here

How do I get the data as a json string without extra slashes? I'm not interested in processing params in php or manipulating it in a query, it's used for storage of data (which could vary a lot) and will be used in js side. Using a document based db is not an option at this time.

I have problems with "params", as some extra quotes remain if I try to use stripslashes and invalidates json.




<?php
...
$statement=$pdo->prepare("SELECT params FROM content WHERE id = 19");
$statement->execute();
$results=$statement->fetchAll(PDO::FETCH_ASSOC);
$json=json_encode($results);

$json = stripslashes($json);
var_dump ($results);
echo "<br/>";
echo $json;
?>

Answer

Your fields are already JSON strings. By encoding it, you obtain not valid JSON.

Write in this way:

$data = array();
while( $row = $statement->fetch(PDO::FETCH_ASSOC) )
{
    $data[] = ['params'=>json_decode( $row['params'] )];
}

$json = json_encode($data);
echo $json;

This will output a JSON like this:

[
    {"params":{"sentence1":"city"}},
    (...)
]

If you don't want preserve the “params” key, you can do in this way:

$results=$statement->fetchAll(PDO::FETCH_ASSOC);
$data = array_map( 'json_decode', array_column( $results, 'params' ) );
$json = json_encode($data);
echo $json;

This will output a JSON like this:

[
    {"sentence1":"city"},
    (...)
]