I'm trying to make a simple partial address search utility in PHP. The table in question simply has an "address" column. My goal is to be able to have the user enter a partial address into a form and have my script retrieve the 25 closest matches in that table.
The obvious and, in my opinion, sloppy way to do this would be to select every address in the table then have a PHP loop go through each one of them, calculate percentage similarity to the search term, order them, and output. This seems like a big waste of resources when you consider that the table has tens of thousands of rows and I'm looking for at most 25.
I would like to do something like this:
SELECT id, firstname, lastname, PERCENTMATCH(address, $searchterm) AS matchpercent
FROM accounts
WHERE matchpercent > 85
ORDER BY matchpercent
LIMIT 25
I haven't been able to find any way to do that from within my query, however. Is this possible or do I have to go the sloppy route?