CODEWITHSUNDEEP
phpmysqlcodeigniter
AL-zami
AL-zami

Reputation: 9066

keep query outside of "VIEW" in codeigniter

i am quite new to codeigniter.I am in a situation where i have two table and they have one to many relationship between them.The schema is the following.One is the employee table and the other is the table for vacation application

employee(id,name),

application(id,start_date,end_date,reason,user_id),

plan is to display all the application in a single page showing start_date,end_date,reason and name.For querying the application table i have a admin controller as only admin can query the application table.And there is another userController to get data from employee table.In admin Model i have the following query to get all applications

class Adminmodel extends CI_Model{
  public function fetchAllRequest(){
    $q=$this->db->get('application');
    if($q->num_rows()){
       return $q->result_array();
    }else{
       return false;
    }

   }

Admin controller is the following :

 class Admin extends CI_Controller{

         public function index(){
             $this->load->model('Adminmodel');
             $data['request']=$this->Adminmodel->fetchAllRequest();
             $this->load->view('admin/index',$data);
         }

   }
    }

it bring me a result like this:

Array ( 

     [0] => Array ( 

         [id] =5 

         [start] => 05/04/2016 

         [end] => 05/12/2016 

         [reason] => sick [status] => Pending 

         [employee_id] => 1

I am giving a single row.There can be hundreds of applications .My problem is i need to grab the employee_id from each row and use it to fetch name from employee table.

One solution is i can go through each row in a foreach loop inside view and for each of them i call the user model passing employee id and fetch data.

If i do that i need to use user model inside "VIEW" which is not a MVC approach.How i can solve this problem.Thanks !!

Upvotes: 1

Views: 173

Answers (1)

Rana Soyab
Rana Soyab

Reputation: 896

In CI 2: check Join https://www.codeigniter.com/userguide2/database/active_record.html#select

In CI 3: check join http://www.codeigniter.com/user_guide/database/query_builder.html#selecting-data

you can join both table by employee.id with application.user_id

$this->db->select('*');
$this->db->from('application');
$this->db->join('employee', 'employee.id = application.user_id');
$query = $this->db->get();

Upvotes: 1

Related Questions