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

Question

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.

Answer 1

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

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.

Answer 2

try this

SELECT * FROM `table_test1` where `price` > 80 order by `price` asc limit 1

Answer 3

SELECT ABS(price - 80) AS nearest FROM table ORDER BY nearest ASC LIMT 1;

Answer 4

Difficult to give you an exact answer, however here is some pseudo SQL which might help you out:

SELECT number, abs((number * 100) - ({$search_number} * 100)) AS distance 
FROM your_table 
ORDER BY distance 
LIMIT 1