phpmysqlcodeigniteractiverecordwhere-clause

WHERE clause condition including CONVERT_TZ() and DATE_ADD() does not render as intended when using CodeIgniter's where() method

I'm using Codeigniter3 and have a problem with an query

The default value of the variable day is 14. I want to convert sql query below to the codeigniter model below.

(CONVERT_TZ(CreateTime,'+0:00','+9:00') > DATE_ADD( CONVERT_TZ(NOW(),'+0:00','+9:00') , INTERVAL -".$day." DAY )

OR CONVERT_TZ(CreateTime,'+0:00','+9:00') > DATE_ADD( CONVERT_TZ(NOW(),'+0:00','+9:00') , INTERVAL -".$day." DAY ) )

This is the model I wrote and it gives error

$where["CONVERT_TZ(CreateTime,'+0:00','+9:00') > DATE_ADD( CONVERT_TZ(NOW(),'+0:00','+9:00'),"] = "INTERVAL -".$day." DAY";

foreach ($where as $key => $value) {
   $this->db->where($key,$value);
}

This renders as:

WHERE CONVERT_TZ(CreateTime,'+0:00','+9:00') > DATE_ADD( CONVERT_TZ(NOW(),'+0:00','+9:00'), 'INTERVAL -14 DAY'

and emits:

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 ''INTERVAL -14 DAY''

Solution

  • Try like this..

     $where = "(CONVERT_TZ(CreateTime,'+0:00','+9:00') > DATE_ADD( CONVERT_TZ(NOW(),'+0:00','+9:00') , INTERVAL -".$day." DAY )
           OR CONVERT_TZ(CreateTime,'+0:00','+9:00') > DATE_ADD( CONVERT_TZ(NOW(),'+0:00','+9:00') , INTERVAL -".$day." DAY ))";

  $this->db->where($where);

    For more see Codeigniter Query Builder