PHP - returning closest match from database

Question

I am returning mobile phone data based on user agent. But in the case when the user account is not stored (newer version of the phone or software), I want to be able to return the closest match, a bit similar to how Google displays "did it matter." i.e.

if i have saved useragent

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

and user agent used

Mozilla / 5.0 (Linux; U; Android 2.1-Update1; en; 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 saved one to add or adapt my data accordingly.

Does anyone know a way to achieve this?

Answer 1

Use full-text search with the most relevant data ...

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

Answer 2

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

Rather, reduce the line you are looking for certain important criteria, 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 take off certain details in too much detail and replace them with% in your LIKE statement. However, you should rethink the architecture - I would keep only the important parts and not take into account the exact build number, etc. Also think about using an external library that already contains user agents and is suitable for you, no need to reinvent the wheel.

EDIT: as Volkerk pointed out above, the "external library" must be a PHP getbrowser . Just added for completeness; -)

Answer 3

Take a look at one of the scenarios on Highlight the difference between two lines in PHP , with some changes you can get a percentage of the difference.

Answer 4

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

• similar_text()
• levenshtein()

Answer 5

To get the best result, you can extract entire entries and loop to guess which word is most similar

check these functions

SIMILAR_TEXT

SOUNDEX

LEVENSHTEIN

those.

 $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 mySQL Fulltext-search, following some recommendations, like this