可以处理缺少字符的MySQL查询

I'm trying to improve my MySQL query.

    SELECT gamename
    FROM giveaway
    WHERE gamename LIKE '$query'

I got an input that consists of URL's that are formed like:

I take the game name from the URL and use this as input for a SQL query.

  • $query = "plain sight"
  • $query = "tex murphy martian memorandum"
  • $query = "flyn"
  • $query = "penguins arena sednas world"

Now in the database the matching name sometimes has more characters like : ' !, etc.

Example:

  • "Plain Sight"
  • "Tex Murphy: Martian Memorandum"
  • "Fly'N"
  • "Penguins Arena: Sedna's World!"

So when putting in the acquired name from the URL this doesn't produce results for the 2nd, 3rd and 4th example. So what I did was use a % character.

  • $query = "plain%sight"
  • $query = "tex%murphy%martian%memorandum"
  • $query = "flyn"
  • $query = "penguins%arena%sednas%world"

This now gives result on the 1st and 2nd example.

.

On to my question:

My question is, how to better improve this so that also the 3rd and 4th ones work?

I'm thinking about adding extra % before and after each character:

  • $query = "%f%l%y%n%"
  • $query = "%p%e%n%g%u%i%n%s%a%r%e%n%a%s%e%d%n%a%s%w%o%r%l%d%"

But I'm not sure how that would go performance wise and if this is the best solution for it.

Is adding % a good solution?

Any other tips on how to make a good working query?

Progress:

After a bit of testing I found that adding lots of wildcards (%) is not a good idea. You will get returned unexpected results from the database, simply because you just added a lot of ways things could match.

Using the slug method seems to be the only option.

If i get your question well, you are creating a way of searching through those informations. And if that is the case then try

$query = addslashes($query);


SELECT name
    FROM giveaway
    WHERE gamename LIKE '%$query%'

Now if you want to enlarge your search and search for every single word that looks like the words in your string, then you can explode the text and search for each word by doing

<?php
$query = addslashes($query);

//We explode the query into a table
$tableau=explode(' ',$query);

$compter_tableau=count($tableau);

//We prepare the query
$req_search = "SELECT name FROM giveaway WHERE  ";

//we add the percentage sign and the combine each query 
for ($i = 0; $i < $compter_tableau; $i++) 
{
$notremotchercher=$tableau["$i"];

if($i==$compter_tableau) { $liaison="AND"; } else { $liaison=""; }

if($i!=0) { $debutliaison="AND"; } else { $debutliaison=""; }

$req_search .= "$debutliaison gamename  LIKE '%$notremotchercher%' $liaison ";
}

//Now you lauch your query here
$selection=mysqli_query($link, "$req_search") or die(mysqli_error($link)); 

?>

By so doing you would have added the % to every word in your query which will give you more result that you can choose from.