International Shipping Rules setup

Hi there, Im just about to add international shipping to an existing website I built. I just wanted clarification with the approach im taking plus what formulas I would use in the ecart rules.

Since I am closely matching Royal Mail costs here in the UK and I am am going to be create weight tiers that match their costs. Also to make things simplier for the Admin I am also following Royal Mails approach to how they organise costs throughout the world, they have divided the world as follows:

UK
Europe
World Zone 1 (North America, South America, Middle East, Far East and South East Asia)
World Zone 2 (eg. Australia, New Zealand and Fiji)

So the above would be great since rather than having to enter in costs for each individual country the admin needs to only enter in the above (UK, Europe, Zone1 and Zone2).

Also Europe, Zone1 and Zone 2 will just require Standard shipping for now...

However UK will require...Collect from shop (so no shipping cost), Special Delivery and Recorded Delivery).

I have created 3 database tables
shippingoptions
shippinglocations
shippingcosts

The fields are as follows:

shippingoptions ->
ShippingOptionsID
ShippingOptionsType

shippinglocations ->
ShippingLocationID
ShippingLocationCountries
ShippingLocationZones

shippingcosts ->
ShippingCostsID
ShippingCostsType
ShippingCostsZone
ShippingCostsWeightFrom
ShippingCostsWeightTo
ShippingCostsValue

From the above table structure would this all seem logical? or am i potentially missing something?

I just need to work on the formulas to enter in the ecart shipping rules.

so for example

if the customer enters UK in the select box, another menu will appear which will allow them to select the records stored in the shippingoptions (eg, collect from store, special delivery or recorded delivery) then from the selected shippingoption together with the weight of all items in the cart the session variable to set. otherwise is another country is selected then there is no need to show the shipping options menu since standard delivery will be that countries default.

and to calcaulate the cost of shipping for example for UK the cost of special delivery will be for all items in the cart that are in the weight range of 0g to 1000g. And I be using the value stored in the table shippingcosts under the field ShippingCostsValue.

Also in addition I am adding a coupon system that the client wants to potentially use for giving their customers free delivery with a special coupon number (but the coupon system questions i will put into another thread).

So fromw aht I have written, with regard to the table structure, does this seem to be a logical approach or am i potentially going to run itno problems that i havent considered?

Chris

the table structure looks like it would work.

you would just need to create a recordset to query the table based on the shipping country they entered, and the cart weight, then store the Shipping amount in a session variable and use that session in the shipping calculation.

Hi Jason, I am still a little stuck on the recordset query, so just need an example to work on if thats possible. also if I am creating this recordset query to load in the correct shipping value based on country zone, delivery type and total cart weight then what would I need to put in for the ecart shipping rules (since I wouldnt need to use the ecart shipping rules weights as this is already determined in the recordset query right?

so just to recap I need to know how to create a recordset query based on the CountryLocationZone, ShippingOptionsType & ecart Total cart weight.

Also in the table for shippinglocation I have added an extra field called "ShippingCode" that will pass the country code to paypal (eg GB, US, FR etc).

and currently on my checkout page I have used a simple recordset to load all values of shippinglocation table to show all counties for the customer to select and a second recordset to load all values of shipping options so that UK customers can select which delivery type they want. it would also be nice to only display shipping options if a customer selects UK fromt he dropdown menu.

But first would like to get the recordsets sorted first.

Ived included the checkout.php files * a copy of the shipping tables in the database for reference. it be great if you could let me know how to construct the recordset to query what i need from the 3 tables. plus what shipping rules i need to create in the ecart object?

Regards

Chris

your data base should be using linking tables, which you are not using


for example, you need a table to define zones:

zones:
zoneID
zoneName


then a table that defines the counties that belong in a zone:
zoneCountries:
zoneCountriesID
zoneCountriesZoneID
zoneCountriesCountry


and include a record for each country to assign it to a zone by the zone ID

then in the shipping cost table, bind the cost to the zone using the zone ID in the ShippingCostZone column


you can then use a joined query to join all the related tables together.

This sort of database setup and SQL Goes beyond what we can help you with in this forum, I suggest signing up for a premiere appointment so we can work with you on this.