Codeigniter Database Mapping

Question

I had a problem comparing dates in the database with the current week using activerecord. I am trying to return a list of events with a start date of less than a week.

Event_start_date is in the format 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'); 

If you fail to develop the correct syntax for this, any help would be appreciated :-)

Answer 1

Two things. First, did you try to put the where clause in quotation marks as follows:

 $this->db->where('event_start_date <=','DATE_ADD(NOW(),INTERVAL 7 DAYS )'); 

Secondly, if necessary, simply skip using the where function and place the entire query as follows:

 $this->db->query('SELECT event_id,title,event_start_date,location FROM sd_events WHERE event_start_date <= DATE_ADD(NOW(),INTERVAL 7 DAYS )'); 

Answer 2

You can simply use the first argument to accept functions with → 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 the WHERE statement only asis. Otherwise, it will pack your "DATE_ADD" in single quotes that will not be evaluated.