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 "<u>Today's Order Is For:</u><p>\r\n";
> > echo "<ul>\r\n";
> >
> > echo "<table border=\"1\">\r\n";
> >
> > echo "<tr><td width=\"225\"><font face=\"times new roman
> > \"><center><u>Product Name</u></center></td>\r\n";
> > echo "<td width=\"100\"><font face=\"times new roman\"><center><u>Unit
> > Price</u></center></td>\r\n";
> > echo "<td width=\"125\"><font face=\"times new roman
> > \"><center><u>Quantity Ordered</u></center></td>\r\n";
> > echo "<td width=\"100\"><font face=\"times new roman
> > \"><center><u>Total</u></center></td></tr>\r\n";
> >
> > $product_cost_total = 0;
> > $i=0;
> > while ($i < $number_of_products) {
> >
> > $product_reference = mysql_result($product_result,
> > $i,"shopping_cart_product.reference");
> > $product_name = mysql_result($product_result,
> > $i,"shopping_cart_product.product_name");
> > $quantity = $_SESSION['selection'][$product_reference];
> > $cost_of_product = mysql_result($product_result,
> > $i,"shopping_cart_inventory.selling_price");
> >
> > if ( $_SESSION['selection'][$product_reference] > 0 ) {
> >
> > echo "<tr><td width=\"225\"><font face=\"times new roman\"><center><a
> > href=\"" . $path_to_shopping_cart . "product/" .
> > stripslashes($product_reference) . "/\">" .
> > stripslashes($product_name) . "</a></center></td>\r\n";
> > echo "<td width=\"100\"><font face=\"times new roman\"><center>$" .
> > $cost_of_product . "</center></td>\r\n";
> > echo "<td width=\"125\"><font face=\"times new roman\"><center>" .
> > $quantity . "</center></td>\r\n";
> > echo "<td width=\"100\"><font face=\"times new roman\"><center>$" .
> > number_format(($quantity * $cost_of_product), 2, '.', '') .
> > "</center></td></tr>\r\n";
> >
> > $product_cost_total = number_format(($product_cost_total +
> > ($cost_of_product*$quantity)), 2, '.', '');
> >
> > #record product(s) being sold to the shopping_cart_products_sold table
> >
> > $shipping_order_reference = $_SESSION['shipping_order_reference'];
> >
> > mysql_connect('localhost',$username,$password);
> > @mysql_select_db($database) or die( "Unable to select database");
> > mysql_query("INSERT INTO `shopping_cart_products_sold` ( `reference` ,
> > `orders_reference` , `product_reference` , `quantity` ) VALUES ( NULL ,
> > '$shipping_order_reference', '$product_reference', '$quantity' );");
> > $saved_product_order_reference = mysql_insert_id();
> > mysql_close();
> >
> > }
> >
> > ++$i;
> > }
> >
> > echo "</table>\r\n";
> >
--
ron.piggott@xxxxxxxxxxxxxxxxxx
www.actsministrieschristianevangelism.org
Acts Ministries Christian Evangelism
"Where People Matter"
12 Burton Street
Belleville, Ontario, Canada K8P 1E6
In Belleville Phone : (613) 967-0032
In North America Call Toll Free : (866) ACTS-MIN
Fax: (613) 967-9963
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
