LIKE and 'text' type fields

[eluser]Aaron L.[/eluser]
Hello,

I am working on writing a search function and have run into a road block. I have a statement like this:

Code:

if($_POST['keywords']){
  $this->db->where('about LIKE',$_POST['keywords']);    
}

where the 'about' field is a field which contains information about a given user, and thus can get quite long.

In this case, a LIKE statement doesn't seem to come up with anything. I would like for it to pull-up records which have a word or words in the keywords contained in the about section. I know I am missing something obvious... can you help me out? Thanks in advance if you can!

- Aaron

[eluser]Colin Williams[/eluser]
I don't know if that method meshes with CI's active record. Try, $this->db->like(field, keyword); And I believe later versions of MySQL have search functions and RegEx searches, which would be much better than a simple LIKE comparison

[eluser]Aaron L.[/eluser]
Hi Colin,

Thanks for the reply! I've tried $this->db->like() and I have the same problem. What version of mysql has RegEx?

[eluser]Colin Williams[/eluser]
Hit up MySQL docs online or google it. You might also switch over to $this->input->post('keyword') instead of directly accessing $_POST. Might not fix your problem but is more compatible with XSS filtering options in CI

[eluser]CI Auke[/eluser]
Maby the "IN" operator is somthing worth trying...

Code:

if($_POST['keywords']){
  $arKeywords = explode (" ", $this->input->post('keywords'));
  $sql = "WHERE about IN $arKeywords";  
}

You will have to lose CI’s active record though...

[eluser]Neovive[/eluser]
When using your 'LIKE' statement, try inserting the '%' wildcard (e.g. '&#xke;yword%') around the keyword to see if that returns any matches. Also, try turning on the CI Profiler to view the generated query and then manually running the query from your database tool to see what is going on.

As Colin mentioned, you may also want to look into MySQL text searching abilities. I haven't tried it myself, but it may work in your situation.