I'm having a problem comparing dates in a database to a week from now using activerecord. I'm trying to return a list of events with their start date less than a week from now.
The event_start_date is in the format of 2011-06-30 09:00:00
$this->db->select('event_id,title,event_start_date,location'); $this->db->where('event_start_date <=',DATE_ADD(NOW(),INTERVAL 7 DAYS )); $query = $this->db->get('sd_events'); Two things. First, have you tried putting your where clause in quotes like this:
$this->db->where('event_start_date <=','DATE_ADD(NOW(),INTERVAL 7 DAYS )'); Second, if necessary just skip using the where function and put the entire query in like this:
$this->db->query('SELECT event_id,title,event_start_date,location FROM sd_events WHERE event_start_date <= DATE_ADD(NOW(),INTERVAL 7 DAYS )'); You can just use the first argument to do accept functions with the ->where.
$this->db->select('event_id,title,event_start_date,location'); $this->db->where('event_start_date <= DATE_ADD(NOW(),INTERVAL 7 DAYS)', null); $query = $this->db->get('sd_events'); This should put it in your WHERE statement just asis. Otherwise, it'll wrap your "DATE_ADD" in single quotes which doesn't evaluate.
Your coding attempt is flawed syntactically because the where() method might be expecting a PHP userland function called DATE_ADD(), but even if that existed, the parameters wouldn't be valid (NOW(),INTERVAL 7 DAYS).
Fortunately, your coding intention is clear. If you had wrapped the second parameter of the where() call in quotes (either single or double), then the PHP would be valid, but the built query would be malformed. This is because CodeIgniter always wraps string-type values supplied as the second parameter in single quote. Your query must not add any quotes to the right side of the comparison operator.
Here are valid versions of correct syntax:
Explicitly disable all automatic quoting by setting the third parameter as false:
$this->db->where('event_start_date <=', 'DATE_ADD(NOW(),INTERVAL 7 DAYS)', false); Implicitly disable all automatic quoting by writing the whole comparison expression as the first parameter. This only works as needed because the first parameter is to be treated as an identifier value AND the string includes a (, ), or '. Consider this technique to be "quote disabling by context".
$this->db->where('event_start_date <= DATE_ADD(NOW(),INTERVAL 7 DAYS)'); If your query requires quoting, then apply escape_identifiers() (for tables, columns, and aliases) or escape() (for values).
$this->db->where( $this->db->escape_identifiers('event_start_date') . ' <= DATE_ADD(NOW(),INTERVAL 7 DAYS)' ); Context-disabled quoting can be enjoyed with get_where() too.
return $this->db ->select('event_id,title,event_start_date,location') ->get_where( 'sd_events', 'event_start_date <= DATE_ADD(NOW(),INTERVAL 7 DAYS)' ) ->result();