mysql - 如何将 MySQL 查询转换为 codeigniter 事件记录？

Question

我是 codeigniter 的新手，我想将以下 MySQL 查询转换为 Codeigniter Active Record 查询。

MySQL 查询:

select sevadar_id, sevadar_name from tbl_sevadarmaster where  
sevadar_designation = 6 and sevadar_id not in (SELECT p_id FROM  
`tbl_events` WHERE program_id = 27 and event_date = 2014-07-06 and p_id is not null)

Answer 1

CodeIgniter 的整个 API 都有很好的文档，我强烈建议您快速阅读一下。具体关于您的问题，您应该查看 Active Record 文档 here在 CodeIgniter query library ，它将为您提供有关使用 API 生成更安全查询的提示。这两个链接一起包含您在 CodeIgniter 中构建查询所需的所有 API 调用。

在最简单的层面上你可以做到:

$query = $this->db->query('select sevadar_id, sevadar_name from tbl_sevadarmaster where sevadar_designation = 6 and sevadar_id not in (SELECT p_id FROM `tbl_events` WHERE program_id = 27 and event_date = 2014-07-06 and p_id is not null)');

但是您需要使用查询绑定(bind)(请参阅 query libraries 页面的最底部)来自动转义数据以生成更安全的查询，以下是使用该 API 页面中的查询绑定(bind)的示例:

$sql = "SELECT * FROM some_table WHERE id = ? AND status = ? AND author = ?"; 
$this->db->query($sql, array(3, 'live', 'Rick'));

如果您使用 Active Record 库，您可以尝试:

$this->db->select('p_id');
$this->db->from('tbl_events');
$this->db->where('program_id', 27);
$this->db->where('event_date', '2014-07-06');
$this->db->where('p_id is not', null);
$where_clause = $this->db->get_compiled_select();

$this->db->select('sevadar_id, sevadar_name');
$this->db->from('tbl_sevadarmaster');
$this->db->where('sevadar_designation', 6);
$this->db->where("`sevadar_id` NOT IN ($where_clause)", NULL, FALSE);