3

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.

Paul
  • 634
  • 3
  • 7
  • 18
  • PS this is a simplification of the code I off-course also have some security input checking in place. – Paul Feb 14 '15 at 13:02
  • 5
    I would recommend adding another column in your table called `slug`, which has a sanitized version of the title. Then you can just directly try and match that, rather than go to great lengths to convert a URL or other input into some kind of query – Djave Feb 14 '15 at 13:24
  • 1
    Seperate the game name (`tex-murphy-martian-memorandum`) and game title (`"Tex Murphy: Martian Memorandum"`). Have a function generate the gamename from game title (make lowercase, strip out characters, replace space with - etc). Use gamename in your urls and for searching, and gametitle for display. –  Feb 14 '15 at 13:24
  • Using slugs as mentioned here in comments, I suggest to combine it with text similarities/sound programming functions to make predictable searches http://stackoverflow.com/questions/5351659/algorithms-for-string-similarities-better-than-levenshtein-and-similar-text – Alejandro Quiroz Feb 14 '15 at 17:44

1 Answers1

-1

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.

John Max
  • 432
  • 8
  • 23
  • What I'm trying to do is find a solution in dealing with characters like : and ' . For certain inputs (see above) I now get no results back. In most cases there is 1 result for each query. – Paul Feb 14 '15 at 13:49