在php数组中查找值

Question

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"}

Answer 1

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)"