CODEWITHSUNDEEP
phpmysqlcodeigniterjoinactiverecord
Shourob Datta
Shourob Datta

Reputation: 2072

Join two tables and select distinct rows using CodeIgniter query builder methods

I'm new to CI and I'm having a problem with MySQL

Table1

id | house_id | 
1  |  1       | 
2  |  4       | 
3  |  3       |

Table2

house_id | image_name | 
4        |  a.jpg     | 
4        |  b.jpg     | 
3        |  c.jpg     |

How I can select (distinctively) image_name for each house_id by using CodeIgniter Active Record class?

Upvotes: 0

Views: 144

Answers (4)

Geeks Parthiban
Geeks Parthiban

Reputation: 112

Just use a simple method group_by in codeigniter model

function get_house_image(){
$items = $this->db->select('house_id', 'image_name')
->from("table1")
->join("table2", "table1.house_id = table2.house_id")
->group_by("table1.house_id")  
->get();    
return $items->result_array();
}

Upvotes: 1

Needhi Agrawal
Needhi Agrawal

Reputation: 1326

You can use left join of tables to get records as follow: 

$this->read->select('t1.house_id,t2.image_name');
 $this->read->join('table2 as t2','t1.house_id = t2.house_id','left');
 $result = $this->read->get('table1 as t1');
if($result){
      $data = $result->result_array();
      print_r($data);
}

Upvotes: 0

user1607528
user1607528

Reputation:

In your Model

function get_house_image($id){
$this->db->where('house_id',$id);
$query = $this->db->get('table2');
return $query->result();
}

In your cotroller
function house($id){
$data['house'] = $this->your_model->get_house_image($id);
$this->load->view('your_view',$data);

In your view foreach the result and use it. This gets the house's picture which id you pass in url.

Upvotes: 0

Jan Wiemers
Jan Wiemers

Reputation: 341

http://codeigniter.com/user_guide/database/active_record.html this is the API you are looking for $this->db->distinct();

Upvotes: 3

Related Questions