I'm producing a query like the following using ActiveRecord
SELECT * FROM (`foods`) WHERE `type` = 'fruits' AND
`tags` LIKE '%green%' OR `tags` LIKE '%blue%' OR `tags` LIKE '%red%'
The number of tags
and values is unknown. Arrays are created dynamically. Below I added a possible array.
$tags = array (
'0' => 'green'.
'1' => 'blue',
'2' => 'red'
);
Having an array of tags, I use the following loop to create the query I posted on top.
$this->db->where('type', $type); //var type is retrieved from input value
foreach($tags as $tag):
$this->db->or_like('tags', $tag);
endforeach;
The issue: I need to add parentheses around the LIKE
clauses like below:
SELECT * FROM (`foods`) WHERE `type` = 'fruits' AND
(`tags` LIKE '%green%' OR `tags` LIKE '%blue%' OR `tags` LIKE '%red%')
I know how to accomplish this if the content within the parentheses was static but the foreach loop throws me off..
From the CI wiki:
The codeignighter ActiveRecord feature allows you to create SQL queries relatively simply and database-independant, however there isno specific support for including parenthesis in an SQL query.
For example when you want a where statement to come out simmilarly to the folowing:
WHERE (field1 = value || field2 = value) AND (field3 = value2 || field4 = value2)
This can be worked around by feeding a string to the CI->db->where() function, in this case you will want to specifically escape your values.
See the following example:
$value=$this->db->escape($value);
$value2=$this->db->escape($value2);
$this->db->from('sometable');
$this->db->where("($field = $value || $field2 = $value)");
$this->db->where("($field3 = $value2 || $field4 = $value2)");
$this->db->get();
A simmilar workaround can be used for LIKE clauses:
$this->db->where("($field LIKE '%$value%' || $field2 LIKE '%$value%')");
$this->db->where("($field3 LIKE '%$value2%' || $field4 LIKE '%$value2%')");