Loading Relational Data from Views in CodeIgniter

Question

I am using CodeIgniter

I have two tables -

Company int id , varchar name

Employee int id, varchar name , int company_id

I have a simple controller called Employees Controller

<?php 

class Employees extends CI_Controller {

    public function index()
           {

----
----
$data['employees'] = $selected_employees;
$this->load->view('employees/index',$data);
}

}

This controller passes an array of Employees to the view . So inside my view , I can freely use employees[4].name , employees[3].id etc

Now If I want to show the name of the Company of the employees , it seems the only way is the Controller should pass another array with the name of the companies to the view . Is there any better way to achieve this - so that the controller doesnt have to explicitly have to send the data ?

For eg - say I want to access employees[4].company.name I have been spoilt by Rails . I used to take these for granted .

how do you get that data? querying it right? why don't you do a join? — Fabio Antunes, Mar 08 '13 at 14:56
I've noticed that you are used to orm you can read more about that on this question http://stackoverflow.com/questions/3438198/which-orm-for-codeigniter, codeigniter doesn't support orm out of the box you must had some sorf of plugin if you don't want to that I can post a query with a join that would solve your problem — Fabio Antunes, Mar 08 '13 at 15:04
Sorry, do you still need the join or are you going to implement ORM — Fabio Antunes, Mar 08 '13 at 15:11
I think adding an ORM is going to be tedious for a small need . Join would be good . — geeky_monster, Mar 08 '13 at 15:21
Take a look here: [active record joins](http://ellislab.com/codeigniter/user-guide/database/active_record.html) — swatkins, Mar 08 '13 at 15:25

score 4 · Accepted Answer · answered Mar 08 '13 at 15:47

The best way to go about this is using a join statement in your SQL query. The SQL query would typically look something like the following:

SELECT * FROM Employee JOIN Company ON Employee.company_id = Company.id

However, CodeIgniter's active record class will help us to simplify this (see http://ellislab.com/codeigniter/user-guide/database/active_record.html). In your model, you could write your query like so:

$this->db->select('*');
$this->db->from('Employee');
$this->db->join('Company', 'Employee.company_id = Company.id');
$query = $this->db->get();

You can tweak this to select the exact data that you want like you would any SQL query:

$this->db->select('Employee.id, Employee.name, Employee.company_id, Company.name AS company_name');

You can also add left, right, outer, inner, left outer, or right outer as a third parameter to the $this->db->join() function to allow for left joins, right joins, etc.

I would recommend using the Active Record class when possible in your CodeIgniter applications. It will help keep your queries clean, organized, and readable.

Thanks RJ for the elaborate answer . Cheers !! – geeky_monster Mar 09 '13 at 02:11 — geeky_monster, Mar 09 '13 at 02:11

score 2 · Answer 2 · answered Mar 08 '13 at 15:33

2

Do a join on your model

public function get_ selected_employees(){
    $this->db->select('Employee.*, Company.name as company_name');
    $this->db->from('Employee');
    $this->db->join('Company', 'Employee.company_id = Company.id');
    return $this->db->get()->result();
}

To get the company name just do employees[4].company_name

answered Mar 08 '13 at 15:33

Fabio Antunes

22,251
15
81
96

1

This is fine but there will be a conflict as both tables have `name` fields, just need to use `$this->db->select('Employee.*, Company.name as company_name');` or similar. – Adam Westbrook Mar 08 '13 at 15:39
Thanks Fabio. This is of great help . I hadnt thought of the possibility of using a join . Cheers ! – geeky_monster Mar 09 '13 at 02:11

Loading Relational Data from Views in CodeIgniter

2 Answers2