我有一个使用UNION的查询:
$this->db->query('
SELECT DISTINCT users.user_pic, users.id, users.username, contacts.accepted
FROM users
LEFT JOIN contacts ON users.id = contacts.user_1
WHERE contacts.user_2 = ' . $this->session->userdata('user_id') . '
UNION DISTINCT
SELECT DISTINCT users.user_pic, users.id, users.username, contacts.accepted
FROM users
LEFT JOIN contacts ON users.id = contacts.user_2
WHERE user_1 = ' . $this->session->userdata('user_id')
);
是否有一种方法可以查看会话'user_id'是否在联系人中遇到。user_1 or contacts.user_2?也许通过改变查询,或者如果我可以在视图中使用一些if语句?
Thanks in advance
乔治是的,你可以添加另一个字段来选择,你可以识别像(1,2)
$this->db->query('
SELECT 1 as table_id,DISTINCT users.user_pic, users.id, users.username, contacts.accepted
FROM users
LEFT JOIN contacts ON users.id = contacts.user_1
WHERE contacts.user_2 = ' . $this->session->userdata('user_id') . '
UNION DISTINCT
SELECT 2 as table_id,DISTINCT users.user_pic, users.id, users.username, contacts.accepted
FROM users
LEFT JOIN contacts ON users.id = contacts.user_2
WHERE user_1 = ' . $this->session->userdata('user_id')
);
我通常使用:
$id = $this->session->userdata('user_id');
则在查询中使用$id。至少在MySQL上读起来更干净,如果查询失败,它会列出$id的值。