CODEWITHSUNDEEP
phpmysqlcodeigniter
Sam Pearson
Sam Pearson

Reputation: 127

CodeIgniter Query Escaping Quotes

I have the following simple query which works perfectly in MySQL;

select * from client_contact
where date_format(client_next_contact_on, '%Y-%m-%d') = '2018-07-25'

I've then added this in my codeigniter query but the error I get is;

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 ''2018-07-25'' at line 3

SELECT * FROM (client_contact) WHERE dateformat(client_next_contact_on, '%Y-%m-%d') '2018-07-25'

By the looks of the above, it's missing the = in the query.

Here's my query in codeigniter;

$today = new DateTime();
$today_formatted = $today->format('Y-m-d');
$this->db->where('dateformat(client_next_contact_on, \'%Y-%m-%d\')', $today_formatted);
$return = $this->db->get('client_contact')->row_array();

If you're wondering why I need to use date_format, it's because it's stored as a date time in my database for other purposes. For this purpose, I need a list of clients that I need to contact today, regardless of the time.

Upvotes: 0

Views: 161

Answers (1)

Blue
Blue

Reputation: 22911

Just include the equals in the where statement, and rename dateformat as it isn't a function in MySQL. Use DATE_FORMAT:

$this->db->where('DATE_FORMAT(client_next_contact_on, \'%Y-%m-%d\') =', $today_formatted);

Now, onto the guts of why code igniter chose not to include an equals sign, which (by the documentation, it should have). On this line of the codeigniter source, it calls this function to determine if it contains an operator. Apparently because you put a space after the , it believed you included an operator, so a simple manual adjustment to add this in is fine.

protected function _has_operator($str)
{
    return (bool) preg_match('/(<|>|!|=|\sIS NULL|\sIS NOT NULL|\sEXISTS|\sBETWEEN|\sLIKE|\sIN\s*\(|\s)/i', trim($str));
}

You can see where the regex matched here: https://regex101.com/r/BTuZxj/1

Upvotes: 3

Related Questions