need help with mysql query Keyword search

[BlackRaven]

yeah hi, ok here is my problem (having been trying to figure this out for 3 hours now) i want to do a key word search, how ever my sql query only returns words that have the word within them:
Example i search for food
what i get is "food and drink"
what i am missing is
"toad food and stuff" or "myfood"
the problem is the %food% operator that i am using only returns words that have a food characters inbetween them ignoring words that start or end with food.

this is what i am using:

if ( isset($search) && $searchfield!= "" )
{
$where_query .= empty($where_query)? " WHERE " : " AND ";
$where_query .= $searchfield." LIKE '%".$search."%'";
}

$query = "SELECT * FROM $table " . $where_query ." ORDER BY ". $orderby;

[volatilegx]

maybe try:

$where_query .= $searchfield." REGEXP '".$search."'";

[coopster]

I can't see why your query wouldn't work as is, unless you need to trim the leading and trailing blanks from your $search variable, perhaps?

[BlackRaven]

yes i did i used trim? would that cause a problem?

[BlackRaven]

after spending 6hours yester i ccould believe that a trim($search) could be such a PAIN! ALL newbies be aware.!

[BlackRaven]

thanks for pointing me into the right direction coopster

[coopster]

You're welcome, but what exactly did you get stuck on? How were you using the trim() [php.net] function that made it difficult for you?