Codeigniter active records: like and where in SQL statement

Question

I need to select several rows that respect these conditions:

a) date >= today b) keyword present in fields 1, 2 or 3

i guess the SQL statement for this should be: SELECT * FROM events WHERE date >= '2010-09-12' AND (field1 LIKE '%keyword%' OR field2 LIKE '%keyword%' OR field3 LIKE '%keyword%')

I am trying to write this using codeigniter's active records, but the the LIKE condition seems to override the date one.

    $this->db->select('*');
  $this->db->join('venues', 'events.venue = venue_id');

    //first condition: date >= today
    $this->db->where('date >=', date('Y-m-d'));

  if ($keyword)
//if keyword is present, add this condition as well
  {
   $this->db->like('events.description', $keyword);
   $this->db->or_like('band', $keyword);
   $this->db->or_like('venues.venue', $keyword);

  }

  $this->db->order_by('date', 'ASC');
  $this->db->order_by('events.priority', 'DESC'); 
  $Q = $this->db->get('events');

I probably need to insert the LIKE statement inside a parentesis, but don't know how to do it. any suggestions?

thanks! Patrick

Answer 1

I think you have to write the 'like'-part of your query this way:

$this->db->select('*');
$this->db->join('venues', 'events.venue = venue_id');

if ($keyword)
//if keyword is present, add this condition as well
{
   $where = "( events.description LIKE '$keyword' OR band LIKE '$keyword' OR venues.venue LIKE '$keyword')";
   $this->db->where($where);

}
//first condition: date >= today
$this->db->where('date >=', date('Y-m-d'));

$this->db->order_by('date', 'ASC');
$this->db->order_by('events.priority', 'DESC');