How to JOIN three tables in Codeigniter

Question

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

User · Answer

try this

In your model

If u want get all album data use

  function get_all_album_data() {

    $this->db->select ( '*' ); 
    $this->db->from ( 'Album' );
    $this->db->join ( 'Category', 'Category.cat_id = Album.cat_id' , 'left' );
    $this->db->join ( 'Soundtrack', 'Soundtrack.album_id = Album.album_id' , 'left' );
    $query = $this->db->get ();
    return $query->result ();
 }

if u want to get specific album data use

  function get_album_data($album_id) {

    $this->db->select ( '*' ); 
    $this->db->from ( 'Album' );
    $this->db->join ( 'Category', 'Category.cat_id = Album.cat_id' , 'left' );
    $this->db->join ( 'Soundtrack', 'Soundtrack.album_id = Album.album_id' , 'left' );
    $this->db->where ( 'Album.album_id', $album_id);
    $query = $this->db->get ();
    return $query->result ();
 }

User · Answer

Try as follows   public function funcname  id         this- gt db- gt select            this- gt db- gt from  Album a          this- gt db- gt join  Category b    b cat id a cat id    left         this- gt db- gt join  Soundtrack c    c album id a album id    left         this- gt db- gt where  c album id   id        this- gt db- gt order by  c track title   asc                  query    this- gt db- gt get         return  query- gt result array        If no result found CI returns false otherwise true

User · Answer

Try this one for data     function get album data          this- gt db- gt select    album   cat   s track              - gt from    albums as album             - gt join    categories cat    cat cat id   album cat id           - gt join    soundtracks s tracks     s tracks album id   album album id         query    this- gt db- gt get         return  query- gt result         while for datum try this     function get album datum  album id         this- gt db- gt select    album   cat   s track              - gt from    albums as album             - gt join    categories cat    cat cat id   album cat id           - gt join    soundtracks s tracks     s tracks album id   album album id         this- gt db- gt where    album album id    album id        query    this- gt db- gt get         return  query- gt row     7   855788

User · Answer

Use this code in model  public function funcname  id         this- gt db- gt select            this- gt db- gt from  Album a          this- gt db- gt join  Category b    b cat id a cat id    left         this- gt db- gt join  Soundtrack c    c album id a album id    left         this- gt db- gt where  c album id   id        this- gt db- gt order by  c track title   asc                  query    this- gt db- gt get         if  query- gt num rows      0                return  query- gt result array              else               return false

User · Answer

public function getdata             this- gt db- gt select  c country name as country  s state name as state  ct city name as city  t id as id             this- gt db- gt from  tblmaster t              this- gt db- gt join  country c    t country c country id             this- gt db- gt join  state s    t state s state id             this- gt db- gt join  city ct    t city ct city id             this- gt db- gt order by  t id   desc             query    this- gt db- gt get                  return  query- gt result

User · Answer

Check bellow code it s working fine and  common model function also      supported more then one join and also supported  multiple where condition  order by  limit it s EASY TO USE and REMOVE CODE REDUNDANCY                                                                            Album php      put bellow code in your controller                                                                            album id      album id        pass join table value in bellow format      join str 0   table      Category        join str 0   join table id      Category cat id        join str 0   from table id      Album cat id        join str 0   join type      left         join str 1   table      Soundtrack        join str 1   join table id      Soundtrack album id        join str 1   from table id      Album album id        join str 1   join type      left          selected    Album   Category cat name Category cat title Soundtrack track title Soundtrack track url        albumData   this- gt common- gt select data by condition  Album   array  Soundtrack album id    gt   album id    selected                   join str                                    call common model function      if   empty  albumData             print r  albumData      print album data                                                                                       Common php       put bellow code in your common model file                                                                               function select data by condition  tablename   condition array   array     data         sortby        orderby        limit        offset        join str   array           this- gt db- gt select  data         if join str array is not empty then implement the join query     if   empty  join str             foreach   join str as  join                if   join  join type                             this- gt db- gt join  join  table     join  join table id            join  from table id                   else                    this- gt db- gt join  join  table     join  join table id            join  from table id     join  join type                                          condition array pass to where condition      this- gt db- gt where  condition array           Setting Limit for Paging     if   limit        amp  amp   offset    0             this- gt db- gt limit  limit         else if   limit        amp  amp   offset    0             this- gt db- gt limit  limit   offset               order by query     if   sortby        amp  amp   orderby                   this- gt db- gt order by  sortby   orderby               query    this- gt db- gt get  tablename         if limit is empty then returns total count     if   limit                   query- gt num rows                if limit is not empty then return result array     return  query- gt result array

[php] How to JOIN three tables in Codeigniter

Examples related to php

Examples related to mysql

Examples related to codeigniter

Examples related to join