CODEWITHSUNDEEP
phparraysfor-loopmatch
Frank
Frank

Reputation: 3

Finding a value in a php array

I've been banging my head hard over this problem for the last 2-3 days trying to see the problem from as many different angles as possible but to no avail. I'm turning to the SO community for extra perspectives. Below is the code I have which prints all 9 product plans. I'm wanting to find and print the plan with pricing equals or closest to a given user input. How can I do this?

//arrays of productnames
$productnames=array(1=>"Beginner","Advanced","Expert");

//arrays of productlevels
$productlevels=array(1=>"Bronze","Silver","Gold");

//Get The Length of Product Name Array
$planname_array_length=count($productnames);

//Get The Length of Product Level Array
$planlevel_array_length=count($productlevels);

for ($prn=1; $prn <= $planname_array_length; $prn++) {//loop to create plan name indicators 
    for ($prl=1; $prl <= $planlevel_array_length; $prl++) {//loop to create plan level indicators 

        $getpoductsql = " SELECT name, level,productNameId,productLevelId,finalProductPrice
                        FROM ( 
                        SELECT wspn.productName AS name, wspl.productLevel AS level, wsp.productNameId AS productNameId, wsp.productPlanLevel AS productLevelId, 
                        ROUND(SUM(`Price`) * 1.12) AS finalProductPrice,
                        FROM `products` ws 
                        left join product_plan wsp on wsp.productId = ws.wsid 
                        left join product_plan_level wspl on wsp.productPlanLevel = wspl.wsplid 
                        left join product_plan_name wspn on wspn.wspnid = wsp.productNameId 
                        WHERE wspn.productName = '$planname_array_length[$pn]' AND wspl.productLevel = '$planlevel_array_length[$pl]'
                        )
                        AS x ORDER BY ABS(finalProductPrice - $compareprice)"
            $resultproducts = $conn->query($getpoductsql);
        $prodArray = mysqli_fetch_array($resultproducts);

        //print array of each plan
        $resultArr = array('planNameID' => $prodArray['planNameId'], 
                           'planName' => $prodArray['name'], 
                           'planLevelID' => $prodArray['planLevelId'],
                           'planLevelName' => $prodArray['level'],
                           'planPrice' => $prodArray['finalProductPrice'];

                           //print arrays of products
                           echo json_encode($resultArr);

    }
}

This will output 9 plans as follow :

{"planNameID":"1","productName":"Beginner","productLevelID":"1","productLevelName":"Bronze","productPrice":"15"}

Upvotes: 0

Views: 56

Answers (3)

Amr Berag
Amr Berag

Reputation: 1118

The way you are doing it will produce invalid json, do it like this: 

  $result=array(); 
    for ($prn=1; $prn <= $planname_array_length; $prn++) {   
      for ($prl=1; $prl <= $planlevel_array_length; $prl++) {   
            .     .     .     // the other code   
     //print array of each plan          
   $resultArr = array('planNameID' => $prodArray['planNameId'],    
                             'planName' => $prodArray['name'],                                 'planLevelID' => $prodArray['planLevelId'],                 
               'planLevelName' => $prodArray['level'],   
        'planPrice' => $prodArray['finalProductPrice']; 
                                    //print arrays of products     
                            $resul[]=$resultArr;        
                                      }//loop1   
  }//loop2   

      echo json_encode($result);

you should also add the limit 1 and do the rest in JS in the front end

Upvotes: 0

Barmar
Barmar

Reputation: 782785

Rather than performing a separate query for each product name and product level, do them all in one query, and let MySQL find the one with the closest price.

   $getpoductsql = " SELECT name, level,productNameId,productLevelId,finalProductPrice
                    FROM ( 
                    SELECT wspn.productName AS name, wspl.productLevel AS level, wsp.productNameId AS productNameId, wsp.productPlanLevel AS productLevelId, 
                    ROUND(SUM(`Price`) * 1.12) AS finalProductPrice,
                    FROM `products` ws 
                    left join product_plan wsp on wsp.productId = ws.wsid 
                    left join product_plan_level wspl on wsp.productPlanLevel = wspl.wsplid 
                    left join product_plan_name wspn on wspn.wspnid = wsp.productNameId 
                    WHERE wspn.productName IN ('Beginner', 'Advanced', 'Expert') AND wspl.productLevel IN ('Bronze', 'Silver', 'Gold')
                    GROUP BY productNameId, productLevelId
                    )
                    AS x ORDER BY ABS(finalProductPrice - $compareprice)"

Upvotes: 1

JJ Brown
JJ Brown

Reputation: 587

forgive my formatting, I'm on mobile

Like Amr Berag said above, your result should be the first row returned from your query.

If you have a table like this:

ID    value
----   ------
A       7
B       12
C       23
...

You can then SELECT from this table to find the closest to some value, like so:

(Assume your desired value is $VALUE)

SELECT id, value, ABS(value - $VALUE) AS diff
FROM your_table
ORDER BY diff ASC

This will return something like this (say $VALUE is 10):

id  value  diff
--  ------  ----
B    12   2
A    7     3
C    23   13
...

You can just pick the first row.

You may also be able to add a WHERE clause to only select the row with the least difference using the MIN function:

SELECT id, value, ABS(value - $VALUE) AS diff
FROM your_table
WHERE diff = MIN(diff)

Upvotes: 0

Related Questions