How to get data from 2 related tables in the same controller?

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