+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived General Discussion (https://forum.codeigniter.com/forumdisplay.php?fid=21)
+--- Thread: Active Record - where_in AND or_like (/showthread.php?tid=31455)
Active Record - where_in AND or_like - El Forum - 06-20-2010
[eluser]packetfox[/eluser]
Hello all,
i am trying to build a search query that includes a where_in clause to enable me to restrict searches to certain categories denoted by category_id's.
This is what my current model that performs the query looks like:
Code:
function fetchByCategory($category_ids,$search_query){
$this->db->where_in('category_id',$category_ids);
$this->db->like('id',$search_query);
$this->db->or_like('item_code',$search_query);
$query=$this->db->get('items');
if($query->num_rows()>0){
return $query->result_array();
} else {
return FALSE;
}
}A Query run by the above model looks like this:
Code:
SELECT * FROM (`items`) WHERE `category_id` IN ('7', '43') AND `id` LIKE '%TK%' OR `item_code` LIKE '%TK%'First this all looks good, but the problem is that it will also return Items in this Case that have a item_code like %TK% even if it is not in Category 7 or 43. What id like is built a query that will return all Items whose ID or Item Code is like TK AND whose category_id is either 7 or 43.
I hope i describe my problem clear and someone can give me a suggestion on how to improve my Active Record Query.
Many thanks and best regards,
D
Active Record - where_in AND or_like - El Forum - 06-20-2010
[eluser]WanWizard[/eluser]
To alter the selection order of the where clause you need to use brackets:
Code:
SELECT * FROM (`items`) WHERE `category_id` IN ('7', '43') AND (`id` LIKE '%TK%' OR `item_code` LIKE '%TK%')Search the forum, I posted an DB_active_rec.php extension that introduced a method to add brackets to an active record query...