CODEWITHSUNDEEP
phpcodeigniter
user2009750
user2009750

Reputation: 3187

CodeIgniter model insert extra characters in my query

I am executing a query in CodeIgniter model here is my code:

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;
        }

while executing this query works fine on mysql console but with this active record patten of CodeIgniter some extra characters are introduced in date_format section. Here is error shown by CodeIgniter all extra characters are clearly visible in 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"

How do I tackle this issue any help will be appreciated. Thanks

Upvotes: 0

Views: 179

Answers (2)

Shaiful Islam
Shaiful Islam

Reputation: 7134

Just add false as 2nd parameter of select so that codeigniter do not convert the format

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() 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.

Upvotes: 1

Joerg
Joerg

Reputation: 3101

For removing the backticks, you have to add a second, optional parameter (FALSE) to your select part:

$this->db->select("postID, userID, . . .", FALSE);

Upvotes: 1

Related Questions