ci 数据库操作问题

zlb401

PHP复制代码

  $this->db->select('risks.*, s.option_name risk_status, p.option_name risk_potential, i.option_name risk_impact, t.name project_name,CONCAT_WS(",", c.last_name, c.first_name)  risk_assignedto');
        $this->db->from('risks');
        $this->db->join('options as s', 'risks.status = s.option_id and s.field = "risk_status"', 'left');
        $this->db->join('options as p', 'risks.potential = p.option_id and p.field = "risk_potential"', 'left');
        $this->db->join('options as i', 'risks.impact = i.option_id and i.field = "risk_impact"', 'left');
        $this->db->join('contacts as c', 'risks.assignedto = c.id', 'left');
        $this->db->join('projects as t', 'risks.project_id = t.id', 'left');
        $query = $this->db->get();

复制代码

出现错误：ERROR - 2010-10-13 16:49:04 --> Query error: 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 '`, `c`.`last_name`, `c`.`first_name)` risk_assignedto
F' at line 1

怎么解决啊，急啊。。。。

qi_ruo

SELECT `risks`.*, `s`.`option_name` risk_status, `p`.`option_name` risk_potential, `i`.`option_name` risk_impact, `t`.`name` project_name, CONCAT_WS(", `"`, `c`.`last_name`, `c`.`first_name)` risk_assignedto FROM (`risks`) LEFT JOIN `options` as s ON `risks`.`status` = `s`.`option_id` and s.field = "risk_status" LEFT JOIN `options` as p ON `risks`.`potential` = `p`.`option_id` and p.field = "risk_potential" LEFT JOIN `options` as i ON `risks`.`impact` = `i`.`option_id` and i.field = "risk_impact" LEFT JOIN `contacts` as c ON `risks`.`assignedto` = `c`.`id` LEFT JOIN `projects` as t ON `risks`.`project_id` = `t`.`id`

CONCAT_WS(", `"`, `c`.`last_name`, `c`.`first_name)`这边出了点小问题  
着急的话先加个false参数去掉反引号吧
不怕麻烦的话 自己手动加反引号也行  大家看看有什么方法

$this->db->select('risks.*, s.option_name risk_status, p.option_name risk_potential, i.option_name risk_impact, t.name project_name,CONCAT_WS(",", c.last_name, c.first_name ) risk_assignedto',false);

qi_ruo

如果能改那个 , 分割符 把 , 改成 . 或者 _ 其他都行 然后c.first_name还要后面加个空格

CONCAT_WS(".", c.last_name, c.first_name )   最好还是自己手动加反引号吧

$this->db->select() 可接受一个可选的第二个参数。如果你把它设为FALSE， CodeIgniter 将不会使用反引号保护你的字段或者表名。这在进行复合查询时很有用。

visvoy

藤椅正解