Jump to content

Issue with Active Records in CodeIgniter


Nethox

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.