Pulling json formatted data from table and accessing info within that data

Alex Lord
Alex Lord used Ask the Experts™
on
How do i work with this data ?

column
above is the column

below is the data structure in this column

data structure
i am formatting it as json in the column but how do i access is on php from i pull it from the data and select the values of ids i want ?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
David FavorFractional CTO
Distinguished Expert 2018

Commented:
Do something like this...

$obj = json_decode($json);

Open in new window


Then access $obj data as a nested hash... maybe like...

$campaign = $obj->{"output"}->{"campaign"};

$obj->{"output"}->{"campaign"} = "new-campaign-name";

Open in new window

Most Valuable Expert 2018
Distinguished Expert 2018
Commented:
Assuming you already have a PDO connection to your DB, something like this maybe:

$data = $db->query("SELECT CHANNEL FROM yourTable WHERE id = 1");
$json = $data->fetchColumn();

$channel = json_decode($json);
var_dump($channel);

Open in new window

That will pull the CHANNEL data from your table where the Record ID is 1 (filter the query anyway you like). It then json_decodes that data into an object called $channel. You can then access the data like any other object:

echo $channel->referrer;
echo $channel->output->campaign;
echo $channel->output->channel;
echo $channel->output->type;

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial