在mysqli数组php中查找最接近的值 [英] Finding nearest value in a mysqli array php
本文介绍了在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...
这就是我所拥有的:
//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
}
推荐答案
加15%并在SQL查询本身中找到最接近的价格.
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);
这只会返回您想要的一行,因此您不需要while
循环.
This will just return the one row that you want, so you don't need a while
loop.
这篇关于在mysqli数组php中查找最接近的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文