Login

10-13-2011, 06:47 AM

[eluser]Unknown[/eluser]
Need help with Active Record: using where_in and like and or_like at the same time does not work out...

Here is my code :

Code:
// Making sure categories are visible

 $visible_cats = $this->get_all_visible_cats();

 $cat_array = array();

 foreach($visible_cats as $cat)

 {

     $cat_array[] = $cat['id'];

 }

 $match = $this->input->post('search');

 $clean_match = $this->security->xss_clean($match);

 $this->db->like('video_name',$clean_match);

 $this->db->or_like('username',$clean_match);

 $this->db->or_like('description',$clean_match);

 $this->db->or_like('tags',$clean_match);

 $this->db->where_in('category', $cat_array);

 $query = $this->db->get('videos');

 $videos = $query->result_array();

and this is the get_all_visible_cats() method (which works very well in other instances where I'm using where_in but not like and or_like at the same time)

Code:
function get_all_visible_cats()

    {

        $this->db->select('id, category');

 $this->db->where('visible', 1);

        $query = $this->db->get('categories');

        $categories = $query->result_array();

        return $categories;

    }

Here is the SQL query that it generates:

Code:
SELECT * FROM (`videos`) WHERE `category` IN ('6', '7', '8', '9', '11', '12', '13') AND `video_name` LIKE '%test%' OR `username` LIKE '%test%' OR `description` LIKE '%test%' OR `tags` LIKE '%test%'

In another method that I have, I use it like this and it works perfectly:

Code:
// Making sure categories are visible

 $visible_cats = $this->get_all_visible_cats();

 $cat_array = array();

 foreach($visible_cats as $cat)

 {

     $cat_array[] = $cat['id'];

 }

 // db query (id needs to be last for array_pop to work at the end)

        $this->db->select('thumbnail, video_name, description, category, views, date, username, tags, id');

 $this->db->order_by("date", $sort);

 $this->db->where_in('category', $cat_array);

        $query = $this->db->get('videos', $num, $offset);

        $videos = $query->result_array();

Any help is appreciated!
Thank you.

EDIT: I forgot to mention what doesn't work. The like() and or_like() functions work well, I get the search results correctly. What doesn't work is the where_in.. It gives me all categories including the ones that are not even in the array (see SQL query up here)..

The last bit of code that I posted does not return these non-visible categories.. That is the goal.