Welcome Guest, Not a member yet? Register   Sign In
Need help with Active Record: using where_in and like and or_like at the same time does not work...
#1

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


Messages In This Thread
Need help with Active Record: using where_in and like and or_like at the same time does not work... - by El Forum - 10-13-2011, 06:47 AM



Theme © iAndrew 2016 - Forum software by © MyBB