[RESOLVED] Fulltext search question.

[cafrow]

I have a table that has a fulltext field called keywords. Keywords contains keywords for that product, so an example of the keywords might be:

Cows, grass, outdoors, cattle

Now I am using the Match / Against query in MySQL and I am having a problem. I want it so if people type in Cow that it will find the entry Cows. I have tried to add a wildcard '%' and that does not seem to work, maybe I am not doing it right. Here is where I assign the wildcard to the search terms

$search_terms = trim($_GET['search']);
$search_terms = '%' . $search_terms . '%';
$search_terms = str_replace(' ' , '% %', $search_terms); 

and here is my query

$sql = sprintf("SELECT COUNT(id) AS total FROM products WHERE MATCH (keywords) AGAINST(%s) ORDER BY name ASC",
    $action->smart($search_terms));
$sql = @mysql_query($sql); 

And here is the smart function from inside the Action class.

function smart($value)
{
   if (get_magic_quotes_gpc()) {
       $value = stripslashes($value);
   }
   if (!is_numeric($value)) {
       $value = "'" . mysql_real_escape_string($value) . "'";
   }
   
   return $value;
} 

The main reason for me using the Fulltext match / against search is so if you type in Cow Horse that you will get ALL matches of Cow AND Horse in one query, the results page is using record sets so i need to be able to set limits AND get the same results from the query each time so that when you go to page 2 you don't get the same results as page 1.

Thanks for any help.

[bokeh]

I

$search_terms = trim($_GET['search']);
$search_terms = '%' . $search_terms . '%';
$search_terms = str_replace(' ' , '% %', $search_terms); 

This is wrong. With a fulltext search just feed in a string of words. If you are interested I'll post the query from one of my site search engines.

[cafrow]

Thanks Bokeh, I had it that way before without adding all the wildcards (%) but I am still unable to get the results I am looking for. Heres another example of what I am trying to make work. Lets say I enter the word bull into the search, and each line below is the keyword field for a record.

1) bulls, baseball, grass
2) bull, soccer
3) bulldog, baseball, happy
4) dragonfly, bull, puppy

I would want all 4 records to show back up because each record contains the word bull, now if the search word was bulls then I want only record 1 to come back. That is why I tried to add wildcards but that did not seem to work.

[bokeh]

What you need to do is a boolean fulltext search and then use boolean fulltext search mode operators. You would then use bull* as the search terms.

[cafrow]

What you need to do is a boolean fulltext search and then use boolean fulltext search mode operators. You would then use bull* as the search terms.

Bokeh thank you so much. I checked it out and got it working, this is great, thank you.