php - 使用 CodeIgniter 从 2 个表中选择 SQL

Question

我的选择有问题，所以我的数据库中有 2 个表:

USER
id       name        is_admin        is_verificator
DOCUMENT
id       date        admin           verificator 

其中 document.admin 是链接用户表的外键

如果我插入了以下行:

1      John           Y            N
2      Patrick        N            Y

和:

1       24/08/2014    1            2

选择后我想获取此文档的验证者和管理员

John   Patrick

public function getOneDosar()
{
    $uid = (int) $this->uri->segment(3, 0);
    $this->load->database();
    $get_dosar = $this->db->query("select
                                    d.date,
                                    u1.name,
                                    u1.name,
                                    from document d
                                    join `users` u1 on(d.fk_user = u1.id and u1.is_admin = 'Y')
                                    join `users` u2 on(d.fk_verificator = u2.id and u2.is_verificator = 'Y')
                                    AND d.id_dosar = $uid");
    if ($get_dosar->num_rows())
    {
        $get_dosar = $get_dosar->result_array();
    }
    else
    {
        $get_dosar = NULL;
    }

    return $get_dosar;
}

请帮帮我

Answer 1

您可以像这样构造查询:

$this->db->select( 'd.date,u1.name, u1.name');
    $this->db->from('document d');
    $this->db->join('users u1', "d.fk_user = u1.id and u1.is_admin = 'Y'",your join like inner); 
$this->db->join('users u2 "d.fk_verificator = u2.id and u2.is_verificator = 'Y'",your join like inner);