CodeIgniter Forums
How to get data from 2 related tables in the same controller? - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: How to get data from 2 related tables in the same controller? (/showthread.php?tid=74027)



How to get data from 2 related tables in the same controller? - dragoshjianu - 07-09-2019

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();
   }



RE: How to get data from 2 related tables in the same controller? - Wouter60 - 07-09-2019

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.


RE: How to get data from 2 related tables in the same controller? - neuron - 07-09-2019

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.


RE: How to get data from 2 related tables in the same controller? - dragoshjianu - 07-11-2019

(07-09-2019, 11:50 PM)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