Can not group by when creating json encode on CodeIgniter

3
votes

I'm trying to make encode json from mysql database and join table with CodeIgniter but I want to combine typing the same data into one with group_by error occurred. What is wrong?

Error alert

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'project.mytb1.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

my script 

function get_idCode() {
    $keyword = $this->uri->segment(3);
    $data = $this->db->from('mytb1')
                          ->join('mytb2', 'mytb2.id = mytb1.id')    
                          ->like('mytb1.id',$keyword)
                          ->group_by('mytb1.id')                          
                          ->get();  

    foreach($data->result_array() as $row)
    {
        $arr['query'] = $keyword;
        $arr['suggestions'][] = array(
            'value' =>$row->id,
            'name'  =>$row->name

        );
    }
    echo json_encode($arr);
}
1
phpmysqlcodeigniter

1 Answers

2
votes

Try this

$data = $this->db->select('*') # added  
                  ->from('mytb1')  # changed  
                  ->join('mytb2', 'mytb2.id = mytb1.id')    
                  ->like('mytb1.id',$keyword)
                  ->group_by('mytb1.id')                          
                  ->order_by('mytb1.id', 'ASC') # added                       
                  ->get();