On an estore Im working on, I made the option to calculate shipping based on order weight. Eg, an order weighing between 0 - 5lbs could cost 4.95, an order weighing 5 - 10 could cost 9.95, etc. Each product has a unique weight.

My database table 'shipping' has such pricing. It currently reads:

id price weight_min weight_max
1 4.95 0 5
2 5.95 5 10
3 7.95 10 15

And in a table called 'product', there is a field called weight. I want to add up the weight of each individual product in the cart, and then find which interval its in.

Sessions are arranged:
$_SESSION['product'][''.$prodid.'']['quantity'][''.$prodquantity.'']

and the query on checkout is


foreach($_SESSION['product'] as $key => $v)
{
foreach($_SESSION['product'][''.$key.'']['quantity'] as $k => $v2)
{

$result = mysql_query("SELECT name,gross_price,shipping,sku,id,weight FROM product WHERE id='".$key."' ") or die("product foreach failed. ".mysql_error());
$prod = mysql_fetch_object($result);


What do I do to total up all of the weights and find the interval? To total the weights I figure I can do

$shipping = 0; // above the loopage
$shipping = $shipping + $prod->weight;


But then i am lost on how to find the interval.

First algorithm that came to my mind, rather simple:

find tuples in the shipping table by weight_max ascending order:
select weight_max from shipping order by weight_max asc;

starting with the lowest weight_max, check to see if the weight of the items are =< weight_max

if so do:
select price from shipping where weight_max = '$weigh_max';
set that equal to $shipping

if not:
check next_record()

This is assuming that the shipping table is logically correct (ie. some weight tuples' weight range do not interfere with other tuples' weight range)

Also, if the weight is increasing at a set interval... you can mathematically figure out easily.

Why do you need 4 dimension array ?

$_SESSION[$prodid][qty]

Originally posted by alpha
First algorithm that came to my mind, rather simple:

find tuples in the shipping table by weight_max ascending order:
select weight_max from shipping order by weight_max asc;

starting with the lowest weight_max, check to see if the weight of the items are =< weight_max

if so do:
select price from shipping where weight_max = '$weigh_max';
set that equal to $shipping

if not:
check next_record()

This is assuming that the shipping table is logically correct (ie. some weight tuples' weight range do not interfere with other tuples' weight range)

Also, if the weight is increasing at a set interval... you can mathematically figure out easily.

I see what you are saying, but I have not worked out a system similiar to this ever and php.net is not being very helpful. Could you help me out with a few code snippets to get mre rolling?

To me.. all you're trying to do is add up all the products in the shopping cart, multiply them by the quantity then by the weight, and add everything up? And then if the weight is between a certain amount, set the shipping cost?

It also seems to me, you are mis-using the world 'interval'

First, as utsn pointed out, you don't need a 4 dimentional array.

I'm going to make a simple case and hopefully you can edit it for your application.


/* Assuming that $products is an array that contains
all the products in the shopping cart (their ids) and the
quantity purchased something like

$products[1] = 2;
$products[2] = 4;

Here, the product which has the id of 1 was added 2 times
to the cart, and the product of id 4 was added 4 times.
*/
$total_weight = 0.0;

while(list($pid, $quantity) = each($products))
{
$query = "SELECT weight FROM product WHERE id = '".$pid."'";
$result = mysql_query($query);
if (!$result)
{
die ("MySQL error while executing ".$query."<br />".mysql_error());
}
$p_data = mysql_fetch_assoc($result);
$total_weight = $total_weight + ($p_data['weight'] * $quantity);
}

//We now know the total weight of the shopping cart
//So we are going to find the shipping rate

$query = "SELECT price FROM shipping WHERE ".$total_weight." BETWEEN weight_min AND weight_max";

$result = mysql_query($query);
if (!$result)
{
die("MySQL Error while executing<br />".$query."<br />".mysql_error());
}

$s_data = mysql_fetch_assoc($result);

$shipping_cost = $s_data['price'];

echo "The shipping cost will be ".$shipping_cost;


Let me know if you need help understanding any part of the snippet.

Hey thanks for all of your help guys!

fyrestrtr, modifying your code worked great. I didn't know mysql had a between operator, heh. Thanks again!

Originally posted by FW-Mike
Hey thanks for all of your help guys!

fyrestrtr, modifying your code worked great. I didn't know mysql had a between operator, heh. Thanks again!


Man.... MYSQL has many more usefully functions... hundreads..


Take a look at the manual..

Sometimes we make 20 line of php codes to do just a matching of one line of mysql query!!!

Peace,