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 个解决方案
#1
1
have a look at the documentation for active record within the database, no need for writing sql queries.
看一下数据库中活动记录的文档,不需要编写SQL查询。
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');
#2
1
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)
#1
1
have a look at the documentation for active record within the database, no need for writing sql queries.
看一下数据库中活动记录的文档,不需要编写SQL查询。
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');
#2
1
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)