需要有关在codeigniter中使用MySQL join的帮助

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).

有一点需要注意,任何行中的“CreatedBy”字段都包含创建该行(用户)的用户的id。

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

现在我需要显示所有用户的列表以及创建者的名称。不是“CreatedBy”字段,它是一个整数。

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?

我如何在Codeigniter中执行此操作或仅使用原始mysql查询?

Thanks in advance

提前致谢

2 个解决方案

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

看一下数据库中活动记录的文档,不需要编写SQL查询。

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

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)

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

看一下数据库中活动记录的文档,不需要编写SQL查询。

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

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)