How To generate json format from model in codeigniter

Question

I Have Database :

enter image description here

how to query mysql in codeigniter model so that it can output json data like this :

enter code here</script"> $.ajax({ url: '?grid=true&tahun=' + tahuncikarang, method: "GET", success: function(data) { var obj = JSON.parse(data);

         }
        Highcharts.chart('cikarang', {
            chart: {
                type: 'bar'
            },
            title: {
                text: 'Highcharts multi-series drilldown'
            },
            subtitle: {
                text: 'The <em>allowPointDrilldown</em> option makes point clicks drill to the whole category'
            },
            xAxis: {
                type: 'category'
            },

            plotOptions: {
                series: {
                    borderWidth: 0,
                    dataLabels: {
                        enabled: true
                    }
                }
            },

            series: [{
                name: '2010',
                data: obj.tahun1
            }, {
                name: '2014',
                data: obj.tahun2
            }],
            drilldown: {
                allowPointDrilldown: false,
                series: [{
                    id: 'republican-2010',
                    name: 'Republican 2010',
                    data: [
                        ['East', 4],
                        ['West', 2],
                        ['North', 1],
                        ['South', 4]
                    ]
                }, {
                    id: 'democrats-2010',
                    name: 'Republican 2010',
                    data: ''
                }, ]
            }
        });
    }

});

I try in model : function index($tahuncikarang) {

    $this->db->select('nama AS nama, CONCAT(nama,"-", tahun) AS drilldown, sum(nilai) AS y');
    $this->db->where('tahun=2010');
    $this->db->group_by('nama')
              ->group_by('tahun');
    $tahuna1  = $this->db->get(self::$table5);
    $tahuna2 = array();
    foreach ($tahuna1->result() as $row) {
        array_push($tahuna2, $row);
    }

    $this->db->select('nama AS nama, CONCAT(nama,"-", tahun) AS drilldown, sum(nilai) AS y');
    $this->db->where('tahun=2014');
    $this->db->group_by('nama')
        ->group_by('tahun');
    $tahunb1  = $this->db->get(self::$table5);
    $tahunb2 = array();
    foreach ($tahunb1->result() as $row) {
        array_push($tahunb2, $row);
    }
    $result = array();
    $result['tahun1']    = $tahuna2;
    $result['tahun2']    = $tahunb2;
    return json_encode($result, JSON_NUMERIC_CHECK);
} 

Answer 1

I read your question just before. If you could use this query, you maybe get good results. Thank you

$qy = $this->db->query("select concat(lower(nama),'-',tahun) as id,concat(nama,'-',tahun) as name,group_concat(concat(zone,'-' ,nilai),',') as data group by nama, tahun")->result_array();

$result = array(); 
    foreach($qy as $row) { 
        $temp = array( 
            "id"=>$row['id'], 
            "name"=>$row['name'], 
            "data"=>array() 
        ); 
        $array = explode(',',$row['data']); 
        foreach($array as $el) { 
            $temp['data'][]=explode('-',$el); 
        } 
        $result[] = $temp; 
    }

echo json_encode($result);