view all products using group by in codeigniter

Question

i've this client who has a employee list and he wants to display all employees of same category(in group), i'm sorry if i'm not able to explain, i will try to explain

----------------------------
id | e_id | e_pos   | e_name
-----------------------------
 1 | 142  |   dev   | abc
 2 | 143  |  sr.dev | bac
 3 | 144  |  intern | jlk
 4 | 145  |   dev   | jlsd
 5 | 146  |   dev   | asdf
 6 | 147  |  sr.dev | adsc
 7 | 148  |  intern | mlkn
 8 | 149  |  sr.dev | vjll
 9 | 150  |   dev   | knmk
10 | 151  |   dev   | jlkm
----------------------------

how to show the output

Number of dev(5) 1) abc 2) jlsd 3) asdf 4) knmk 5) jlkm Number of intern(2) 1) jlk 2) mlkn Number of sr.dev(3) 1) bac 2) adsc 3) vjll now the problem is i've tried this piece of code

$this->db->select('e_id,e_pos,e_name');
$this->db->group_by('e_pos');
$query = $this->db->get('emp'); // table name
if($query->num_rows() > 0)
{
    return $query->result();
}

but the above code is not working, it's just showing dev and number of dev, i want to see all dev data.

dev

I'm using Codeigniter 2.2.0

`pivot` might help you... http://stackoverflow.com/questions/18629155/pivot-table-in-mysql-convert-to-pivot-table-with-values-in-varchar-format — Mohammed Sufian, Jan 27 '15 at 12:43
You have formed wrong query here. It will not work using group by clause. — , Jan 28 '15 at 10:40
You can use order by clause on 'e_pos'.which will list all employee with same category together. Like, SELECT `e_pos` , `e_id` , `e_name` FROM dv_test ORDER BY `e_pos` — , Jan 28 '15 at 10:50

score 0 · Answer 1 · answered Jan 28 '15 at 10:41

You can use this CI code to get your data (detailed docs on this link):

$query = $this->db->query("YOUR QUERY");

and then pass in following query:

select test.e_pos, test.e_name, inn.cnt 
from test
inner join 
    (SELECT e_pos, e_name, count(e_pos) as 'cnt' 
    FROM `test`
    group by e_pos ) inn on test.e_pos = inn.e_pos 
order by test.e_pos

You will get table like this, which you can then iterate to display result: enter image description here

foreach ($query->result() as $row)
{
  // echo data as desired 
}

view all products using group by in codeigniter

1 Answers1