6

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;
user1064660
  • 97
  • 1
  • 9
  • Are you sure you definitely want to do this in PHP? If you want to search for matches (or close matches) this is something that's correctly handled on the DB end. Please let us know. –  Dec 06 '11 at 02:03
  • as @rdlowery says, you're probably better off passing the responsibility to MySQL - have a read of http://stackoverflow.com/questions/634995/implementation-of-levenshtein-distance-for-mysql-fuzzy-search and http://www.artfulsoftware.com/infotree/queries.php#552 for a solution... – HorusKol Dec 06 '11 at 02:14

2 Answers2

1

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

Mestre San
  • 1,806
  • 15
  • 24
  • This should be the accepted answer, as it will perform better because MySQL will already narrow the data down, and possibly index it extremely well. You could also store the SOUNDEX value in a separate column – twicejr Aug 05 '14 at 14:54
0

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.

Phrancis
  • 2,222
  • 2
  • 27
  • 40
  • Easily and efficiently? Normally that opposes "using stored procedures". Because they are.. just bad in MySQL. Also, in 90% cases, if the intention is to use stored code - then it's time to think if that code should be moved to _application_. Finally, code above has nothing to do with Levenshtein distance – Alma Do Jun 02 '14 at 06:47
  • Fair point Re: Levenshtein distance. Though above proc will run super fast if that's all he's trying to do, I think Levenshtein may be overkill. – Phrancis Jun 02 '14 at 07:22