calculate values between ranges (help!)

kbc1

Hi all

I need to calculate a delivery price based on a set of rules outline below and my head is boxed trying to figure out how to do it. I will try to keep it as brief as possible to keep you insterested.

I suspect in advance this will be one of those posts that never gets a reply! :o)

My basket contains a total weight. eg. 15kg.

I have a table which stores a range of data (from, to, price, type are the fields in the mysql table), and the values of those fields I have displayed below:

Row 1: from = 0, to = 20, price = 5.00, type =1
Row 2: from = 21, to = 300, price = 0.15, type = 2
Row 3: from = 301, to = 500, price = 48, type =1

So if the total weight of my basket is 15kg, I would charge the customer £5 delivery using the rule in Row 1 above. Simple!

HOWEVER, if the weight of the basket is 22kg, I need to charge the customer £5 (for the first 20kg) and then 0.15p per KG for the remaining 7 kilograms of weight. (type = 2 means charge per item in the range, type = 1 means a surcharge).

For the life of me I cannot get this calculation written in PHP successfully.

Any ideas (or wishes of good luck ;O)) will be appreciated.

KBC

scrupul0us

switch(TRUE)
{
     case in_array($weight,range(0,20)):
          $price = '5.00';
          $type = '1';
          break;
     case in_array($weight,range(21,300)):
          $price = '.15';
          $type = '2';
          break;
     case in_array($weight,range(301,500)):
          $price = '48';
          $type = '1';
          break;
}

should get you going in the right direction...

johanafm

First off, you have a gap in the data for your limits.
from 0 to 20. from 21 to 300
should be:
from 0 to 20. from 20 to 300

Else, what happens for someone buying 20.5 kg?

Moreover, I'd change the limits to be (will simplify the sql and also the php calculations)

From		To	Price		Type
0		300	5		1
20		300	0.15		2
300		500	48		1

$weight = calcTotalWeight();	// I'm assuming you got this part working, however you do it.

$result = mysql_query('SELECT from, to, price, type FROM your_limit_table WHERE ' . $weight . ' BETWEEN from AND to');
while($row = $result)
	$limits[$row['type']] = $row;

// base cost is easy to get. the type=1 row will have this.
$baseCost = $limits['1']['price'];

/* remove the ceil() if you don't want to charge per "started kg", or use floor() if you
    feel nice. */
$weightOverSetLimit = ceil($weight - $limits['2']['from']);
$additionalCost = $limits['2']['price'] * $weightOverSetLimit;

$total = $additionalCost + $baseCost;

johanafm

I was in a hurry when writing the code in my previous post. You'll need to check that you get two rows in the SQL result and not just one, as when weight is in the fixed price range.

kbc1

Hi johanafm

That is a great help, it works perfectly for one row and two rows but it fails to record the previous two rows data if it goes onto a third row (i.e. 300 - 500).

Could you possibly help identify where and how we get it to ensure it keeps recording the additional cost if it loops into three rows?

Many Thanks for your help, it is greatly appreciated.

kbc

kbc1

Just to help, I echo the query and when (for example) my weight is 310, the query only gets one row from the table (the row where from=300 and to=500), I need this to get all records as I need to add the prices together to accumulate a final price.

Thanks for any advice you can offer.

tomas_teicher

I am not sure whether I understand your problem correctly. Maybe this will help

//data about ranges from db to multidimensional array. First dimension is range and second are attributes of the range
$range = array();
$range[0]['from'] = 0;
$range[0]['to'] = 20;
$range[0]['price'] = 5;
$range[0]['type'] = 1;
$range[1]['from'] = 20;
$range[1]['to'] = 300;
$range[1]['price'] = 0.15;
$range[1]['type'] = 0;

/*...
and so on...*/

$price = 0;
$weight = 100;  //for example
$i=0;
while ($range[$i]['to']<$weight){
$price += ($range[$i]['to'] - $range[$i]['from']) * $range[$i]['price'];
$i++
}
$price += ($weight - $range[$i]['from']) * $range[$i]['price'];

[/CODE]

johanafm

The intent of my code was to get just one or two rows from the table. I assumed that
0-20 kg would be charged £5 and nothing else.
20-300 would be charged £5 + £0.15 per Kg over 20kg.
300-500 would be charged £48 and nothing else.

Are you saying that for 300 Kg you want to charge 5 + 0.15 * 280 + 48 = £95?

What happens at 350? Do you add another 0.15 * 50 for this order, or would it still be £95?

And what happens beyond 500? Do you continue to add 0.15 / Kg, do you use another price / Kg or is 500 the limit?