从CodeIgniter上的子字符串查询中按ID分组

Question

I have data from the database, and get the two characters from the id, I do not understand how to calculate data from the substring with the same id?

Example:

id = AA001, AA002, AA003, AB005, AB006, AC009, AC010, AC012, AC015

Result substring = AA, AA, AA, AB, AB, AC, AC, AC, AC,

I expected = AA:3, AB:2, AC:4

This my controllers

function mySubstr() { 
        $data=array('section'   => $this->mSubstr->get_substr(),
                    'isi'       =>'home/substr');
        $this->load->view('layout/wrapper', $data); 
 }

Models

function get_substr() {
    $query = $this->db
            ->select('LEFT(id, 2) as id', FALSE)
            ->from('tbSubstr')
            ->group_by('id')
            ->get();
    return $query->result();   
}

Views

<?php
    foreach ($section as $row) {
        echo json_encode($row->id); 
    }
?>

Answer 1

Try this will may help you,

function get_substr() {
   $query = $this->db
        ->select('LEFT(id, 2) as sub_id , COUNT(*)', FALSE)
        ->from('tbSubstr')
        ->group_by('sub_id')
        ->get();
   return $query->result();   
}