• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
How to get data from 2 related tables in the same controller?

#1
I'm trying to get data from 2 DB tables that are related. Departments and Functions: Each Department has multiple Functions.
I want to show all data in an accordion on the same page.
Until now, the code works like this: Declare the variable departments in the controller, and in view, in the foreach loop, I have called the Functions model in a variable.
Controller:
Code:
public function index(){
       //Data variable declarations
       $data['page_title'] = "Departments and Functions";
       $data['page_subtitle'] = "choose an occupation for the list of users assigned";

       //Data variable declaration - from models
       $data['departments'] = $this->Department->get_all('departments');

       $this->load->view('layouts/header');
       $this->load->view('layouts/title', $data);
       $this->load->view('layouts/aside');
       $this->load->view('departments/index', $data);
       $this->load->view('layouts/footer');
   }

Here I want to add the Function->get_by_department_id() method. How to get the ID dynamically?
The occupation model method:
Code:
public function get_by_fk($table = 'occupations', $fk = "department_id" , $fk_value=$department->department_id){
       $this->db->select('*');
       $this->db->from($table);
       $this->db->where($fk, $fk_value);
       $query = $this->db->get();

       return $result = $query->result();
   }
Reply

#2
Use JOIN to get the result from both tables at once.
The CI documentation explains it very well.
If you prefer the query builder, look for $this->db->join() AND $this->db->order_by().
Once you get all the data in an array, you can fill the accordion in your view with it.
Reply

#3
PHP Code:
$this->db->select('d.*, o.*')
->
from('departments d')
->
join('occupations o''o.department_id = d.department_id''LEFT'


Simple join it is all you have to do, and you should learn JOIN clause in SQL.
Reply

#4
(07-10-2019, 12:50 AM)neuron Wrote:
PHP Code:
$this->db->select('d.*, o.*')
->
from('departments d')
->
join('occupations o''o.department_id = d.department_id''LEFT'


Simple join it is all you have to do, and you should learn JOIN clause in SQL.

Thank you both for the replies. It worked perfectly
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


Users browsing this thread:
1 Guest(s)


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.