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

Question

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%" ';
  }
}

Answer 1

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%' ";          
        }
    }