带有后缀的mysql搜索关键字，存储在数据库中的csv字符串中

I have in mysql database table column keywords there are csv keywords like "hotel, new hotel, good hotel".

Now when user enter hotel it works(select data) but not for hotels(it shouldn't). Now I want user enter hotels then it should also match hotel keyword.

In-short with suffix search should work. currently i implemented following.

$queried = trim(mysqli_real_escape_string($con,$_POST['query']));

$keys = explode(" ",$queried);


$sql = 'SELECT name FROM image WHERE keyword LIKE "%$queried%"';

foreach($keys as $k){
 $k= trim(mysqli_real_escape_string($con,$k));
  if(count($keys) > 1)
  {
     $sql .= ' OR keyword LIKE "%$k%" ';
  }
}

i did this way it's not what i want but it works for my criteria.

$suffix = array('','s','es','ing','ment'); // suffix you want to ad 

$sql = 'SELECT name FROM image WHERE keyword LIKE "%$queried%"';

foreach($keys as $k)
    {
      $k= trim(mysqli_real_escape_string($con,$k));

        for ($i=1; $i < sizeof($suf) ; $i++)
        {   
           if(substr($k, (-1 * strlen($suf[$i])))==$suf[$i])
           {
               $wp=substr( $k, 0, (-1 * $i));
           }    
       }
        if($wp!="")
        {
                    $sql .= " OR keyword LIKE '%$k%' OR keyword LIKE '%$wp%' ";          
        }
    }

you'd have to (additionally) ask whether the search term contains any of the words in the rows. Currently you're doing the opposite (which is fine for the opposite situation, so don't get rid of it)

Something like:

$sql = 'SELECT name FROM image WHERE $queried LIKE "%" + keyword + "%"';

(Apologies if MySQL syntax isn't quite right, not used it for a while).

It might occasionally throw up unwanted things though, e.g. if the user wrote "aparthotel" it'd still return "hotel", you may or may not want that. Or it could even something entirely irrelevant depending on the words involved.

Once you get onto anything more complex than that though, you're probably into the realms of search engines and natural language processing.