dwiyanto dwiyanto - 2 months ago 7
MySQL Question

Group by id from the substring queries on CodeIgniter

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

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();   
}       
Comments