MySQL a real LIKE statement

Question

I'm helping a friend build a dictionary-of-sorts for a project he's working on. Part of the project is to create a Search functionality. The database is in MySQL, backend in php.

Now, running our simple query was a piece of cake:

 SELECT *,
    (
        (CASE WHEN word LIKE '%$query%' THEN 1 ELSE 0 END) +
        (CASE WHEN defin LIKE '%$query%' THEN 1 ELSE 0 END)
    ) AS relev
    FROM dictionary
    WHERE word LIKE '%$q%'
    OR defin LIKE '%$q%'
    ORDER BY relev DESC;

It produced good results; for example, inputting "fire" gave us fire, firemen, firetruck, on fire, etc. However, we also want room for error: We want the mistake "prnk" to give us prank, prink and also pink, or the word "mule" to also suggest the word "mole".

Quite surprisingly, we weren't able to find any information on it. The relevence system is entirely superficial because we don't need actual relevence (just an overall pointer), but we do need something (and that's why we went for the LIKE statement and not the MATCH...AGAINST statement, where we found nowhere to sort by relevence.)

The database only consists of three things: id, word, defin. Simple as that, as that was the required complexity (or simplicity.)

Thanks to anyone in advance.

Answer 1

Try testing if the word sounds like one in the dictionary, so something along the lines of:

SELECT *,
    (
        (CASE WHEN word LIKE '%$query%' THEN 1 ELSE 0 END) +
        (CASE WHEN defin LIKE '%$query%' THEN 1 ELSE 0 END) +
        (CASE WHEN LEFT(SOUNDEX(word), 4) = LEFT(SOUNDEX('$query'), 4) THEN 1 ELSE 0 END) +
        (CASE WHEN LEFT(SOUNDEX(defin), 4) = LEFT(SOUNDEX('$query'), 4) THEN 1 ELSE 0 END)
    ) AS relev
    FROM dictionary
    WHERE word LIKE '%$q%'
    OR defin LIKE '%$q%'
    ORDER BY relev DESC;