Reputation: 97
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
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.
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 ;
From PHP just CALL sp_SearchInterests('whateveryouwant')
to return the desired results.
Upvotes: 0
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