Help With Query Join Types For Shipping Rates

[ajcolburn]

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:

product (productID*, productName, productPrice, productRangeID)
shopPostage (postageID*, postagePrice, postageFriendlyName)
shopPostageProduct(ID*, postageID, productID)
shopPostageRange(ID*, postageID, rangeID)
shopPostageCountry(ID*, postageID, countryID)

Here is my attempt at the SQL for returning a product's postage, as you can see I haven't been able to include shopPostageCoun try (really confused!)

[CODE=SQL]
SELECT *
FROM ((product
RIGHT JOIN shopPostageRang e ON productRangeID= shopPostageRang e.rangeID)
RIGHT JOIN shopPostageProd uct ON product.product ID=shopPostageP roduct.productI D)
INNER JOIN shopPostage ON
shopPostageProd uct.postageID=s hopPostage.post ageID
OR
shopPostageRang e.postageID=sho pPostage.postag eID
WHERE product.product ID =13 AND shopPostageCoun try.countryID=2
[/CODE]

Any help with this would be greatly appreciated!
Kind Regards,
Alex

[ajcolburn]

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.product ID, product.product RangeID, postagePrice, postageName FROM product
INNER JOIN ((shopPostage
INNER JOIN shopPostageCoun try ON shopPostageCoun try.postageID=s hopPostage.post ageID)
INNER JOIN shopPostageRang e ON shopPostageRang e.postageID=sho pPostage.postag eID)
ON product.product RangeID=shopPos tageRange.range ID
WHERE product.product ID='$it' AND shopPostageCoun try.countryID=' ".$_SESSION['orderDetails']['shipping_count ry']."'
LIMIT 0,1";

$result = mysql_query($sq l);
if(mysql_num_ro ws($result)){
$product = mysql_fetch_arr ay($result);
$postagePrice = $product['postagePrice'];
}

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

$sql = "
SELECT product.product ID, postagePrice, postageName FROM product
INNER JOIN ((shopPostage
INNER JOIN shopPostageCoun try ON shopPostageCoun try.postageID=s hopPostage.post ageID)
INNER JOIN shopPostageProd uct ON shopPostageProd uct.postageID=s hopPostage.post ageID)
ON product.product ID=shopPostageP roduct.productI D
WHERE product.product ID='$it' AND shopPostageCoun try.countryID=' ".$_SESSION['orderDetails']['shipping_count ry']."'
LIMIT 0,1";

$result = mysql_query($sq l);
if(mysql_num_ro ws($result)){
$product = mysql_fetch_arr ay($result);
$postagePrice = $product['postagePrice'];
}

mysql_close();

return $postagePrice;

}
[/PHP]