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";
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
