使用CodeIgniter从2个表中选择sql - Select in sql from 2 tables using CodeIgniter

Select in sql from 2 tables using CodeIgniter

本文关键字：选择 sql 2个 CodeIgniter 使用 | 更新日期: 2023-09-27

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

USER
id       name        is_admin        is_verificator
DOCUMENT
id       date        admin           verificator

文档。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;
}

请帮帮我

您可以这样构造查询:

$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);