Welcome Guest, Not a member yet? Register   Sign In
Active Record - where_in AND or_like
#1

[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
#2

[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...




Theme © iAndrew 2016 - Forum software by © MyBB