PHP - returning closest match from database

I'm returning mobile phone data based on the user agent. But in an instance where the useragent is not stored (newer version of phone or software) I want to开发者_如何学C be able to return the closest match, a bit like how Google displays the "did you mean this". i.e.

if I have a stored useragent of

Mozilla/5.0 (Linux; U; Android 2.1-update1; en-nl; Desire_A8181 Build/ERE27) AppleWebKit/530.17 (KHTML, like Gecko) Version/4.0 Mobile Safari/530.17

and the user agent in use is

Mozilla/5.0 (Linux; U; Android 2.1-update1; en-nl; Desire_G45H Build/ERE27) AppleWebKit/530.17 (KHTML, like Gecko) Version/4.0 Mobile Safari/530.17

I want to be able to return the one stored to add or adapt my data accordingly.

Does anyone know a way of achieving this?

use full text searching with most relevant data...

SELECT * MATCH(browser) AGAINST ('your browser') AS score order by score DESC

The usual approach for fuzzy string matching are things like calculating the levenshtein distance or implementing it as an n-gram search index. But for matching user agents, this is overkill.

Rather reduce the string you search for to certain important criterias, then do something like

SELECT * FROM agents WHERE agent LIKE "Mozilla/5.0 (Linux; U; Android%) AppleWebKit/5% Version/4.0 Mobile Safari/5%"

So, you strip out certain too detailed parts and replace them by % in your LIKE statement. You should, however, reconsider the architecture - I would only save the important parts and leave out the exact build number etc. Also consider using an external library that already contains user agents and does the matching for you, no need to reinvent the wheel.

EDIT: just as VolkerK pointed out above, the "external library" should be PHPs getbrowser. Just added for compeleteness of the answer ;-)

Take a look at one of the scripts on Highlight the difference between two strings in PHP, with some modifications you should be able to get a percentage of the difference.

Use full-text search; otherwise these functions may help:

• similar_text()
• levenshtein()

To get the best result, you can extract the whole records and do a cycle to guess who's the most similar word

check these functions

SIMILAR_TEXT

SOUNDEX

LEVENSHTEIN

I.E.

$most_similar = "";
$highestPercentage = 0;
foreach ($ua in $all_user_agents) {
    $i = similar_text($current_user_agent, $ua, &$p);
    if ($p > $highestPercentage) {
        $highestPercentage = $p;
        $most_similar = $ua;
    }
}

echo "most similar = $most_similar"

Anyway, you can use the mySQL Fulltext-search following some guidelines like this one