Need help on using MySQL join in codeigniter

Question

I've a table called users.

and the structure for "users" is as follows:

ID          int(11)
Name        varchar(50)
CreatedBy   int(11)

one thing to note that the "CreatedBy" field in any row contains the id of the user who created that row (user).

now i need to show the list of all users along with name of the creator. NOT the "CreatedBy" field, which is an integer.

for example if i have these rows in my users table:

ID          Name          CreatedBy

1           Nina              1
2           John Doe          1
3           Samir Nasri       2

then i need to show them as:

Name             CreatedBy

Nina              Nina
John Doe          Nina
Samir Nasri       John Doe

how can i do this in Codeigniter or just using the raw mysql query?

Thanks in advance

Answer 1

have a look at the documentation for active record within the database, no need for writing sql queries.

http://ellislab.com/codeigniter/user-guide/database/active_record.html

$this->db->join('users u', 'u.id=c.createdby', 'left');
$query = $this->db->get('users c');

Answer 2

Pretty sure you can join tables to eachother, in which case the following should work:

select u.name, c.name from users u left join users c on (u.id=c.createdby)