CodeIgniter Forums
Joining two tables - 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: Joining two tables (/showthread.php?tid=66469)

Joining two tables - greenarrow - 10-24-2016

I have 2 tables that i want to join & show the name of user's role. here's the situation

My 2 tables are users_mlh & user_roles_mlh

on the role column of users_mlh table i'm storing the ID of user role, user_roles_mlh contains the name  & id of user role. what i want to do is show the name of the user role in my view.

my tables as follows.

[Image: image.jpg]
i have tried this in my model 


PHP Code:
$this->db->select('*');
$this->db->from('user_roles_mlh');
$this->db->join('users_mlh', 'users_mlh.role = user_roles_mlh.id');
$this->db->where('users_mlh.role = user_roles_mlh.id');
 
$query = $this->db->get();

return $query->result_array(); 
   
 but from above i get something like this

[Image: Untitled_122.jpg]

at the moment it lists all user level not the role of each individual user

RE: Joining two tables - Wouter60 - 10-24-2016

Try this:

PHP Code:
$this->db->select('u.*,r.role_type');
$this->db->from('users_mlh u');
$this->db->join('user_roles_mlh r', 'u.role = r.id');
$query = $this->db->get();
return $query->result_array(); 

In the result_array, you will now find a field named 'role_type', which is the role_type for the given user.

RE: Joining two tables - php_rocs - 10-25-2016

(10-24-2016, 11:39 AM)greenarrow Wrote: I have 2 tables that i want to join & show the name of user's role. here's the situation

My 2 tables are users_mlh & user_roles_mlh

on the role column of users_mlh table i'm storing the ID of user role, user_roles_mlh contains the name  & id of user role. what i want to do is show the name of the user role in my view.

my tables as follows.

[Image: image.jpg]
i have tried this in my model 


PHP Code:
$this->db->select('*');
$this->db->from('user_roles_mlh');
$this->db->join('users_mlh', 'users_mlh.role = user_roles_mlh.id');
$this->db->where('users_mlh.role = user_roles_mlh.id');
 
$query = $this->db->get();

return $query->result_array(); 
   
 but from above i get something like this

[Image: Untitled_122.jpg]

at the moment it lists all user level not the role of each individual user

@greenarrow - a suggestion for future queries.  I would test your queries in a database tool (first) just to make sure you are getting the data that you want (i.e. MySQL Workbench, phpMyAdmin or HeidiSQL).  Once you are sure that it is giving you the data that you want simply convert it.