CODEWITHSUNDEEP
phpmysqlcpu-wordmatchinglevenshtein-distance
user1064660
user1064660

Reputation: 97

Match search word accuracy possibly using the Levenshtein distance

i have a mySQL table where people add their names and their interests. I want to use some sort of word match that goes through and finds either a 100% match or a close match. Ive heard of the levenshtein distance but have no clue how to make it cycle through my table.

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

Done some googling 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;

Upvotes: 6

Views: 1048

Answers (2)

Phrancis
Phrancis

Reputation: 2280

I think using PHP to do this is the incorrect approach, MySQL can easily and efficiently do this. I'm not sure what your whole schema structure is like, but you could just make a PROCEDURE in MySQL with the search parameters and just call it from PHP.

  1. Make something similar to this in MySQL:

    -- Create proc with search parameter CREATE 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 ;

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

Upvotes: 0

Mestre San
Mestre San

Reputation: 1925

I think SOUNDEX is an alternative for you.

The Lo Sauer's article can help you with that

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

Upvotes: 1

Related Questions