join 03 table ?

[eluser]Angkor[/eluser]
with my table.

Code:

person_id serial NOT NULL,
  firstname character varying(30) NOT NULL,
  lastname character varying(30),
  email character varying(50),
  username character varying(20) NOT NULL,
  "password" character varying(100) NOT NULL,
  gender character varying(10),
  dob date,
  accesslevel smallint NOT NULL,
  company_id integer NOT NULL,//Reference to table company
  position_id integer NOT NULL,//Reference to table position
company_id serial NOT NULL,
  company_name character varying(80) NOT NULL,
  description character varying(255),
  address character varying(100) NOT NULL,

In my controller

Code:

........................
// load data
$persons = $this->person_model->get_paged_list(10,0);
// generate table data
$this->load->library('table');
$this->table->set_empty(" ");
$this->table->set_heading('No', 'FirstName', 'LastName','E-mail','Company''Gender', 'Date of Birth', 'Actions');
foreach ($persons as $person){
    $this->table->add_row(++$i, $person->firstname,
                                     $person->lastname,
                                     $person->email,
                                     $person->company_name,
                                    //HOW CAN I GOT THE POSITION TITLE ?,
                            strtoupper($person->gender)=='M'? 'Male':'Female',
                            date('d-m-Y',strtotime($person->dob)),
}

My model

Code:

<?php
class Person_Model extends Model {
    
    private $person= 'person';
    
    function Person(){
        parent::Model();
    }
    
    function list_all(){
        $this->db->order_by('person_id','asc');
        return $this->db->get($person);
    }
    
    function count_all(){
        return $this->db->count_all($this->person);
    }
    
    function get_paged_list($limit = 0, $offset = 0) {
         $this->db->limit($limit, $offset);
         $this->db->select("person.*, company.company_name as company");
         $this->db->from('person');
         $this->db->join('company','person.company_id = company.company_id','left');
         //CAN I JOIN MORE WITH TABLE POSITION?
        
        $query = $this->db->get();    
        return $query->result();
     }
    
    function get_by_id($id){
        $this->db->where('person_id', $id);
        return $this->db->get($this->person);
    }
    
    function save($person){
        $this->db->insert($this->person, $person);
        return $this->db->insert_id();
    }
    
    function update($id, $person){
        $this->db->where('person_id', $id);
        $this->db->update($this->person, $person);
    }
    
    function delete($id){
        $this->db->where('person_id', $id);
        $this->db->delete($this->person);
    }
}
?>

[eluser]Caio Russo[/eluser]
Hi Angkor,

Yes, u can join many tables as needed:

Code:

function get_paged_list($limit = 0, $offset = 0) {
         $this->db->limit($limit, $offset);
         $this->db->select("person.*, company.company_name as company");
         $this->db->from('person');
         $this->db->join('company','person.company_id = company.company_id','left');
         //CAN I JOIN MORE WITH TABLE POSITION? -> Yes, you can! :) (I can't resist. ehhee)
         $this->db->join('position','person.position_id = position.position_id','inner'); //or left or right

        
        $query = $this->db->get();    
        return $query->result();
}