Output json specified data from mysql-CodePudding

I use phppagebuilder for editing my pages html content and in pages table from mysql i have this json generated code when i save:

{"html":["[block slug=\"contact\" id=\"IDL8P4JHM1GYPVX0\"]"],"components":[[{"tagName":"phpb-block","content":"","attributes":{"slug":"contact","id":"IDL8P4JHM1GYPVX0"}}]],"css":"* { box-sizing: border-box; } body {margin: 0;}","style":[],"blocks":{"en":{"IDL8P4JHM1GYPVX0":{"settings":{"attributes":{"style-identifier":"IDL8P4QRULUOPXC0"}},"blocks":[],**"html":** <- all page html is here

I need to extract the last "html": data after "blocks":[] to retrive all my html code to output in my php homepage.

php homepage output:

$s = mysqli_query($db,"SELECT `data` FROM `pages` WHERE `id` = 1");
$row = mysqli_fetch_array($s);
echo $row['data'];

but i get all the json code... and i need only my html code from the builder.

phppagebulder source : https://github.com/HansSchouten/PHPagebuilder

CodePudding user response：

You have two ways to solve this. First you can fetch all data and parse it using PHP:

$s = mysqli_query($mysqli,"SELECT `data` FROM `pages` WHERE `id` = 1");
$row = mysqli_fetch_array($s);

$data = json_decode($row[0], true);

preg_match('/id="([^"] )"/', $data['html'][0], $m);

$id = $m[1];

echo $data['blocks']['en'][$id]['html'] . PHP_EOL ;

Second way is parsing JSON by MySQL:

$s = mysqli_query($mysqli,"SELECT `data`->\"$.blocks.en.*.html\" FROM `pages` WHERE `id` = 1");
$row = mysqli_fetch_array($s);

var_dump($row[0]);

php code online