Search word match accuracy, possibly using Levenshtein distance

Question

I have a mySQL table where people add their names and their interests. I want to use some kind of phrase that passes and finds either a 100% match or a close match. Ive heard of levenshtein distance, but have no idea how to do this loop through my desk.

$input = $_POST["interest"]; $result = mysql_query("SELECT interest_desc FROM interests"); 

Did some search engine and got to this point

  function closest($seed, $haystack){ $shortest = -1; foreach ($haystack as $word){ $lev = levenshtein($seed, $word); if ($lev == 0) { $closest = $word; $shortest = 0; break; } if ($lev <= $shortest || $shortest < 0) { $closest = $word; $shortest = $lev; } } return $closest; } $array = mysql_fetch_row($result); $closestmatch = closest($input,$array); echo $closetmatch; 

Answer 1

I think SOUNDEX is an alternative for you.

Lo Sauer article can help you with this.

http://www.lsauer.com/2013/05/mysql-fuzzy-searching-fulltext-queries.html

Answer 2

I think that using PHP for this is the wrong approach; MySQL can do this easily and efficiently. I'm not sure what your structure of the whole schema is, but you can just do PROCEDURE in MySQL with search parameters and just call it from PHP.

• Do something similar to this in MySQL:

  - Create a proc with the search parameter CREATION PROCEDURE sp_SearchInterests (IN p_SearchParam VARCHAR (30)); DELIMITER //

  SELECT interest_desc FROM interests WHERE interest_desc = p_SearchParam OR interest_desc LIKE '% pSearchParam%' //

  END; DELIMITER;

• From PHP, just CALL sp_SearchInterests('whateveryouwant') to return the desired results.