php codeigniter 活动记录左连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23486536/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
codeigniter active record left join
提问by user1884324
I have 3 mysql tables.
我有 3 个 mysql 表。
Table 1 user
id | name
Table 2 emails
id | email
Table 3 user_email
user_id | email_id
I have no experience in query multi tables.
Using codeigniter active record, i want to find out the user email address based on the user id, pls advise if the below code is correct ?
我没有查询多表的经验。
使用codeigniter活动记录，我想根据用户ID找出用户电子邮件地址，请告知以下代码是否正确？
$CI->db->select('email');
$CI->db->from('emails');
$CI->db->where('id', $userid);
$CI->db->join('user_email', 'user_email.user_id = emails.id', 'left');
$query = $CI->db->get();
回答by M Khalid Junaid
You have wrong where clause you need to compare user_id from your table ,you are comparing the id of email to the provided $user_id
您有错误的 where 子句，您需要从表中比较 user_id ，您正在将电子邮件的 id 与提供的 ID 进行比较 $user_id
$CI->db->select('email');
$CI->db->from('emails');
$CI->db->where('user_id', $userid);
$CI->db->join('user_email', 'user_email.user_id = emails.id', 'left');
$query = $CI->db->get();
A more useful way is to give aliases to your tables so the tables with same columns will not have any confusion
一种更有用的方法是为您的表提供别名，这样具有相同列的表就不会产生任何混淆
$CI->db->select('e.email');
$CI->db->from('emails e');
$CI->db->join('user_email ue', 'ue.user_id = e.id', 'left');
$CI->db->where('ue.user_id', $userid);
$query = $CI->db->get();
回答by rahim.nagori
@m khalid 's answer is correct but I have created a dynamic function to achieve join with multiple tables. Check this out.
@m khalid 的答案是正确的，但我创建了一个动态函数来实现与多个表的连接。看一下这个。
function join_records($table, $joins, $where = false, $select = '*', $orderBy = false, $direction = 'DESC'){
$CI->select($select);
$CI->from($table);
foreach($joins as $join){
$CI->join($join[0], $join[1], $join[2]);
}
if($where) $CI->where($where);
if($orderBy) $CI->order_by($orderBy, $direction);
$query = $CI->get();
return $query->result_array();
}
Applying your question to this.
将您的问题应用于此。
$table = 'emails';
$joins[0][0] = 'user_email';
$joins[0][1] = 'user_email.user_id = emails.id';
$joins[0][2] = 'left';
$where['user_id'] = $userid;
You may add more join like $join1[0].. If you need to select some specific column you can define in following manner $select = 'table1.column1, table1.column2, table2.column1, table2.column2' Or if you want all the columns put a *
您可以添加更多的连接，如 $join 1[0].. 如果您需要选择某些特定的列，您可以按以下方式定义 $select = 'table1.column1, table1.column2, table2.column1, table2.column2' 或者如果你希望所有的列都放一个 *
$this->join_records($table, $joins, $where, $select);
