"Did you mean" feature with php and mysql

Question

I have been creating a search function in a website, and I want to add a function of type correction. When I add in these code, the output is always the first one of the "words" array, please help!

This is my code:

<?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 />";
?>

Did you try it with different examples? And did you check the [levenshtein example](http://php.net/manual/en/function.levenshtein.php)? — agold, Oct 11 '15 at 09:37
That is where I got my idea from, my code is based on the example from that website. — Jay, Oct 11 '15 at 10:34
I think the code makes pretty much sense to me... but the result is always the first item of my database, does it mean that the loop doesn't work? Or the loop stops after the first time? — Jay, Oct 11 '15 at 12:46
How can you ask a question on Stack Overflow about you wanting help with your code, then when people ask you what's wrong with your code, you don't know? That's like asking someone to wallpaper your kitchen but you don't know what wallpaper you want. — desbest, Oct 11 '15 at 13:17
Sorry, but what I meant was "When I run the code, it always outputs the first item of the database.But I don't know the reason." — Jay, Oct 13 '15 at 13:34

score 1 · Accepted Answer · edited May 23 '17 at 12:22

In MySQL add levenshtein function.

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 ;

Note:- This answer is sourced from answers on this stack-overflow question How to add levenshtein function in mysql?

Tip:- You should run this SQL script/code in MySQL console or PHPMyAdmin as you execute normal SQL commands.

And Now you can use this function like this

SELECT levenshtein('abcde', 'abced')

Returns : 2

According to your problem and dictionary table you can use this SQL command

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

Note:- You should do FILTERING ON USER INPUT FOR PREVENTION OF SQL INJECTION AND REMOVE SPECIAL CHARS(It will improve speed as well prevent from injection related problems)

score 0 · Answer 2 · answered May 18 '19 at 20:05

Following the example I change the use of variable from arrays to string when used by the levenshtein function and echo.

    <?php

         // db connection parameter
        require $_SERVER['DOCUMENT_ROOT'] . "/php/db-params.php";

        $stmt = $pdo->query( "SELECT name FROM cities500 LIMIT 1999" );
        $words = $stmt->fetchAll( PDO::FETCH_ASSOC );
        $input = "nevyork";
        $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["name"] );
            //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["name"]."?<br />";

    //};
?>

Marcin Żurek · Answer 3 · 2021-07-29T11:29:38.837

I had same issue and I decide to write custom simple function to do that. In my case works fine:

<?php 
$input = 'inputWord';

$statement = $connection->prepare("SELECT name FROM `words`");
$statement->execute(array());
$rows = $statement->fetchAll();
$resultArray = [];

foreach ($rows as $row) {
    $compSum = 0;
    for ($i = 0; $i < strlen($row[0]) - 1; $i++) {
        $searchPosition = strpos($input, $row[0][$i] . $row[0][$i + 1]);
        if (is_numeric($searchPosition)) {
            $compSum += abs($searchPosition - $i);
        } else {
            $compSum += 10;
        }
    }
    $lenDiference = strlen($input) - strlen($row[0]);
    if ($lenDiference > 0) {
        $compSum += $lenDiference * 10;
    }
    $resultArray[] = array('word' => $row[0], 'compSum' => $compSum);
}

function sortBySum($a, $b) {
    return $b['compSum'] - $a['compSum'];
}

usort($resultArray, 'sortBySum');
$bestMatch = array_pop($resultArray);

echo 'Input word: ' . $input . '<br/>';
if ($bestMatch['compSum'] < 50) {
    echo 'Did you mean: ' . $bestMatch['word'];
}

"Did you mean" feature with php and mysql

3 Answers3