Workout the highest postage cost:

[roldahayes]

Hi, 

Please can someone point me in the right direction to solving this.

 

The following code set the postage rate on a basket.  It selects the lowest postage assigned to a product and set that as the postage rate.

 

I now need this to work the opposite way round and select the highest postage rate of the added products.

 

The postage rates are stored in the database as:

 

0=0.00

1=4.95

2=7.95

3=12.95

 

So it looks like somewhere there has been something added that tells it to always give preference to the "0" of any products giving the lowest postage but I'm sure on this....

 

 

Thanks.

 

//set default postage value outside loop$postagerate = 15.00; while ($row = mysql_fetch_assoc($result)){  //decide which postage value is the highest and use that to calculate overall price  //get the postage values for each product  $sqlpostquery = "SELECT * FROM postage WHERE Post_ID = '" . htmlspecialchars($row['Post_ID']) . "'";   //get the postage values from the database  $postresult = mysql_query($sqlpostquery);  $rowpost = mysql_fetch_assoc($postresult);   // check if postage value was available  if ($postresult || !(mysql_num_rows($postresult) == 0))    {    $rawpostage = htmlspecialchars($rowpost['Post_Cost']) ? htmlspecialchars($rowpost['Post_Cost']) : 0.00;     //get the lowest postage rate.    if ($postagerate > $rawpostage)      {        $postagerate = $rawpostage;    }  }  else  {    $postagerage = 0.00;  }   //round postage rate of 2 decimal places  $postagerate = $postagerate;   //release the postage resultset array 
  mysql_free_result($postresult);

[mac_gyver]

in whatever sql query is being run right before the code you posted, just use MAX(Post_Cost) in the query to get the highest post cost value for the set of matching rows.

[maxxd]

It looks like your SQL needs to be re-evaluated. Without more of the code, it's difficult to tell, but it certainly looks like you're doing a separate query within a loop of results from a previous query. Don't. Chances are high that this setup can be modified using a JOIN in the original SQL SELECT statement to return all the data you need in a single call to the database; this'll not only save system resources and page load time, it makes the results much easier to deal with programmatically. It's technically possible that the method you're using is the best approach depending on the table setup, but usually if you can't use a JOIN to gather all the data in one go the database structure is bad.

[mac_gyver]

since this code seemed familiar, i went looking through your previous threads, and found that i had helped with bits of it before.

 

this code is responsible for retrieving and displaying the content of the shopping basket. you would do what maxxd has suggested above, and join the postage table with the existing basket/products join query to get the Post_Cost for each item in the basket. while you are looping over the result of that ONE query, you would detect and save the highest Post_Cost value. you could also, in the loop, store the Post_Cost values into an array, and just use php's max() function on that array to get the highest Post_Cost value.

 

you, or who originally wrote this, easily have two times more php code that what is needed, resulting in it taking 5-10 times longer to find and fix any problems or just make changes to what the code is doing (and reducing the chance of someone helping with it as who wants to read through a wall of verbose and repetitive code that can be replaced with a couple of statements.) taking the time to reorganize and reduce the code, will actually save time overall, and make it easier to switch from using the depreciated mysql_ functions, since there will be less overall calls to the mysql_ functions and all the database logic that stores, updates, and retrieves the data for the page will (should) be grouped near the start of the code on the page. the html on the page will (should) be grouped at the end, with only simple php echo, conditional, and loop statements in it.

 

edit: assuming you use a single joined query and that you have a Post_Cost associated with each item in the basket, the following pseudo logic is all you would need to find the highest Post_Cost value - 

$Post_cost = array();
while(...){ // your existing while loop

    $Post_cost[] = $row['Post_Cost']; // save each post cost in the array

} // end of your existing while loop
$highest_post_cost = max($Post_cost); // find the maximum value in the array

and if you want the $postagerate = 15.00; amount to be the minimum postage value, just put that value into the $Post_cost array before the start of the while(){} loop.

[roldahayes]

Thanks for the replys.

 

 

you, or who originally wrote this, easily have two times more php code that what is needed, resulting in it taking 5-10 times longer to find and fix any problems or just make changes to what the code is doing

 

This is exactly the problem that I am having.... The code was written a few years ago by someone else and I have tacked bit and pieces on over the last few years so I'm totally lost with it all to be honest....

 

From what I can see, the following code has all of the postage block info in it.

 

I dont actually need the $postagerate=15.00;  part at all....  as each product in the database has a Post_Cost associated to it, it just needs to select the highest of all of these.

 

<?php$sqlquery = "SELECT products.Prod_REF, basket.productID, basket.quantity, products.Prod_Make, products.Prod_Model, products.Prod_Type, products.Car_Make, products.Car_Model, products.Price_ExVat, products.Post_ID, Product_Desc FROM basket INNER JOIN products ON basket.productID = products.Prod_ID WHERE ((basket.userID) = '" . $userID . "')"; $result = mysql_query($sqlquery);$rowCount = mysql_num_rows($result); if (!$result || (mysql_num_rows($result) == 0)){ } else //*************display contents of basket////////////////////////////////////////////////////// { // echo each header from array //foreach ($dbFields as $headIndex) // echo an extra blank header for the delete item column // fetch each row as an associative array $counter = 1; $price = 0;  //set default postage value outside loop$postagerate = 15.00; while ($row = mysql_fetch_assoc($result)){  //decide which postage value is the highest and use that to calculate overall price  //get the postage values for each product  $sqlpostquery = "SELECT * FROM postage WHERE Post_ID = '" . htmlspecialchars($row['Post_ID']) . "'";   //get the postage values from the database  $postresult = mysql_query($sqlpostquery);  $rowpost = mysql_fetch_assoc($postresult);   // check if postage value was available  if ($postresult || !(mysql_num_rows($postresult) == 0))    {    $rawpostage = htmlspecialchars($rowpost['Post_Cost']) ? htmlspecialchars($rowpost['Post_Cost']) : 0.00;     //get the lowest postage rate.    if ($postagerate > $rawpostage)      {        $postagerate = $rawpostage;    }  }  else  {    $postagerage = 0.00;  }   //round postage rate of 2 decimal places  $postagerate = $postagerate;   //release the postage resultset array   mysql_free_result($postresult);   echo "<form action=basket.php method=get name=form".$counter.">     <input name=update type=hidden value=yes>     <input name=productID type=hidden value=". $row['productID'] ."><tr class=stdtable>";   echo("<td align=center> " . htmlspecialchars($row['Car_Model']) . "</td>");   echo("<td align=center> " . htmlspecialchars($row['Prod_REF']) .  "</td>");   echo("<td align=left>" . htmlspecialchars($row['Product_Desc']) . "</td>");   //echo("<td align=center>". htmlspecialchars($row['Prod_REF']) ."</td>");   // for the final column echo an hyperlink to delete the product entry     //settype($row["Price_ExVat"], "integer");   echo "<td align=center><input name=updateQuantity onchange=submit(); type=text size=2 value=". htmlspecialchars($row['quantity'])."></td><td align=center>". $currency . number_format(htmlspecialchars($row['Price_ExVat']), 2) ."</td><td align=center>". $currency . number_format(calcVAT($row["Price_ExVat"]), 2) ."</td><td align=center>". $currency . number_format((calcVAT (htmlspecialchars($row['Price_ExVat'] * $row['quantity']))), 2)  ."</td>";   echo "<td align=center><a href=\"basket.php?delete=yes&productID=" . $row['productID'] . "\"><img src=2003/remove.gif border=0></a></td>";   echo "</tr></form>";   $counter ++;   //get a cumulative value of the price as items are added to the basket and multiply by quantity as we go.   $price = $price + (calcVAT (htmlspecialchars($row['Price_ExVat']))) * htmlspecialchars($row['quantity']);  } //assign subtotal and round to 2 decimal places $subtotal = $price; $total = $subtotal + $postagerate; //pick overall postage type if ($postagerate == 5) $postage = 3; else if ($postagerate == 9) $postage = 2; else if ($postagerate == 10) $postage = 1; } //update shopper table with new/changed info $sqlshopper = "SELECT * FROM shopper WHERE User_ID = '" . $userID . "'"; //echo "query: " . $sqlshopper; $result = mysql_query($sqlshopper); $rowCount2 = mysql_num_rows($result); //add shopper if ($rowCount2 == 0) { $sqladd = "INSERT INTO shopper" . $shopperFields . "VALUES ('" . $userID . "', '" . $total . "', '" . $postage . "')"; //echo "noshopper: " . $sqladd; $shopadd = mysql_query($sqladd); if (!$shopadd) echo "<font class=error><p>Your basket has not been processed</p></font>"; } else { //update details $sqlupdate = "UPDATE shopper SET Basket_total = '" . $total . "', Postage = '" . $postage . "' WHERE user_ID = '" . $userID . "'"; //echo "shopper: " . $sqlupdate; $shopupdate = mysql_query($sqlupdate); if (!$shopupdate) echo "<font class=error><p>Your basket has not been updated</p></font>"; } // finish table

Edited December 16, 2014 by roldahayes

[mac_gyver]

here's the JOIN'ed query that (untested) should get the Post_Cost for each item in the basket -

$sqlquery = "SELECT p.Prod_REF, b.productID, b.quantity, p.Prod_Make, p.Prod_Model, p.Prod_Type,
p.Car_Make, p.Car_Model, p.Price_ExVat, Product_Desc, pstg.Post_Cost
FROM basket b JOIN products p ON b.productID = p.Prod_ID
JOIN postage pstg ON p.Post_ID = pstg.Post_ID
WHERE b.userID = '$userID'";

then just change your existing max post cost code to make use of the example code i posted in the reply #4 above.

 

here's another hint, all code for the select, insert, update queries for the shopper table, besides not being needed at all (the total and postage are derived values, that should not be stored, but calculated whenever needed), can be replaced with one INSERT ... ON DUPLICATE KEY UPDATE ... query (the user_id column would need to be a unique index.) so, basically, all you would need is two lines of code. the ONE query and to run the query.

[roldahayes]

Thanks for the great advice.

 

I have read up on JOIN'ed queries and can see the logic in the way you have suggested.

 

Now i'm going to spend some time on clearing up the necessary code to make life easier in the future!

 

Thanks again,

Edited December 17, 2014 by roldahayes

[roldahayes]

Sorry to drag this up again......

 

How would I override the postage on certain items?

 

i.e if the 'Post_ID' was 7, it would automatically default the postage to 0.00  regardless of the code telling it to select the max postage

[Barand]

You could put the condition in the query EG

SELECT (CASE Post_Id WHEN 7 THEN 0.00 ELSE Post_cost END) as Post_cost, ...