Decode stored JSON value in Database result in result_array()

Question

I want to extract value from json stored in db, but i cannot figure it out. here's my db looks like :

+-------------------+-------------------------------------------------+---------------------+---------------------+
| refid             |                      kepribadian                | jam_mulai           | jam_selesai         |
+-------------------+-------------------------------------------------+---------------------+---------------------+
| GAD25D335B0F1DE3B | {"Introversion":"53.3%","Extroversion":"46.7%"} | 2019-08-09 01:16:00 | 2019-08-09 01:19:46 |
| GAD25D32DAE90578A | {"Introversion":"63.3%","Extroversion":"34.7%"} | 2019-08-09 08:55:47 | 2019-08-09 08:56:48 |
+-------------------+-------------------------------------------------+---------------------+---------------------+

And this is my models :

public function filter_hasil_mbti($search, $limit, $start, $order_field, $order_ascdesc)
{

    $sql =  $this->db->select('a.id AS id, a.refid AS refid, CONCAT(b.firstname," ",b.lastname) AS fullname, a.kepribadian AS kepribadian, a.jam_mulai AS jam_mulai, a.jam_selesai AS jam_selesai, a.percentage AS percentage')
            ->from('jawaban_mbti as a')
            ->join('registrasi_baru as b', 'a.refid=b.refid', 'left')
            ->like('a.refid', $search)
            ->or_like('b.firstname', $search)
            ->or_like('b.lastname', $search)
            ->or_like('a.kepribadian', $search)
            ->or_like('a.jam_mulai', $search)
            ->or_like('a.jam_selesai', $search)
            ->order_by($order_field, $order_ascdesc)
            ->limit($limit, $start)
            ->get()->result_array();

    return $sql;
}

So, I can access column kepribadian json as key and store it all in result_array() How can i do that?

Thank you.

UPDATE

Here's my controller :

public function ajax_jawaban_mbti()
{
    $search         = $_POST['search']['value'];
    $limit          = $_POST['length'];
    $start          = $_POST['start'];
    $order_index    = $_POST['order'][0]['column'];
    $order_field    = $_POST['columns'][$order_index]['data'];
    $order_ascdesc  = $_POST['order'][0]['dir'];

    $sql_total      = $this->mhasil->count_all_Soal('jawaban_mbti');
    $sql_data       = $this->mhasil->filter_hasil_mbti($search, $limit, $start, $order_field, $order_ascdesc);
    $sql_filter     = $this->mhasil->count_filter_hasil_mbti($search);

    //print_r($sql_data);die;

    $callback       = array(
        'draw'              => $_POST['draw'],
        'recordsTotal'      => $sql_total,
        'recordsFiltered'   => $sql_filter,
        'data'              => $sql_data,
    );

    //header('Content-Type: application/json');
    echo json_encode($callback);
}

Soufiane Lamnizeh · Accepted Answer · 2019-08-09 14:03:11Z

1

you can use json_decode to convert it to a array like bellow:

Example:

 $json = '{"Introversion":"53.3%","Extroversion":"46.7%"}';
 $array = json_decode($json)
 var_dump($array);

I suggest to change the name of the variable from $sql to $results because you return a array and not the sql

update 1:

foreach ($sql as $key => $val) {
// you can add in the second argument of json_decode 1 to have a array instead of a object.
if(isset($val['kepribadian']))
    $sql[$key]['kepribadian'] = json_decode($val['kepribadian']);
}

the result wil be like this:

    Array
(
    [0] => Array
        (
            [kepribadian] => stdClass Object
                (
                    [Introversion] => 53.3%
                    [Extroversion] => 46.7%
                    [Sense] => 66.7%
                    [Intuition] => 33.3%
                    [Thingking] => 46.7%
                    [Feeling] => 53.3%
                    [Judging] => 66.7%
                    [Perceiving] => 33.3%
                )

        )

    [1] => Array
        (
            [id] => 1337
            [refid] => GAD25D335B0F1DE3B
            [fullname] => Ravhi Rizaldi
            [kepribadian] => 
            [jam_mulai] => 2019-08-09 01:16:00
            [jam_selesai]] => 2019-08-09 01:19:46
        )

)

edited Aug 9, 2019 at 14:03

answered Aug 9, 2019 at 13:04

Soufiane Lamnizeh

4342 silver badges11 bronze badges

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

6 Comments

ravhirzldi Over a year ago

how to decode it when the data is on the result_array()? it's like array in the array and thanks for your sugestion :)

Soufiane Lamnizeh Over a year ago

can you give me a print_r of the result in the description ? the structure

ravhirzldi Over a year ago

Array ( [0] => Array ( [kepribadian] => {"Introversion":"53.3%","Extroversion":"46.7%","Sense":"66.7%","Intuition":"33.3%","Thingking":"46.7%","Feeling":"53.3%","Judging":"66.7%","Perceiving":"33.3%"} ) [1] => Array ( [id] => 1337 [refid] => GAD25D335B0F1DE3B [fullname] => Ravhi Rizaldi [kepribadian] => [jam_mulai] => 2019-08-09 01:16:00 [jam_selesai] => 2019-08-09 01:19:46 ) )

ravhirzldi Over a year ago

I got this error : A PHP Error was encountered Severity: Notice Message: Undefined offset: 0

Soufiane Lamnizeh Over a year ago

yes i have also updated my reponse update 1, try it

|

F.Madsen · Accepted Answer · 2019-08-09 13:18:15Z

0

I guess you will get an array of data from your select statement where the "kepribadian" column is one of the values. So what you have to do is the DECODE this column from JSON to a PHP array.

In principle like this, (not tested)

...
$sqlrec = filter_hasil_mbti(...  ...);
$phpArr = json_decode($sqlrec['kepribadian']);
...

answered Aug 9, 2019 at 13:18

F.Madsen

7124 silver badges6 bronze badges

Comments

Lucas · Accepted Answer · 2019-08-09 13:21:37Z

0

You can try

<?php
    $json_decoded = json_decode(array_column($rsult, "kepribadian"), true);
 ?>

If you add true parameter in json_decode() the result will be given as a PHP array.

Have fun :)

answered Aug 9, 2019 at 13:21

Lucas

731 gold badge1 silver badge8 bronze badges

Collectives™ on Stack Overflow

Decode stored JSON value in Database result in result_array()

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related