How to create json data from database query in codeigniter?

Question

I am trying to call data from database and put them into json format but I can't format the data as I want. I want this kind of json format:

[{
    name: 'Revenue',
    data: [49.9, 71.5, 106.4, 129.2, 144.0, 176.0, 135.6, 148.5, 216.4, 
194.1, 95.6, 54.4]

}, {
    name: 'Top5',
    data: [83.6, 78.8, 98.5, 93.4, 106.0, 84.5, 105.0, 104.3, 91.2, 83.5, 106.6, 92.3]

}]

Are there any references for me to do that? Here is my code that I have tried

$myData = array();
$data_top5 = $this->db->get_where('top_five_kfa', array('kfa' => 'Aceh', 'produk' => 'Fituno'))->result();
foreach ($data_top5 as $key => $value){
        $myData[$key]['revenue'] = $value->revenue;
        $myData[$key]['top5'] = $value->top5;
    }


$data['myData'] = json_encode($myData, JSON_NUMERIC_CHECK);
var_dump($data);

But from the code I made I got this kind of json format:

array(1) { ["top5"]=> string(1179) "[{"revenue":49.9,"top5":83.6},{"revenue":71.5,"top5":78.8},{"revenue":106.4,"top5":98.5},{"revenue":129.2,"top5":93.4},{"revenue":144.0,"top5":106.0},{"revenue":176.0,"top5":84.5},{"revenue":135.6,"top5":105.0},{"revenue":148.5,"top5":104.3},{"revenue":216.4,"top5":91.2},{"revenue":194.1,"top5":83.5},{"revenue":95.6,"top5":106.6},{"revenue":54.4,"top5":92.3}]" }

This kind of json can't be used in highcharts

Hi @Saepul Anwar, You have used highcharts tag, but the problem doesn't seem to be about a chart - rather about data. If you'll have problems with Highcharts then please provide a demo with hard-coded, exemplary data. — ppotaczek

ZGuard ZGuard · Accepted Answer · 2019-09-05T18:20:52

Something like this, untested. Your mileage may vary:

$keys = array('Revenue', 'Top5');
foreach($data_top5 as $key => $value) {
    if (empty($keys[$key]) {
        $keys[$key] = array('name' => $key, 'data' => array());
    }
    $keys[$key]['data'][] = $value->{$key};
}
$result = json_encode(array_values($keys));

How to create json data from database query in codeigniter?

1 Answers