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

[eluser]vivid_haze[/eluser]
Hi, 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.

Right? 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://ellislab.com/codeigniter/user-gui...ecord.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'... Does anyone know how I could do this? I hope my question is intelligible!

I'd really appreciate some help, Thanks!

[eluser]Cristian Gilè[/eluser]
Your db is not well structured. Use three tables, one for posts, one for categories and for for association between posts and categories :

Code:

POST table:
-id
-name
-content

CATEGORY table:
-id
-name

POST_CATEGORY table:
-id
-post_id
-category_id

Cristian Gilè

[eluser]vivid_haze[/eluser]
Thanks for your reply Christian. Unfortunately the system I'm building has many levels of complexity, and to reconstruct the DB like that would cause countless other problems and probably take many weeks of work! To put my question in a much simpler way-

Is it possible to have a chain of LIKE clauses which all refer to the same column?

Thanks.

[eluser]Cristian Gilè[/eluser]
Yes, it's possible to have a chain of LIKE clauses which all refer to the same column.

Please, post your code.


Cristian Gilè

[eluser]vivid_haze[/eluser]
Thanks Cristian. Here is my Code. As you can see, it would be nice if every iteration of the foreach loop was able to produce a LIKE clause.

function get_all_titles($orderBy, $filter, $filterQ, $dir){
$this->db->select('id, title, date, categories, visible');
if($filter != 'none'){
switch ($filter){
case 'drafts':
$a='visible'; $b=0;
break;
case 'published':
$a='visible'; $b=1;
break;
}
$this->db->where($a,$b);
}
if($filterQ != 'none'){
$parts = explode(':',$filterQ);
switch($parts[0]){
case 'CATS':
$cats = explode(',',$parts[1]);
foreach ($cats as $cat){
$this->db->like('categories',$cat);
}
break;
case 'SEARCH':



break;
}
}
$this->db->order_by($orderBy, $dir);
$query = $this->db->get('entries');
$list = $query->result_array();
$table = $this->build_edit_table($list);
return $table;
}

[eluser]Cristian Gilè[/eluser]
Please, could you provide a sample for $filterQ string ?


Cristian Gilè

[eluser]vivid_haze[/eluser]
Cristian,

$filterQ = 'CATSolitics,Sociology,History';

(oops, I made a mistake above- the second explode should use a comma as the divider. Now corrected)

[eluser]Cristian Gilè[/eluser]
I don't see any error in the code you have posted.
Have you tried to pass the $filter parameter as none so the

Code:

$this->db->where($a,$b);

is not inserted?


Cristian Gilè

[eluser]vivid_haze[/eluser]
No, and anyway it should work either way.

Assuming $filterQ = 'CATSolitics,History';
Then this whole section of code-

if($filterQ != 'none'){
$parts = explode(':',$filterQ);
switch($parts[0]){
case 'CATS':
$cats = explode(',',$parts[1]);
foreach ($cats as $cat){
$this->db->like('categories',$cat);
}
break;
case 'SEARCH':



break;
}
}

...Should be equal to this

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

However, the above two lines works perfectly, but the 'if' statement does not. WHY????!!!

The result I am getting seems to be a result of ONLY the first like clause being produced, ie. I am getting all the posts with the category 'Politics', but not with both History and Politics- most of them are not listed under History at all- so the second clause is not being produced. Is this a bug in CI?

Thanks for responding anyway, Cristian, I appreciate it.

[eluser]Cristian Gilè[/eluser]

No, and anyway it should work either way.

Please, try to remove/disable the first if statement (where the "where clause" could be performed) or try to pass as $filter parameter the "none" value and check if it works.

I put this test function in a model

Code:

function test($filterQ)
{
    if($filterQ != "none")
    {
      $parts = explode(":",$filterQ);
      switch($parts[0])
      {
        case "CATS":
            $cats = explode(",",$parts[1]);
            foreach ($cats as $cat)
            {
              $this->db->like('categories',$cat);
            }
        break;
        case "SEARCH":
          break;
      }
      
      return $this->db->get('test')->result();
}

and from a controller i pass the following parameter:

Code:

$filterQ = "CATS:Science,Geo,History";

in my table test there are the following records:

Code:

1     History,Politics,Geo,Science
2     Geo,Science,History
3     Science
4     History

it returns the first two records as result so it works perfectly.

Could you provide some sample records from your db?


Cristian Gilè