如何在 MySQL 中的 IF 条件下修复 Codeigniter 中的“发生数据库错误”？

Question

我是Codeigniter的新手，使用IF 语句时 mysql 出现错误，这是我的代码$this->db->select('A.city_id, A.`name` AS city_name, B.`name` AS state_name, C.`name` AS country_name, IF(`A.visible`,"Yes","No") AS active');$this->db->from('abs_city AS A');$this->db->join('abs_state AS B', 'A.state_id = B.state_id AND B.visible = 1 AND A.country_id = B.country_id ', 'inner');$this->db->join('abs_countries AS C', 'A.country_id = C.country_id AND B.visible = 1 ', 'inner' );#$this->db->where('A.visible = 1');return $this->db->get()->result();浏览器中的错误在 phpmyadmin 中运行查询后删除标记（`）    SELECT    `A`.`city_id`,    `A`.`name` AS city_name,    `B`.`name` AS state_name,    `C`.`name` AS country_name,IF (A.visible, "Yes", "No") AS activeFROM    (`vbs_abs_city` AS A)INNER JOIN `vbs_abs_state` AS B ON `A`.`state_id` = `B`.`state_id`AND B.visible = 1AND A.country_id = B.country_idINNER JOIN `vbs_abs_countries` AS C ON `A`.`country_id` = `C`.`country_id`AND B.visible = 1请帮我解决这个问题，在此先感谢。

Answer 1

您可以使用 调用 select 方法FALSE as the last parameter，如下所示

$this->db->select('A.city_id, A.`name` AS city_name, B.`name` AS state_name, C.`name` AS country_name, IF(`A.visible`,"Yes","No") AS active',false);

这将阻止 CI 在您的查询中添加 `

Answer 2

尝试将您的查询更新为

$this->db->select('A.city_id, A.`name` AS city_name, B.`name` AS state_name, C.`name` AS country_name, IF(`A.visible`,"Yes","No") AS active',false);

$this->db->from('abs_city AS A');

$this->db->join('abs_state AS B', 'A.state_id = B.state_id AND B.visible = 1 AND A.country_id = B.country_id ', 'inner');

$this->db->join('abs_countries AS C', 'A.country_id = C.country_id AND B.visible = 1 ', 'inner' );

#$this->db->where('A.visible = 1');

return $this->db->get()->result();

Answer 3

不要将false参数添加到您的 SELECT 语句（这本质上是不安全的），而是尝试将语句更改为：

$this->db->select('A.city_id, A.`name` AS city_name, B.`name` AS state_name, C.`name` AS country_name, IF(A.visible,"Yes","No") AS active');

（只需删除周围的反引号A.visible）

这是一个比添加FALSE参数更安全的版本。后者将阻止查询生成器添加到已构建语句的自动转义，从而使整个 select 语句的安全性降低，这是不可取的