get the result from table with relationship

[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

Code:

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($this->limit, $offset)->result();
// 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,
                                    //HOW CAN I GOT THE COMPANY NAME ?,
                            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 = 10, $offset = 0){
        $this->db->order_by('person_id','asc');
        return $this->db->get($this->person, $limit, $offset);
    }
    
    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]frist44[/eluser]
Look at the syntax for joins in the database library and in the active record syntax, join the company table on the company_id column that's in both person and company tables.

[eluser]thevenin[/eluser]
Change your model method:

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');
        
    $query = $this->db->get();    
    return $query->result();
}

and in controller:

Code:

$persons = $this->person_model->get_paged_list(10,0);

...

foreach($persons as $person) {
  echo $person->company;
}

[eluser]Angkor[/eluser]

Code:

A Database Error Occurred

Error Number:

ERROR: column person.* does not exist LINE 1: SELECT "person"."*", "company"."company_name" as company ^

SELECT "person"."*", "company"."company_name" as company FROM "person" LEFT JOIN "company" ON "person"."company_id" = "company"."company_id" LIMIT 10

[eluser]Angkor[/eluser]
@thevenin I found the mistake ,thank for quick answers.

[eluser]thevenin[/eluser]
Try this one:

Code:

function get_paged_list($limit = 0, $offset = 0) {
    $this->db->limit($limit, $offset);
    $this->db->select("p.*, c.company_name as company");
    $this->db->from('person as p');
    $this->db->join('company as c','p.company_id = c.company_id','left');
        
    $query = $this->db->get();    
    return $query->result();
}

[eluser]Angkor[/eluser]
[quote author="thevenin" date="1272336455"]Try this one:

Code:

function get_paged_list($limit = 0, $offset = 0) {
    $this->db->limit($limit, $offset);
    $this->db->select("p.*, c.company_name as company");
    $this->db->from('person as p');
    $this->db->join('company as c','p.company_id = c.company_id','left');
        
    $query = $this->db->get();    
    return $query->result();
}

[/quote]

If I need join more with position table to get the title.
Please help achieved this?

[eluser]thevenin[/eluser]

Code:

function get_paged_list($limit = 0, $offset = 0) {
    $this->db->limit($limit, $offset);
    $this->db->select("p.*, c.company_name as company, pos.title");
    $this->db->from('person as p');
    $this->db->join('company as c','p.company_id = c.company_id','left');
    $this->db->join('position as pos','p.position_id = pos.position_id','left');
        
    $query = $this->db->get();    
    return $query->result();
}