Finding nearest value in a mysqli array php

Question

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  
    }

Barmar · Accepted Answer · 2018-06-08 00:52:07Z

1

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.

edited Jun 8, 2018 at 0:52

answered Jun 7, 2018 at 21:19

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

18 Comments

Gary J. Over a year ago

Yes it does need to be in the while loop. I will update my answer and show you the reason why.

Barmar Over a year ago

If there are multiple rows with the closest price, do you need all of them?

Gary J. Over a year ago

No I just need one row.

Barmar Over a year ago

Then why would you need a loop? It will just return 1 row, so you call mysqli_fetch_assoc() once to get it.

Gary J. Over a year ago

Ok you are right with the while loop, I will remove that. However, I can't do the comparison in the statement because the pricing is not in the table. I'm using SUM() to output the price by adding columns together then I'm adding 15% to come up with final pricing. I will update my answer for you to see the statement.

|

Collectives™ on Stack Overflow

Finding nearest value in a mysqli array php

1 Answer 1

18 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

18 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related