mysql 最接近的值,在mysqli数组php中查找最接近的值

In my app, the user can enter a number for pricing and based on the input, the database will return a plan with the same price. If there is no number/price corresponding to the user input, I would like the program to find the plan with the nearest value. How can I find the "nearest" value in a haystack?

Examples :

User inputs : $14, Returns the 15$ plan

User inputs : $20, Returns the 15$ plan

User inputs : 25$. Returns the 30$ plan

Etc...

This is what I have :

//Create pricing for each plan

$getplansql = "SELECT SUM(`Distributor Net Price`) AS dnetprice FROM `services` wspn

WHERE wspn.planName = '$planname_num[$pn]' AND wspn.planLevel = '$planlevels_num[$pl]'";

$resultplans = $conn->query($getplansql);

while($plan = mysqli_fetch_assoc($resultplans)) {// output data of each row

$inhousepricing = ($plan['dnetprice'] * 0.15) + ($plan['dnetprice']);

$finalpricing = round($inhousepricing);

if($planprice == $finalpricing) {//found matching row// there's a plan with that price

//put plan info in array

$planArray = array(

'planName' => $plan['name'],

'planPrice' => $finalpricing,

'planDescription' => $plan['description']

);

break;//stop statement and only get the first plan//row found

}else{//get the plan with the nearest value

//put plan info in array

}

解决方案

Add 15% and find the closest price in the SQL query itself.

$getplansql = "name, description, dnetprice

FROM (

SELECT planName AS name, planDescription AS description, ROUND(SUM(`Distributor Net Price`) * 1.15) AS dnetprice

FROM `services` wspn

WHERE wspn.planName = '$planname_num[$pn]' AND wspn.planLevel = '$planlevels_num[$pl]'

) AS x

ORDER BY ABS(dnetprice - $planprice)

LIMIT 1";

$resultplans = $conn->query($getplansql);

$planArray = mysqli_fetch_assoc($resultplans);

This will just return the one row that you want, so you don't need a while loop.