I'm using two tables to hold information about cars. In the first table there is a information about car manufacturer etc and the second contains images for each car. My tables looks like this:
car_information car_images
--------------- ---------
id | 2 car_id 2 | img1
car_make | Fiat car_id 2 | img2
color | Blue car_id 2 | img3
etc ....
As you can see each car has three images.
Here is query I'm using to fetch the result;
$this->db->select('
c.id,
c.car_make,
c.car_color,
ci.image
');
$this->db->from('car_information c');
$this->db->join('car_images ci', 'ci.car_id = c.id', 'left');
return $this->db->get();
Everything works fine but the problem I'm facing is that results contain redundant data.
For example:
2 Fiat Blue img1
2 Fiat Blue img2
2 Fiat Blue img3
3 BMW white img4
3 BMW white img5
How can I get a result to looks like this?
2 Fiat Blue img1 | img 2 | img 3
I want to get all information in a single row. I know that I can you two queries, but I'm wondering how to do with a single query.
Add the following line: $this->db->group_by('c.id');
like so:
$this->db->select('
c.id,
c.car_make,
c.car_color,
ci.image
');
$this->db->from('car_information c');
$this->db->join('car_images ci', 'ci.car_id = c.id', 'left');
$this->db->group_by('c.id');
return $this->db->get();
This will work in MySQL because group by
can be used as a synonym for distinct
in MySQL.