我正在CodeIgniter模型中执行查询,这是我的代码:
if($query = $this->db->select('postID, users.userID, users.userFirstName, users.userLastName, postTitle, postDescription, postHtmlContent, date_format(creationDate, "%d %b, %Y") as creationDate, date_format(updationDate, "%d %b, %Y") as updationDate, cover, postType')->from('posts, users')->where('users.userID = posts.userID and postID = 1 and postStatus = "APPROVED"')->get()){
return $query->result()[0];
}else{
return NULL;
}
虽然在 MySQL 控制台上执行此查询工作正常,但对于 CodeIgniter 的此活动记录模式,date_format
部分中引入了一些额外的字符。这是CodeIgniter显示的错误,所有多余的字符在date_format(creationDate,
"%d %b,
%Y")part
中清晰可见。
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 'FROM (`posts`, `users`) WHERE `users`.`userID` = posts.userID and postID = 1 and' at line 2
SELECT `postID`, `users`.`userID`, `users`.`userFirstName`, `users`.`userLastName`, `postTitle`, `postDescription`, `postHtmlContent`, date_format(creationDate, `"%d` %b, `%Y")` as creationDate, date_format(updationDate, `"%d` %b, `%Y")` as updationDate, `cover`, `postType` FROM (`posts`, `users`) WHERE `users`.`userID` = posts.userID and postID = 1 and postStatus = "APPROVED"
我如何解决这个问题,任何帮助将不胜感激。谢谢
只需添加false
作为选择的第二个参数,这样代码点火器就不会转换格式
if($query = $this->db->select('postID, users.userID, users.userFirstName, users.userLastName, postTitle, postDescription, postHtmlContent, date_format(creationDate, "%d %b, %Y") as creationDate, date_format(updationDate, "%d %b, %Y") as updationDate, cover, postType',false)->from('posts, users')->where('users.userID = posts.userID and postID = 1 and postStatus = "APPROVED"')->get()){
return $query->result()[0];
}else{
return NULL;
}
$this->db->select() 接受可选的第二个参数。如果将其设置为 FALSE,CodeIgniter 将不会尝试使用反引号保护您的字段或表名称。如果需要复合选择语句,这将非常有用。
要删除反引号,您必须向所选部分添加第二个可选参数 (FALSE):
$this->db->select("postID, userID, . . .", FALSE);