Issue with Active Records in CodeIgniter

[Nethox]

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 :

<?php // 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)

<?php 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:

<?php 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:

<?php // 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.

 

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, even though '10' is not in the array, it returns rows with category 10...)

 

The last bit of code that I posted does not return these non-visible categories.. That is the goal but I have to use like and or_like for my search in this case...

[Nethox]

Ok I figured out what doesn't work and it seems to be an AR issue..

 

I redid the mysql syntax manually and adding brackets where required.. AR does not seem to do that automatically for some reason. Here is the appropriate SQL for those looking for an answer to this problem.

 

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%')