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
