Snippet: Codeigniter Join 3 tables

on Snippet Written by Sirwan Qutbi.

A snippet for joining multiple tables in codeigniter.

public function funcname($id) { $this->db->select('*'); $this->db->from('Album a'); $this->db->join('Category b', 'b.cat_id=a.cat_id', 'left'); $this->db->join('Soundtrack c', 'c.album_id=a.album_id', 'left'); $this->db->where('c.album_id',$id); $this->db->order_by('c.track_title','asc');
$query = $this->db->get(); if($query->num_rows() != 0) { return $query->result_array(); } else { return false; } }

Important, if you have and and you use select(''), the first id field will not show up. You need to manually select(' as id1, as id2, mytable., mysecondtable.*')... codeigniter joins don't include the table prefix so the recordset doesn't return duplicate fields