Shopping cart shipping logic

Shopping cart shipping logic

am 15.05.2008 05:12:16 von Ron Piggott

I need help writing a mySQL query and syntax that will determine the
shipping packing selected and will then determine the cost to ship. I
have assigned dollar values to various packaging. I have a designed the
following table which contains various packaging, the maximum size (in
centimeters) that packaging may contain and the cost to ship within
Canada, US or international:

shopping_cart_packaging_options:

reference int(2)
packaging_name varchar(50)
packaging_image_filename varchar(40)
package_length decimal(3,1)
package_width decimal(3,1)
package_height decimal(3,1)
packaging_cost_to_ship_canada decimal(3,2)
packaging_cost_to_ship_us decimal(3,2)
packaging_cost_to_ship_international decimal(3,2)

When I input a product I have been recording it's dimensions into this
table:

shopping_cart_product:

reference int(5)
category_reference int(3)
product_name varchar(50)
product_description longtext
product_length decimal(3,1)
product_width decimal(3,1)
product_height decimal(3,1)
supplier_reference int(3)
sku varchar(12)
reorder_alert int(5)
discontinued int(1)

The following code is from my check out script. It displays the
products being purchased at check out, quantity ordered and pricing. It
also creates the order record in the shopping_cart_orders table. I am
hoping some code could be added in here to figure out shipping costs
based on product dimensions and the dimensions of the available
packaging. I haven't been able to figure out how to compare the total
dimensions of the products with the available packaging. There are (2)
types of packaging: Tubes for posters (The poster category has a
category_reference of 2) and envelopes / boxes for everything else.
Perhaps a split shipment needs to take place if someone orders a poster
and something else that needs to ship in an envelope. I want the logic
behind this to optimize packaging shipping and handling costs and for
the order created to indicate to me which type of packaging I should use
that will hold all items ordered.

Thanks for helping me. Ron


mysql_connect('localhost',$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM ( shopping_cart_category INNER JOIN
shopping_cart_product ON shopping_cart_category.reference =
shopping_cart_product.category_reference ) INNER JOIN
shopping_cart_product_image ON
shopping_cart_product_image.product_reference =
shopping_cart_product.reference INNER JOIN shopping_cart_inventory ON
shopping_cart_inventory.product_reference =
shopping_cart_product.reference WHERE
shopping_cart_product_image.primary_image =1 ORDER BY product_name ASC";
$product_result=mysql_query($query);
$number_of_products=mysql_numrows($product_result);
mysql_close();

echo "Today's Order Is For:

\r\n";
echo "

Re: Shopping cart shipping logic

am 15.05.2008 05:17:18 von Ron Piggott

I have just made a change that I realize is going to be necessary for
the shipping logic queries. I have added "packaging_type int(1)" into
the table. Value 1 is for poster tubes; Value 2 is for all other

shopping_cart_packaging_options:

reference int(2)
packaging_name varchar(50)
packaging_type int(1)
packaging_image_filename varchar(40)
package_length decimal(3,1)
package_width decimal(3,1)
package_height decimal(3,1)
packaging_cost_to_ship_canada decimal(3,2)
packaging_cost_to_ship_us decimal(3,2)
packaging_cost_to_ship_international decimal(3,2)

On Wed, 2008-05-14 at 23:12 -0400, Ron Piggott wrote:
> I need help writing a mySQL query and syntax that will determine the
> shipping packing selected and will then determine the cost to ship. I
> have assigned dollar values to various packaging. I have a designed the
> following table which contains various packaging, the maximum size (in
> centimeters) that packaging may contain and the cost to ship within
> Canada, US or international:
>
> shopping_cart_packaging_options:
>
> reference int(2)
> packaging_name varchar(50)
> packaging_image_filename varchar(40)
> package_length decimal(3,1)
> package_width decimal(3,1)
> package_height decimal(3,1)
> packaging_cost_to_ship_canada decimal(3,2)
> packaging_cost_to_ship_us decimal(3,2)
> packaging_cost_to_ship_international decimal(3,2)
>
> When I input a product I have been recording it's dimensions into this
> table:
>
> shopping_cart_product:
>
> reference int(5)
> category_reference int(3)
> product_name varchar(50)
> product_description longtext
> product_length decimal(3,1)
> product_width decimal(3,1)
> product_height decimal(3,1)
> supplier_reference int(3)
> sku varchar(12)
> reorder_alert int(5)
> discontinued int(1)
>
> The following code is from my check out script. It displays the
> products being purchased at check out, quantity ordered and pricing. It
> also creates the order record in the shopping_cart_orders table. I am
> hoping some code could be added in here to figure out shipping costs
> based on product dimensions and the dimensions of the available
> packaging. I haven't been able to figure out how to compare the total
> dimensions of the products with the available packaging. There are (2)
> types of packaging: Tubes for posters (The poster category has a
> category_reference of 2) and envelopes / boxes for everything else.
> Perhaps a split shipment needs to take place if someone orders a poster
> and something else that needs to ship in an envelope. I want the logic
> behind this to optimize packaging shipping and handling costs and for
> the order created to indicate to me which type of packaging I should use
> that will hold all items ordered.
>
> Thanks for helping me. Ron
>
>
> mysql_connect('localhost',$username,$password);
> @mysql_select_db($database) or die( "Unable to select database");
> $query="SELECT * FROM ( shopping_cart_category INNER JOIN
> shopping_cart_product ON shopping_cart_category.reference =
> shopping_cart_product.category_reference ) INNER JOIN
> shopping_cart_product_image ON
> shopping_cart_product_image.product_reference =
> shopping_cart_product.reference INNER JOIN shopping_cart_inventory ON
> shopping_cart_inventory.product_reference =
> shopping_cart_product.reference WHERE
> shopping_cart_product_image.primary_image =1 ORDER BY product_name ASC";
> $product_result=mysql_query($query);
> $number_of_products=mysql_numrows($product_result);
> mysql_close();
>
> echo "Today's Order Is For:

\r\n";
> echo "

Re: Shopping cart shipping logic

am 15.05.2008 12:04:20 von Ron Piggott

I have figured it out. When I first started I was trying to do too much
in one command; when I broke it down into sections I figured it out. Ron
Ron

On Wed, 2008-05-14 at 23:17 -0400, Ron Piggott wrote:
> I have just made a change that I realize is going to be necessary for
> the shipping logic queries. I have added "packaging_type int(1)" into
> the table. Value 1 is for poster tubes; Value 2 is for all other
>
> shopping_cart_packaging_options:
>
> reference int(2)
> packaging_name varchar(50)
> packaging_type int(1)
> packaging_image_filename varchar(40)
> package_length decimal(3,1)
> package_width decimal(3,1)
> package_height decimal(3,1)
> packaging_cost_to_ship_canada decimal(3,2)
> packaging_cost_to_ship_us decimal(3,2)
> packaging_cost_to_ship_international decimal(3,2)
>
> On Wed, 2008-05-14 at 23:12 -0400, Ron Piggott wrote:
> > I need help writing a mySQL query and syntax that will determine the
> > shipping packing selected and will then determine the cost to ship. I
> > have assigned dollar values to various packaging. I have a designed the
> > following table which contains various packaging, the maximum size (in
> > centimeters) that packaging may contain and the cost to ship within
> > Canada, US or international:
> >
> > shopping_cart_packaging_options:
> >
> > reference int(2)
> > packaging_name varchar(50)
> > packaging_image_filename varchar(40)
> > package_length decimal(3,1)
> > package_width decimal(3,1)
> > package_height decimal(3,1)
> > packaging_cost_to_ship_canada decimal(3,2)
> > packaging_cost_to_ship_us decimal(3,2)
> > packaging_cost_to_ship_international decimal(3,2)
> >
> > When I input a product I have been recording it's dimensions into this
> > table:
> >
> > shopping_cart_product:
> >
> > reference int(5)
> > category_reference int(3)
> > product_name varchar(50)
> > product_description longtext
> > product_length decimal(3,1)
> > product_width decimal(3,1)
> > product_height decimal(3,1)
> > supplier_reference int(3)
> > sku varchar(12)
> > reorder_alert int(5)
> > discontinued int(1)
> >
> > The following code is from my check out script. It displays the
> > products being purchased at check out, quantity ordered and pricing. It
> > also creates the order record in the shopping_cart_orders table. I am
> > hoping some code could be added in here to figure out shipping costs
> > based on product dimensions and the dimensions of the available
> > packaging. I haven't been able to figure out how to compare the total
> > dimensions of the products with the available packaging. There are (2)
> > types of packaging: Tubes for posters (The poster category has a
> > category_reference of 2) and envelopes / boxes for everything else.
> > Perhaps a split shipment needs to take place if someone orders a poster
> > and something else that needs to ship in an envelope. I want the logic
> > behind this to optimize packaging shipping and handling costs and for
> > the order created to indicate to me which type of packaging I should use
> > that will hold all items ordered.
> >
> > Thanks for helping me. Ron
> >
> >
> > mysql_connect('localhost',$username,$password);
> > @mysql_select_db($database) or die( "Unable to select database");
> > $query="SELECT * FROM ( shopping_cart_category INNER JOIN
> > shopping_cart_product ON shopping_cart_category.reference =
> > shopping_cart_product.category_reference ) INNER JOIN
> > shopping_cart_product_image ON
> > shopping_cart_product_image.product_reference =
> > shopping_cart_product.reference INNER JOIN shopping_cart_inventory ON
> > shopping_cart_inventory.product_reference =
> > shopping_cart_product.reference WHERE
> > shopping_cart_product_image.primary_image =1 ORDER BY product_name ASC";
> > $product_result=mysql_query($query);
> > $number_of_products=mysql_numrows($product_result);
> > mysql_close();
> >
> > echo "Today's Order Is For:

\r\n";
> > echo "