greenarrow greenarrow - 1 month ago 8
MySQL Question

Joining two tables in codeginiter

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.

Screen1

i have tried this in my model

$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

https://s4.postimg.org/ot5nyr1hp/Untitled_122.jpg

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

Answer

No need to write separate query for getting role name. Join Roles table when you fetching users data..

$this->db->select('users_mlh.*,user_roles_mlh.role_type');
$this->db->from('users_mlh');
$this->db->join('user_roles_mlh', 'user_roles_mlh.id = users_mlh.role');

$query = $this->db->get();

return $query->result_array();

Try this.