2

I have a search function on a page that when the user types in a search term when submit is pressed it checks the database and shows appropriate records in the database (it uses a %Like% query) on the page.

However the user may spell something wrong. Is there anyway it can still search the database and show some results. I.e. Colour is stored in the database however the user searches color - is there any way we can change the WHERE clause to make sure that Colour is pulled through?

Code is below:

//// Contains the search term from the form  
$searched_club = $_GET['username'];(form is a PHP_SELF)

if($searched_club == ''){   
 $sql = "SELECT name, region FROM jos_users ORDER BY id DESC LIMIT 6";
}else{
 $sql = "SELECT name, region FROM jos_users WHERE name LIKE '%$searched_club%'";
}


$rsd = mysql_query($sql, $dbconn);
<table>
 while($rs = mysql_fetch_array($rsd)) {
   $name = $rs['name'];     
   $region = $rs['region'];     

 ?>     
            <tr>
                <td><?php 
                echo $name; 
                ?></td>
                <td><?php 
                echo $region; 
                ?></td>
            </tr>
            <?php
 } 
 ?>             
</table>
Marcus Adams
  • 53,009
  • 9
  • 91
  • 143
Mikey Barker
  • 83
  • 1
  • 9
  • Do some research on Levenshtein distance. – Gordon Linoff Jul 09 '14 at 12:30
  • possible duplicate of [Suggesting other queries for archaic spellings (eg Google's Did You Mean)](http://stackoverflow.com/questions/7099816/suggesting-other-queries-for-archaic-spellings-eg-googles-did-you-mean) – Marcus Adams Jul 09 '14 at 13:31

1 Answers1

3

You can use the string function soundex() to get a string representing a kind of audible pattern for a supplied string. If two strings are different but would sound similar, they should produce identical soundex() results. Strings like:

  • answer
  • anser

In a SELECT query, you can use the shortcut SOUNDS LIKE to compare the soundex() of two strings:

SELECT * FROM SomeTable WHERE SomeField SOUNDS LIKE SomeInputValue
David
  • 208,112
  • 36
  • 198
  • 279
  • Ok so currently I have the following: $sql = "SELECT name, region FROM jos_users WHERE name SOUNDS LIKE '%$searched_club%'"; however nothing is showing up? – Mikey Barker Jul 09 '14 at 13:17
  • @MikeyBarker: Do a manual query for the `SOUNDEX()` of each of your values and see if they match. It's not a perfect solution, there's still the possibility for error if the `SOUNDEX()` is different. For even more robustness, there's a *lot* of research you can put into string comparisons. For example, a comment on the question suggested "Levenshtein distance" which is a specific algorithm for determining the similarity of two strings. With something like that, you'd still have to set a threshold of what is "similar enough" to be a match. – David Jul 09 '14 at 13:22