SQL语句的反单引号问题--CI框架编程

2015-06-15
Experience
CI/MySQL/PHP

这是我在用CI框架写项目的时候遇到的一个问题。

问题描述

众所周知，mysql语句编写时，尤其是框架开发，最好加上反单引号，以防止出现保留字符而报错，CI框架也不例外。以下面一段程序为例。

1
2
3
4
5
$this->db->select ("id, lamp_num as num, lamp_name as name");
$this->db->from("lamp");
$this->db->where("problem_state_time > lamp_state_setting");
$new_data = $this->db->get()->result();
echo $this->db->last_query();

输出的sql语句就是

1
2
3
SELECT `id`, `lamp_num` as num, `lamp_name` as name
FROM (`sl_lamp`)
WHERE `problem_state_time` > lamp_state_setting

也就是说框架本身已经帮你把反单引号加上去了，非常的智能啊
但是呢，问题来了，我突然间想搜索这样的语句

1
select id, lamp_num as num, lamp_name as name, 'lamp' as problem_type from sl_lamp where problem_state_time > lamp_state_setting;

在mysql终端输入后则是下面的效果

1
2
3
4
5
6
7
8
9
+-----+------+-----------------+--------------+
| id  | num  | name            | problem_type |
+-----+------+-----------------+--------------+
|  54 | 001  | 人民北路001       | lamp         |
|  55 | 002  | 人民北路002       | lamp         |
|  59 | 006  | 人民北路006       | lamp         |
| 128 | 1004 | sklcc1004       | lamp         |
+-----+------+-----------------+--------------+
4 rows in set (0.01 sec)

若在框架里面写，一开始我是这么写的

1
2
3
4
5
$this->db->select ("id, lamp_num as num, lamp_name as name， 'lamp' as problem_type");
$this->db->from("lamp");
$this->db->where("problem_state_time > lamp_state_setting");
$new_data = $this->db->get()->result();
echo $this->db->last_query();

结果报错了，报错信息为Error Number: 1054; Unknown column ''lamp'' in 'field list'
输出的sql语句则是

1
SELECT `id`, `lamp_num` as num, `lamp_name` as name, `'lamp'` as problem_type FROM (`sl_lamp`) WHERE `problem_state_time` > lamp_state_setting;

解决方法

也就是说如果反单引号扩起来的话，就会直接默认里面的内容作为属性名，这明显不是我想要的啊。后来查了官方手册，明白了原来select函数总共有两个参数，前一个不用多说就是查找内容，后一个则是关于反单引号的开关，默认是打开，关闭的话只要再添一个参数FALSE即可。

1
2
3
4
5
$this->db->select ("`id`, `lamp_num` as num, `lamp_name`as name， 'lamp' as problem_type", FALSE);
$this->db->from("lamp");
$this->db->where("problem_state_time > lamp_state_setting");
$new_data = $this->db->get()->result();
echo $this->db->last_query();

则sql语句为

1
2
3
SELECT `id`, `lamp_num` as num, `lamp_name`as name, 'lamp' as problem_type
FROM (`sl_lamp`)
WHERE `problem_state_time` > lamp_state_setting;

这样就对了嘛～

The End~

Blog comments powered by Disqus