php - 在 mysql 中向逗号添加不需要的字符 - codeigniter

Question

这是从 mysql 获取数据的 codeigniter Controller 语句

$result = $this->db->select("GROUP_CONCAT(Service_name SEPARATOR ', ' ) AS service_names")
                           ->where('Service_boid', $boId)
                           ->get('service');

这里的问题是，mysql 中的语法错误，因为 while 回显了查询:

SELECT GROUP_CONCAT(Service_name SEPARATOR ', `'` ) AS service_names FROM (`service_info`) WHERE `Service_boid` = '4'

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AS service_names FROM (service_info) WHEREService_boid` = '4' at line 1

如上所示，一个不需要的字符附加到分隔符。除了直接执行查询(sql 查询通过 mysqli_query() 执行)之外，还有什么解决方案可以解决这个问题？

注意:我想用 ', ' 作为分隔符。

Answer 1

从代码中删除空格:

"GROUP_CONCAT(Service_name SEPARATOR ', ' ) AS service_names")
                                       ^

像这样使用:

"GROUP_CONCAT(Service_name SEPARATOR ',' ) AS service_names")

If You want to use ', ' as the delimiter

然后使用false作为第二个参数。

$this->db->select("GROUP_CONCAT(Service_name SEPARATOR ', ' ) AS service_names", false) 

$this->db->select() accepts an optional second parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names with backticks. This is useful if you need a compound select statement.