Php – Finding the nearest numeric match in a database to what a user has inputted in php


I need to find a way in which to find a field in a database which; numerically, is the closet match to what a user has inputted in a web form and submitted.

Deeper explanation:

In a database table there are two rows with a field entry of 80.1 and 80.7. If a site user enters 80 into an input element on a web form and submits that value, the handling script looks up that table and finds a row with 80.1 and chooses this as the closet match.

If it was the other way round, i.e. the user entering a floated integer (80.6), the table looking for the nearest rounded number i.e. (90) that would be easy. I'm obviously missing something and thought there would be an in built function in PHP for this.

Apologies if I have not explained myself well, if so please let me know.

Best Solution

SELECT ABS($user_value - numeric_field) as nearest, ...
FROM yourtable
ORDER BY nearest ASC

basically, take the difference of the number field you want and the user-provided value, then sort by the difference ascending and then return the first row.

So an exact match would have a difference of 0 and come out first. And then pick out whichever one is "closest" if there's no exact match.