Retrieve json string stored in mysql field

Question

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

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;
?>

Added what I'm currentyl using, thanks for looking

fran35
– fran35

2016-04-27 23:14:35 +00:00
Commented Apr 27, 2016 at 23:14 — fran35
– fran35, Commented Apr 27, 2016 at 23:14

fusion3k · Accepted Answer · 2016-04-27 23:27:53Z

1

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"},
    (...)
]

answered Apr 27, 2016 at 23:27

fusion3k

11.7k4 gold badges27 silver badges47 bronze badges

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

3 Comments

fran35 Over a year ago

Thanks Fusion, makes sense on my mistake with encoding json. I indeed would like to have the "params" key. Php is giving me an error: Parse error: syntax error, unexpected '[' , which happens at line: $data[] = ['params'=>json_decode( $row['params'] )]; Any idea about it?

fusion3k Over a year ago

What is your PHP version? Try with $data[] = array( 'params' => json_decode( $row['params'] ) );

fran35 Over a year ago

That definitely was it, thanks a lot! Just for the record, version is: 5.3.29

hakki · Accepted Answer · 2020-05-18 01:01:13Z

0

Accepted answer works. Additionally if you want to pass parameter to mapped function in array_map()

Use callback like;

array_map(function ($json) { return json_decode($json, true); }, $res);

answered May 18, 2020 at 1:01

hakki

1

Collectives™ on Stack Overflow

Retrieve json string stored in mysql field

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related