如何在CodeIgniter中执行JOIN操作并获取结果？

Question

for a CI System,i have a reseller table and Users tables. each user is mapped to its reseller by a field called KEY which is unique string. It's in both users table and reseller's table each user(record) has a reseller key mapped to it. I hope you understood the concept. Now i want to create a view for admin. Currently i show the list of reseller and list of users,Now how to create a view in which when i click on reseller i can see only his reseller?

here i fetch all the reseller in index function how to filter based on key

    public function index ()
        {
            $usertype=$this->session->userdata('usertype');
            if($usertype ==="admin")
                {
                    // Fetch all users
                    $this->data['users'] = $this->reseller_m->get();
                    // Load view
                    $this->data['subview'] = 'admin/reseller/index';
                    $this->load->view('admin/_layout_main', $this->data);
                }
            else
                {
                    $this->load->view('permission');
                }   
        }

Answer 1

You can use CI active records like this

$this->db->select('*');
$this->db->from('reseller');
$this->db->join('users', 'reseller.KEY = users.KEY');
$this->db->WHERE('reseller.key',$reseller_id);
$result = $this->db->get();

Just change the result input to the view. On first page you passing result of all the user result, so if the user clicks on the specific reseller then basically you have to pass the reseller id to the controller, right? using that id just switch the query result to the view by if else condition.

    public function index ()
        {
            $usertype=$this->session->userdata('usertype');
            $reseller_id=$this->input->get('reseller_id');
            if($usertype ==="admin")
                {

              if($reseller_id){

                    // Fetch only resellers users

                  $this->db->select('*');
                  $this->db->from('reseller');
                  $this->db->join('users', 'reseller.KEY = users.KEY');
                  $this->db->WHERE('reseller.key',$reseller_id);
                  $this->data['users'] = = $this->db->get();

                  }else{
                    // Fetch all users
                    $this->data['users'] = $this->reseller_m->get();

                 }
                    // Load view
                    $this->data['subview'] = 'admin/reseller/index';
                    $this->load->view('admin/_layout_main', $this->data);
                }
            else
                {
                    $this->load->view('permission');
                }   
        }