By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,118 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Help With Query Join Types For Shipping Rates

P: 11
Hi there,

I'm writing a very simple shopping cart for a friend, but I'm confusing myself with the SQL required to return postage costs for each product in the cart, at checkout.

It's probably best to show you my tables first, each postage rate can apply to multiple products, product ranges, and countries. For example, postage rate X could be $80 postage to USA, France, Germany for all products within ranges 1,2,3 and product 15 which is not in the aforementioned ranges.

At checkout, my script loops through each product in the cart passing the productID and customer's countryID to a function which queries the database for a postage rate for the item. These rates are then added together to give the postage total for an order.

Ideally, I would like the postage rate for a productID to override that of a rangeID if applicable. My table structures are below:

Expand|Select|Wrap|Line Numbers
  1. product (productID*, productName, productPrice, productRangeID)
  2. shopPostage (postageID*, postagePrice, postageFriendlyName)
  3. shopPostageProduct(ID*, postageID, productID)
  4. shopPostageRange(ID*, postageID, rangeID)
  5. shopPostageCountry(ID*, postageID, countryID)
  6.  
Here is my attempt at the SQL for returning a product's postage, as you can see I haven't been able to include shopPostageCountry (really confused!)

Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM ((product 
  3. RIGHT JOIN shopPostageRange ON productRangeID=shopPostageRange.rangeID)
  4. RIGHT JOIN shopPostageProduct ON product.productID=shopPostageProduct.productID)
  5. INNER JOIN shopPostage ON 
  6. shopPostageProduct.postageID=shopPostage.postageID
  7. OR
  8. shopPostageRange.postageID=shopPostage.postageID
  9. WHERE product.productID =13 AND shopPostageCountry.countryID=2
  10.  
Any help with this would be greatly appreciated!
Kind Regards,
Alex
Feb 12 '08 #1
Share this Question
Share on Google+
1 Reply


P: 11
I've got the function working with 2 queries, the first checks for a postage rate for the entire range, the second checks for a postage rate for the specific product and overwrites the value if that is true. Could these queries be combined?

Hope this makes my question clearer :)

[PHP]
function getitemPostage($it) { // look up item, price, etc.

connect();
$postagePrice = 0.00;

// Find Postage For Product Range

$sql = "
SELECT product.productID, product.productRangeID, postagePrice, postageName FROM product
INNER JOIN ((shopPostage
INNER JOIN shopPostageCountry ON shopPostageCountry.postageID=shopPostage.postageID )
INNER JOIN shopPostageRange ON shopPostageRange.postageID=shopPostage.postageID)
ON product.productRangeID=shopPostageRange.rangeID
WHERE product.productID='$it' AND shopPostageCountry.countryID='".$_SESSION['orderDetails']['shipping_country']."'
LIMIT 0,1";

$result = mysql_query($sql);
if(mysql_num_rows($result)){
$product = mysql_fetch_array($result);
$postagePrice = $product['postagePrice'];
}

// Find Postage For Specific Item (Overwrite if exists)

$sql = "
SELECT product.productID, postagePrice, postageName FROM product
INNER JOIN ((shopPostage
INNER JOIN shopPostageCountry ON shopPostageCountry.postageID=shopPostage.postageID )
INNER JOIN shopPostageProduct ON shopPostageProduct.postageID=shopPostage.postageID )
ON product.productID=shopPostageProduct.productID
WHERE product.productID='$it' AND shopPostageCountry.countryID='".$_SESSION['orderDetails']['shipping_country']."'
LIMIT 0,1";

$result = mysql_query($sql);
if(mysql_num_rows($result)){
$product = mysql_fetch_array($result);
$postagePrice = $product['postagePrice'];
}

mysql_close();

return $postagePrice;

}
[/PHP]
Feb 13 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.