Codeigniter parentheses in Active Record query

As @M Khalid Junaid has pointed out, Codeigniter's active record library doesn't support grouped where clauses. You can create a workaround by using "where" while preventing Codeigniter from automatically escaping the query:

$escaped_sapfvalue = $this->db->escape( $sapfvalue );$this->purchase_requisition_model->where('deleted','1')->where("( `to` LIKE '%{$escaped_sapfvalue}%'", null, FALSE)->where("OR `date` LIKE '%{$escaped_sapfvalue}%'", null, FALSE)->where("OR `request_by` LIKE '%{$escaped_sapfvalue}%'", null, FALSE)->where("OR `deliver_to` LIKE '%{$escaped_sapfvalue}%'", null, FALSE)->where("OR `name` LIKE '%{$escaped_sapfvalue}%'", null, FALSE)->where("OR `telephone` LIKE '%{$escaped_sapfvalue}%'", null, FALSE)->where("OR `designation` LIKE '%{$escaped_sapfvalue}%'", null, FALSE)->where("OR `budget_status` LIKE '%{$escaped_sapfvalue}%' )", null, FALSE)->find_all();

Notice than how I manually escaped the variable to prevent SQL injection. Also notice how the first and last "LIKE" include the opening and closing parenthesis, respectively.

While this works, it has a lot of repeated code. Looping through an array would be more elegant:

$escaped_sapfvalue = $this->db->escape( $sapfvalue );$or_like = '';foreach( $column_list as $column ) {    // If it's not the first column, add 'OR'    if ( strlen($or_like) > 0 ) {        $or_like .= ' OR ';    }    // Concatenate manually escaped columns and rows    $escaped_column = $this->db->escape( $column );    $or_like .= "`{$escaped_column}` LIKE '%{$escaped_sapfvalue}%'";}// Add grouping parenthesis$grouped_or_like = "( {$or_like} )";// Build the query$this->purchase_requisition_model->where('deleted','1')->where( $grouped_or_like, null, false )->find_all();

EDIT: And while I haven't tested it, I've just thought this should work too:

$escaped_sapfvalue = $this->db->escape( $sapfvalue );$this->purchase_requisition_model->where('deleted','1')->where("( `to` LIKE '%{$escaped_sapfvalue}%'", null, FALSE)->or_like('date',$sapfvalue,'both')->or_like('request_by',$sapfvalue,'both')->or_like('deliver_to',$sapfvalue,'both')->or_like('name',$sapfvalue,'both')->or_like('telephone',$sapfvalue,'both')->or_like('designation',$sapfvalue,'both')->where("OR `budget_status` LIKE '%{$escaped_sapfvalue}%' )", null, FALSE)->find_all();

Choose whatever works best for you.

You can use query grouping too.

Code will look like this:

$this->db->where('deleted', 1);$this->db->group_start();$this->db->or_like('to', $sapfvalue, 'both')$this->db->or_like('date', $sapfvalue, 'both')$this->db->or_like('request_by', $sapfvalue,  'both')$this->db->or_like('deliver_to', $sapfvalue, 'both')$this->db->or_like('name', $sapfvalue, 'both')$this->db->or_like('telephone', $sapfvalue, 'both')$this->db->or_like('designation', $sapfvalue, 'both')$this->db->or_like('budget_status', $sapfvalue, 'both')$this->db->group_end();$q = $this->db->get('table')->result();

Using group_start and group_end assures that this part of or_like statements between then will get into brackets.