phpmysqlcodeignitergroupingresultset

CodeIgniter SELECT query with JOIN to create a delimited string from one-to-many related data


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.


Solution

  • 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.