I have a table as below in MYSQL:
cname | vname | curl
---------------------
A | 1 | url1
A | 2 | url2
B | 1 | url3
B | 3 | url4
C | 2 | url5
C | 3 | url5
C | 4 | url6
D | 2 | url7
And I want to show the result as under:
1 | 2 | 3 | 4
-------------------
A | A | B | C
B | C | C |
| D |
In short, I am trying to show all the cnames group by vnames.
I have tried the following code in Codeigniter:
$this->db->distinct();
$this->db->select('vname, cname, curl');
$this->db->from('tablename');
$this->db->order_by('cname');
$this->db->group_by('vname');
$res = $this->db->get();
if($res->num_rows()>0)
var_dump($res->result());
I am getting only one row per vname as a result of var_dump();
Plesae provide a solution for this problem.
Use GROUP_CONCAT
$this->db->select('vname,GROUP_CONCAT(cname SEPARATOR ",") as cname');
$this->db->from('tablename');
$this->db->group_by('vname');
$res = $this->db->get();
Output :
+--------+--------+
| vname | cname |
+--------+--------+
| 1 | A,B |
| 2 | A,C,D |
| 3 | B,C |
| 4 | C |
+--------+--------+