How to write a SQL function within CodeIgniter's where() method [duplicate]

Question

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.

Virtually a duplicate of How to write a SELECT clause containing an SQL function call with CodeIgniter's select() method — mickmackusa
– mickmackusa ♦, Commented Jun 9 at 4:24

Blue · Accepted Answer · 2018-07-25 10:50:53Z

3

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

edited Jul 25, 2018 at 10:50

answered Jul 25, 2018 at 10:32

Blue

23k7 gold badges69 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to write a SQL function within CodeIgniter's where() method [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related