Mulitple LIKE db query using associative array- but all from the same column name...?

Question

I'm trying to query my database using CodeIgniter's active record class. I have a number of blog posts stored in a table. The query is for a search function, which will pull out all the posts that have certain categories assigned to them. So the 'category' column of the table will have a list of all the categories for that post in no particular order, separated by commas, like so: Politics, History, Sociology. etc.

If a user selects, say, Politics, and History, The titles of all the posts that have BOTH these categories should be returned.

So, the list of categories queried will be the array $cats. I thought this would work-

foreach ($cats as $cat){
    $this->db->like('categories',$cat);
}

By Producing this:

$this->db->like ('categories','Politics');
$this->db->like ('categories','History');

(Which would produce- 'WHERE categories LIKE '%Politics%' AND categories LIKE '%History%')

But it doesn't work, it seems to only produce the first statement. The problem I guess is that the column name is the same for each of the chained queries. There doesn't seem to be anything in the CI user guide about this (http://codeigniter.com/user_guide/database/active_record.html) as they seem to assume that each chained statement is going to be for a different column name.

Of course it is not possible to use an associative array in one statement as it would have to contain duplicate keys- in this case every key would have to be 'categories'...

Answer 1

With regard to MySQL, I just ran the following query on my database and there were no issues.

 SELECT * 
 FROM  `TicketUpdates` 
 WHERE content LIKE  '%update%'
 AND content LIKE  '%footprints%';

Likewise the following code ran as expected:

 $this->db->select('*'); 
 $this->db->from('TicketUpdates');
 $this->db->like('content', 'update');   
 $this->db->like('content', 'footprints');       
 print_r($this->db->get()->result());

If you're using the 'like' function in CI, you have to prefix and postfix them with the proper query functions, example:

 $this->db->select('*');
 $this->db->from('tablename');
 foreach($cats as $cat){
    $this->db->like('categories', $cat);
 }
 $result = $this->db->get();