CODEWITHSUNDEEP
phpcodeigniter
Rajan
Rajan

Reputation: 2425

How to Perform A JOIN operation in CodeIgniter and fetch results?

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

Upvotes: 0

Views: 63

Answers (2)

Gowri
Gowri

Reputation: 16835

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

Upvotes: 2

Melounek
Melounek

Reputation: 890

What about this?

// Fetch all users
$this->data['users'] = $this->get('users');
foreach($this->data['users'] as $ind=>$row){
  // then connect resellers to each user
  $this->data['users'][$ind]['resellers'] = $this->reseller_m->->where('key', $row['id'])->get();
}

First you get all users to array and connect to each user his resellers. I expect you have defined "reseller_m" as model and with method where() which i edited now you specify only recellers of user.

Or this row will do the same I think: (without defined model)

$this->data['users'][$ind]['resellers'] = $this->where('key', $row['id'])->get('resellers');

Upvotes: 1

Related Questions