猿问

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

我是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 active

FROM

    (`vbs_abs_city` AS A)

INNER JOIN `vbs_abs_state` AS B ON `A`.`state_id` = `B`.`state_id`

AND B.visible = 1

AND A.country_id = B.country_id

INNER JOIN `vbs_abs_countries` AS C ON `A`.`country_id` = `C`.`country_id`

AND B.visible = 1

请帮我解决这个问题,在此先感谢。


小怪兽爱吃肉
浏览 97回答 3
3回答

慕姐4208626

您可以使用 调用 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 在您的查询中添加 `

ABOUTYOU

尝试将您的查询更新为$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();

子衿沉夜

不要将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 语句的安全性降低,这是不可取的
随时随地看视频慕课网APP
我要回答