在mysqli数组中查找最接近的值
在mysqli数组中查找最接近的值
提问于 2018-06-08 05:08:06
在我的应用程序中，用户可以输入一个定价数字，根据输入的数字，数据库将返回具有相同价格的计划。如果没有对应于用户输入的数字/价格，我希望程序找到具有最接近值的计划。我怎样才能在一堆干草中找到“最近”的值呢？
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
}回答 1
Stack Overflow用户
回答已采纳
发布于 2018-06-08 05:19:54
加上15%，在SQL查询本身中找到最接近的价格。
$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循环。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50750166
复制相关文章
相似问题