Codeigniter Foreign Key data retrieve

Question

I'm using codeigniter 3.0 and new to it. This is my database structure. How to access person name through single command.

This is my table 1:

CREATE TABLE IF NOT EXISTS `person` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(45) NOT NULL,PRIMARY KEY (`id`));

This is Table 2:

CREATE TABLE IF NOT EXISTS `work` (`ID` int(11) NOT NULL AUTO_INCREMENT,  `personname` int(11) NOT NULL,  `date` varchar(220) NOT NULL,  PRIMARY KEY (`ID`),  FOREIGN KEY (`personname`) REFERENCES person(id))

Here is my model for getting data:

$query = $this->db->get('work');
    return $query;

it only returns person id. But i need name of the person.

Answer 1

Try:

$query = $this->db->select('*')
                  ->from('work')
                  ->join('person', 'person.id = work.personname')
                  ->get();

return $query;

Use it like this:

foreach ($query->result() as $row)
{
        echo $row->name;
}

You can read the docs here on how to use the query builder:

http://www.codeigniter.com/user_guide/database/query_builder.html