php - "Did you mean"php 和 mysql 的功能

Question

我一直在创建一个网站的搜索功能，我想添加一个类型更正的功能。当我添加这些代码时，输​​出总是“words”数组的第一个，请帮忙!

这是我的代码:

<?php
$input = $q;

// array of words to check against
$sql = "SELECT `English` FROM `dict`";
$result = mysql_query($sql);
$words = mysql_fetch_array($result, MYSQL_BOTH);

$shortest=100;
// loop through words to find the closest
foreach ($words as $word) {

    // calculate the distance between the input word and the current word
    $lev = levenshtein($input, $word);
    //if the distance is shorter than the last shortest one, replace it.
    if ($lev <= $shortest) {
        // set the closest match, and shortest distance
        $closest  = $word;
        $shortest = $lev;
    }
}

echo "Input word: ".$input."<br />";
echo "Did you mean: ".$closest."?<br />";
?>

Answer 1

在 MySQL 中添加 levenshtein 函数。

DELIMITER $$
CREATE FUNCTION levenshtein( s1 VARCHAR(255), s2 VARCHAR(255) )
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE s1_len, s2_len, i, j, c, c_temp, cost INT;
DECLARE s1_char CHAR;
-- max strlen=255
DECLARE cv0, cv1 VARBINARY(256);
SET s1_len = CHAR_LENGTH(s1), s2_len = CHAR_LENGTH(s2), cv1 = 0x00, j = 1, i = 1, c = 0;
IF s1 = s2 THEN
RETURN 0;
ELSEIF s1_len = 0 THEN
RETURN s2_len;
ELSEIF s2_len = 0 THEN
RETURN s1_len;
ELSE
WHILE j <= s2_len DO
SET cv1 = CONCAT(cv1, UNHEX(HEX(j))), j = j + 1;
END WHILE;
WHILE i <= s1_len DO
SET s1_char = SUBSTRING(s1, i, 1), c = i, cv0 = UNHEX(HEX(i)), j = 1;
WHILE j <= s2_len DO
SET c = c + 1;
IF s1_char = SUBSTRING(s2, j, 1) THEN
SET cost = 0; ELSE SET cost = 1;
END IF;
SET c_temp = CONV(HEX(SUBSTRING(cv1, j, 1)), 16, 10) + cost;
IF c > c_temp THEN SET c = c_temp; END IF;
SET c_temp = CONV(HEX(SUBSTRING(cv1, j+1, 1)), 16, 10) + 1;
IF c > c_temp THEN
SET c = c_temp;
END IF;
SET cv0 = CONCAT(cv0, UNHEX(HEX(c))), j = j + 1;
END WHILE;
SET cv1 = cv0, i = i + 1;
END WHILE;
END IF;
RETURN c;
END$$
DELIMITER ;

注意:- 这个答案来自这个堆栈溢出问题的答案 How to add levenshtein function in mysql?

提示:-您应该在执行普通 SQL 命令时在 MySQL 控制台或 PHPMyAdmin 中运行此 SQL 脚本/代码。

现在你可以像这样使用这个函数了

SELECT levenshtein('abcde', 'abced')

Returns : 2

根据你的问题和字典表你可以使用这个SQL命令

SELECT `English` FROM `dict` 
ORDER BY
 levenshtein(`English`, 'USER_INPUT')
 ASC LIMIT YOUR_LIMIT(Number of suggestions you want)

注意:-您应该对用户输入进行过滤以防止 SQL 注入(inject)并删除特殊字符(这将提高速度并防止注入(inject)相关问题)