Nearest Value in the Database table

Question

I want to Find the nearest numeric match in a database to what a user has inputed in php. The Database value can be multipled with an integer to find the near value to user Input.

For Ex: Database has the following Pallets:

950, 900, 1070

User Input a Value: 2000

Not the System should check:

950 * 2 = 1900
900 *2 = 1800
1070 * 2 = 2140

So the nearest value to 2000 is 1900. Any Help will be appreciated.

Possible duplicate of [how to get nearest value from database in mysql](https://stackoverflow.com/questions/7269243/how-to-get-nearest-value-from-database-in-mysql) — dns_nx, Oct 09 '17 at 04:22

nithinTa · Accepted Answer · 2017-10-09T04:37:09.333

1

Try this query, also replace 2000 with user input.

SELECT your_column, abs( 2000 MOD your_column) ) as diff
FROM `mytable`
ORDER BY diff
LIMIT 1

edited Oct 09 '17 at 04:37

answered Oct 09 '17 at 04:26

nithinTa

1,632
2
16
32

But the number 2 may be variable i.e it can either be 3 etc. – Mohammad Nomaan Patel Oct 09 '17 at 04:28
Hi @tan, Thanks for the query. It has solved my issue. – Mohammad Nomaan Patel Oct 17 '17 at 06:07

score 0 · Answer 2 · answered Oct 09 '17 at 04:31

0

SELECT your_column, abs( $_POST['form_field_name']-(your_column*2) ) as setcustomcolumn FROM `rablename` order by columnname LIMIT 1

answered Oct 09 '17 at 04:31

Ravi Chauhan

1,409
13
26

give some explanation for your answer – Thamaraiselvam Oct 09 '17 at 06:42

Nearest Value in the Database table

2 Answers2