How to JOIN three tables in Codeigniter

codeigniter, join, mysql, php

Solution

Use this code in model

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;
    }
}

Problem

I'm using codeigniter framework to develop one music cms. i have 3 tables in mysql database, Currently im working in "Album" Table and "Model, Controller". i want to SELECT "Album" Table 1 and JOIN "Album" -> "cat_id" with "Category" -> "cat_id", and fetch all categories records. Then i want to JOIN "Album" -> "album_id" on "Soundtrack" -> "album_id" then fetch all soundtrack records A to Z. Please somebody help me to show proper codeigniter query, how i can SELECT and JOIN Tables then fetch records from 3 tables, ? Table 1 -> Category cat_id cat_name - cat_title date Table 2 -> Album - cat_id album_id album_title album_details Table 3 -> Soundtrack album_id track_title - track_url - date

Original source