International Shipping

[Meshach]

You are making a zone that needs to have the same data as the tables
zones_to_geo_zones
geo_zones

Tables would have ...

For All Countries except the US you could use this to run through the Admin ... Tools ... Install SQL Patch ...

INSERT INTO `geo_zones` (`geo_zone_id`, `geo_zone_name`, `geo_zone_description`, `last_modified`, `date_added`) VALUES (20, 'Not US', 'All Countries Except the US', NULL, '2006-07-03 12:32:13');

INSERT INTO `zones_to_geo_zones` (`zone_country_id`, `zone_id`, `geo_zone_id`) VALUES (1, 0, 20),
(2, 0, 20),
(3, 0, 20),
(4, 0, 20),
(5, 0, 20),
(6, 0, 20),
(7, 0, 20),
(8, 0, 20),
(9, 0, 20),
(10, 0, 20),
(11, 0, 20),
(12, 0, 20),
(13, 0, 20),
(14, 0, 20),
(15, 0, 20),
(16, 0, 20),
(17, 0, 20),
(18, 0, 20),
(19, 0, 20),
(20, 0, 20),
(21, 0, 20),
(22, 0, 20),
(23, 0, 20),
(24, 0, 20),
(25, 0, 20),
(26, 0, 20),
(27, 0, 20),
(28, 0, 20),
(29, 0, 20),
(30, 0, 20),
(31, 0, 20),
(32, 0, 20),
(33, 0, 20),
(34, 0, 20),
(35, 0, 20),
(36, 0, 20),
(37, 0, 20),
(38, 0, 20),
(39, 0, 20),
(40, 0, 20),
(41, 0, 20),
(42, 0, 20),
(43, 0, 20),
(44, 0, 20),
(45, 0, 20),
(46, 0, 20),
(47, 0, 20),
(48, 0, 20),
(49, 0, 20),
(50, 0, 20),
(51, 0, 20),
(52, 0, 20),
(53, 0, 20),
(54, 0, 20),
(55, 0, 20),
(56, 0, 20),
(57, 0, 20),
(58, 0, 20),
(59, 0, 20),
(60, 0, 20),
(61, 0, 20),
(62, 0, 20),
(63, 0, 20),
(64, 0, 20),
(65, 0, 20),
(66, 0, 20),
(67, 0, 20),
(68, 0, 20),
(69, 0, 20),
(70, 0, 20),
(71, 0, 20),
(72, 0, 20),
(73, 0, 20),
(74, 0, 20),
(75, 0, 20),
(76, 0, 20),
(77, 0, 20),
(78, 0, 20),
(79, 0, 20),
(80, 0, 20),
(81, 0, 20),
(82, 0, 20),
(83, 0, 20),
(84, 0, 20),
(85, 0, 20),
(86, 0, 20),
(87, 0, 20),
(88, 0, 20),
(89, 0, 20),
(90, 0, 20),
(91, 0, 20),
(92, 0, 20),
(93, 0, 20),
(94, 0, 20),
(95, 0, 20),
(96, 0, 20),
(97, 0, 20),
(98, 0, 20),
(99, 0, 20),
(100, 0, 20),
(101, 0, 20),
(102, 0, 20),
(103, 0, 20),
(104, 0, 20),
(105, 0, 20),
(106, 0, 20),
(107, 0, 20),
(108, 0, 20),
(109, 0, 20),
(110, 0, 20),
(111, 0, 20),
(112, 0, 20),
(113, 0, 20),
(114, 0, 20),
(115, 0, 20),
(116, 0, 20),
(117, 0, 20),
(118, 0, 20),
(119, 0, 20),
(120, 0, 20),
(121, 0, 20),
(122, 0, 20),
(123, 0, 20),
(124, 0, 20),
(125, 0, 20),
(126, 0, 20),
(127, 0, 20),
(128, 0, 20),
(129, 0, 20),
(130, 0, 20),
(131, 0, 20),
(132, 0, 20),
(133, 0, 20),
(134, 0, 20),
(135, 0, 20),
(136, 0, 20),
(137, 0, 20),
(138, 0, 20),
(139, 0, 20),
(140, 0, 20),
(141, 0, 20),
(142, 0, 20),
(143, 0, 20),
(144, 0, 20),
(145, 0, 20),
(146, 0, 20),
(147, 0, 20),
(148, 0, 20),
(149, 0, 20),
(150, 0, 20),
(151, 0, 20),
(152, 0, 20),
(153, 0, 20),
(154, 0, 20),
(155, 0, 20),
(156, 0, 20),
(157, 0, 20),
(158, 0, 20),
(159, 0, 20),
(160, 0, 20),
(161, 0, 20),
(162, 0, 20),
(163, 0, 20),
(164, 0, 20),
(165, 0, 20),
(166, 0, 20),
(167, 0, 20),
(168, 0, 20),
(169, 0, 20),
(170, 0, 20),
(171, 0, 20),
(172, 0, 20),
(173, 0, 20),
(174, 0, 20),
(175, 0, 20),
(176, 0, 20),
(177, 0, 20),
(178, 0, 20),
(179, 0, 20),
(180, 0, 20),
(181, 0, 20),
(182, 0, 20),
(183, 0, 20),
(184, 0, 20),
(185, 0, 20),
(186, 0, 20),
(187, 0, 20),
(188, 0, 20),
(189, 0, 20),
(190, 0, 20),
(191, 0, 20),
(192, 0, 20),
(193, 0, 20),
(194, 0, 20),
(195, 0, 20),
(196, 0, 20),
(197, 0, 20),
(198, 0, 20),
(199, 0, 20),
(200, 0, 20),
(201, 0, 20),
(202, 0, 20),
(203, 0, 20),
(204, 0, 20),
(205, 0, 20),
(206, 0, 20),
(207, 0, 20),
(208, 0, 20),
(209, 0, 20),
(210, 0, 20),
(211, 0, 20),
(212, 0, 20),
(213, 0, 20),
(214, 0, 20),
(215, 0, 20),
(216, 0, 20),
(217, 0, 20),
(218, 0, 20),
(219, 0, 20),
(220, 0, 20),
(221, 0, 20),
(222, 0, 20),
(224, 0, 20),
(225, 0, 20),
(226, 0, 20),
(227, 0, 20),
(228, 0, 20),
(229, 0, 20),
(230, 0, 20),
(231, 0, 20),
(232, 0, 20),
(233, 0, 20),
(234, 0, 20),
(235, 0, 20),
(236, 0, 20),
(237, 0, 20),
(238, 0, 20),
(239, 0, 20),
(240, 0, 20); 

search helper: premade World Zone or Rest of the World except for the US

Hello Linda,

Would you be able to post a modified version of this SQL code for rest of the world except US and Canada?

Thanks,
Meshach

[Ajeh]

Just use that one and remove Canada ...

Note: if you already have geo_zone_id 20 used, then you need to increment that to something else ...

[Meshach]

Thanks for the quick response Linda... But how do I remove Canada?

Sorry, I have little experience with SQL.

[Ajeh]

If you were to load the SQL code into your database using the Tools ... Install SQL Patches ...

you would then go to the Locations / Taxes ... Zones Definitions ... and click on Not US (All Countries Except US) and click on Details ...

Next, you would find Canada on the List ...

Remove Canada from that new Zone Definition and it will be all countries except for the US and Canada ...

[Meshach]

Ohhh.. I misunderstood how this works.

It works perfectly, thanks!

[Secret Weapon]

Hello Linda,

Would you be able to post a modified version of this SQL code for rest of the world except US and Canada?

Thanks,
Meshach

Another method to accomplish this would be to use the following code.

INSERT INTO `geo_zones` (`geo_zone_id`, `geo_zone_name`, `geo_zone_description`, `last_modified`, `date_added`)
VALUES (20, 'International', 'All Countries Except USA and Canada', NULL, CURDATE());

INSERT INTO `zones_to_geo_zones` (`zone_country_id`, `zone_id`, `geo_zone_id`)
SELECT `countries_id` , 0, 20
FROM `countries`
WHERE countries_iso_code_3 NOT IN ('USA','CAN');

The first part creates your 'International' zone as zone 20.

The second part pulls every country from the countries table, except those with ISO 3 code of 'USA' or 'CAN' and inserts it into the zone description for zone 20, which you created in the first statement.

You can modify this code to exclude any country you like by looking up the ISO3 code from your countries list under the 'Locations / Taxes' tab in admin and using it where USA and CAN are listed.

For instance, if you wanted to have USA, Canada, and Mexico excluded, that last part would be ... NOT IN ('USA','CAN','MEX');
If you wanted all countries except England, you would use ... NOT IN ('GBR');

I hope that helps and doesn't just make it more confusing.

[shawnchr]

I running Zencart version 1.3.8a and I have my lower 48 zone setup and using free shipping for over $200. For order under 200 using UPS.

Now I need everything else in a zone that uses USPS shipping.
including overseas Canada and non lower 48 in the us zone.

I have tried this:

INSERT INTO `geo_zones` (`geo_zone_id`, `geo_zone_name`, `geo_zone_description`, `last_modified`, `date_added`) VALUES (23, 'USPS Shipping', 'USPS Shipping not free', NULL, '2006-07-03 12:32:13');

INSERT INTO `zones_to_geo_zones` (`zone_country_id`, `zone_id`, `geo_zone_id`) VALUES 
(1, 0, 23),
(2, 0, 23),
(3, 0, 23),
(4, 0, 23),
(5, 0, 23),
(6, 0, 23),
(7, 0, 23),
(8, 0, 23),
(9, 0, 23),
(10, 0, 23),
(11, 0, 23),
(12, 0, 23),
(13, 0, 23),
(14, 0, 23),
(15, 0, 23),
(16, 0, 23),
(17, 0, 23),
(18, 0, 23),
(19, 0, 23),
(20, 0, 23),
(21, 0, 23),
(22, 0, 23),
(23, 0, 23),
(24, 0, 23),
(25, 0, 23),
(26, 0, 23),
(27, 0, 23),
(28, 0, 23),
(29, 0, 23),
(30, 0, 23),
(31, 0, 23),
(32, 0, 23),
(33, 0, 23),
(34, 0, 23),
(35, 0, 23),
(36, 0, 23),
(37, 0, 23),
(38, 0, 23),
(39, 0, 23),
(40, 0, 23),
(41, 0, 23),
(42, 0, 23),
(43, 0, 23),
(44, 0, 23),
(45, 0, 23),
(46, 0, 23),
(47, 0, 23),
(48, 0, 23),
(49, 0, 23),
(50, 0, 23),
(51, 0, 23),
(52, 0, 23),
(53, 0, 23),
(54, 0, 23),
(55, 0, 23),
(56, 0, 23),
(57, 0, 23),
(58, 0, 23),
(59, 0, 23),
(60, 0, 23),
(61, 0, 23),
(62, 0, 23),
(63, 0, 23),
(64, 0, 23),
(65, 0, 23),
(66, 0, 23),
(67, 0, 23),
(68, 0, 23),
(69, 0, 23),
(70, 0, 23),
(71, 0, 23),
(72, 0, 23),
(73, 0, 23),
(74, 0, 23),
(75, 0, 23),
(76, 0, 23),
(77, 0, 23),
(78, 0, 23),
(79, 0, 23),
(80, 0, 23),
(81, 0, 23),
(82, 0, 23),
(83, 0, 23),
(84, 0, 23),
(85, 0, 23),
(86, 0, 23),
(87, 0, 23),
(88, 0, 23),
(89, 0, 23),
(90, 0, 23),
(91, 0, 23),
(92, 0, 23),
(93, 0, 23),
(94, 0, 23),
(95, 0, 23),
(96, 0, 23),
(97, 0, 23),
(98, 0, 23),
(99, 0, 23),
(100, 0, 23),
(101, 0, 23),
(102, 0, 23),
(103, 0, 23),
(104, 0, 23),
(105, 0, 23),
(106, 0, 23),
(107, 0, 23),
(108, 0, 23),
(109, 0, 23),
(110, 0, 23),
(111, 0, 23),
(112, 0, 23),
(113, 0, 23),
(114, 0, 23),
(115, 0, 23),
(116, 0, 23),
(117, 0, 23),
(118, 0, 23),
(119, 0, 23),
(120, 0, 23),
(121, 0, 23),
(122, 0, 23),
(123, 0, 23),
(124, 0, 23),
(125, 0, 23),
(126, 0, 23),
(127, 0, 23),
(128, 0, 23),
(129, 0, 23),
(130, 0, 23),
(131, 0, 23),
(132, 0, 23),
(133, 0, 23),
(134, 0, 23),
(135, 0, 23),
(136, 0, 23),
(137, 0, 23),
(138, 0, 23),
(139, 0, 23),
(140, 0, 23),
(141, 0, 23),
(142, 0, 23),
(143, 0, 23),
(144, 0, 23),
(145, 0, 23),
(146, 0, 23),
(147, 0, 23),
(148, 0, 23),
(149, 0, 23),
(150, 0, 23),
(151, 0, 23),
(152, 0, 23),
(153, 0, 23),
(154, 0, 23),
(155, 0, 23),
(156, 0, 23),
(157, 0, 23),
(158, 0, 23),
(159, 0, 23),
(160, 0, 23),
(161, 0, 23),
(162, 0, 23),
(163, 0, 23),
(164, 0, 23),
(165, 0, 23),
(166, 0, 23),
(167, 0, 23),
(168, 0, 23),
(169, 0, 23),
(170, 0, 23),
(171, 0, 23),
(172, 0, 23),
(173, 0, 23),
(174, 0, 23),
(175, 0, 23),
(176, 0, 23),
(177, 0, 23),
(178, 0, 23),
(179, 0, 23),
(180, 0, 23),
(181, 0, 23),
(182, 0, 23),
(183, 0, 23),
(184, 0, 23),
(185, 0, 23),
(186, 0, 23),
(187, 0, 23),
(188, 0, 23),
(189, 0, 23),
(190, 0, 23),
(191, 0, 23),
(192, 0, 23),
(193, 0, 23),
(194, 0, 23),
(195, 0, 23),
(196, 0, 23),
(197, 0, 23),
(198, 0, 23),
(199, 0, 23),
(200, 0, 23),
(201, 0, 23),
(202, 0, 23),
(203, 0, 23),
(204, 0, 23),
(205, 0, 23),
(206, 0, 23),
(207, 0, 23),
(208, 0, 23),
(209, 0, 23),
(210, 0, 23),
(211, 0, 23),
(212, 0, 23),
(213, 0, 23),
(214, 0, 23),
(215, 0, 23),
(216, 0, 23),
(217, 0, 23),
(218, 0, 23),
(219, 0, 23),
(220, 0, 23),
(221, 0, 23),
(222, 0, 23),
(224, 0, 23),
(225, 0, 23),
(226, 0, 23),
(227, 0, 23),
(228, 0, 23),
(229, 0, 23),
(230, 0, 23),
(231, 0, 23),
(232, 0, 23),
(233, 0, 23),
(234, 0, 23),
(235, 0, 23),
(236, 0, 23),
(237, 0, 23),
(238, 0, 23),
(239, 0, 23),
(240, 0, 23),
(223, 0, 23),
(223, 0, 23),
(223, 0, 23),
(223, 0, 23),
(223, 0, 23),
(223, 0, 23),
(223, 0, 23),
(223, 0, 23),
(223, 0, 23),
(223, 0, 23),
(223, 0, 23),
(223, 0, 23),
(223, 0, 23),
(223, 0, 23),
(223, 0, 23);

I would also like to be able to tag say like a catagory named Trains Sets so they do not get free shipping. But can use either USPS or UPS shipping methods.

[Ajeh]

Look at that Zone Definition that you posted ... it looks like you have duplicates in there ...

This section is what caught my attention:

(223, 0, 23),
(223, 0, 23),
(223, 0, 23),
(223, 0, 23),
(223, 0, 23),
(223, 0, 23),
(223, 0, 23),
(223, 0, 23),
(223, 0, 23),
(223, 0, 23),
(223, 0, 23),
(223, 0, 23),
(223, 0, 23),
(223, 0, 23),
(223, 0, 23);

I install it and see the United States in it a dozen times ...

[shawnchr]

Ok I fixed the problem with them all saying united states. But when I set this zone for USPS Shipping and use the shipping estimater I get no listings for AK or HI or the other US regions I wish to not offer free shipping. I get the free shipping when I choose my lower 48. Why is one working and the other is not?

Here is what I have now:

INSERT INTO `geo_zones` (`geo_zone_id`, `geo_zone_name`, `geo_zone_description`, `last_modified`, `date_added`) VALUES (23, 'USPS Shipping', 'USPS Shipping not free', NULL, '2006-07-03 12:32:13');

INSERT INTO `zones_to_geo_zones` (`zone_country_id`, `zone_id`, `geo_zone_id`) VALUES 
(1, 0, 23),
(2, 0, 23),
(3, 0, 23),
(4, 0, 23),
(5, 0, 23),
(6, 0, 23),
(7, 0, 23),
(8, 0, 23),
(9, 0, 23),
(10, 0, 23),
(11, 0, 23),
(12, 0, 23),
(13, 0, 23),
(14, 0, 23),
(15, 0, 23),
(16, 0, 23),
(17, 0, 23),
(18, 0, 23),
(19, 0, 23),
(20, 0, 23),
(21, 0, 23),
(22, 0, 23),
(23, 0, 23),
(24, 0, 23),
(25, 0, 23),
(26, 0, 23),
(27, 0, 23),
(28, 0, 23),
(29, 0, 23),
(30, 0, 23),
(31, 0, 23),
(32, 0, 23),
(33, 0, 23),
(34, 0, 23),
(35, 0, 23),
(36, 0, 23),
(37, 0, 23),
(38, 0, 23),
(39, 0, 23),
(40, 0, 23),
(41, 0, 23),
(42, 0, 23),
(43, 0, 23),
(44, 0, 23),
(45, 0, 23),
(46, 0, 23),
(47, 0, 23),
(48, 0, 23),
(49, 0, 23),
(50, 0, 23),
(51, 0, 23),
(52, 0, 23),
(53, 0, 23),
(54, 0, 23),
(55, 0, 23),
(56, 0, 23),
(57, 0, 23),
(58, 0, 23),
(59, 0, 23),
(60, 0, 23),
(61, 0, 23),
(62, 0, 23),
(63, 0, 23),
(64, 0, 23),
(65, 0, 23),
(66, 0, 23),
(67, 0, 23),
(68, 0, 23),
(69, 0, 23),
(70, 0, 23),
(71, 0, 23),
(72, 0, 23),
(73, 0, 23),
(74, 0, 23),
(75, 0, 23),
(76, 0, 23),
(77, 0, 23),
(78, 0, 23),
(79, 0, 23),
(80, 0, 23),
(81, 0, 23),
(82, 0, 23),
(83, 0, 23),
(84, 0, 23),
(85, 0, 23),
(86, 0, 23),
(87, 0, 23),
(88, 0, 23),
(89, 0, 23),
(90, 0, 23),
(91, 0, 23),
(92, 0, 23),
(93, 0, 23),
(94, 0, 23),
(95, 0, 23),
(96, 0, 23),
(97, 0, 23),
(98, 0, 23),
(99, 0, 23),
(100, 0, 23),
(101, 0, 23),
(102, 0, 23),
(103, 0, 23),
(104, 0, 23),
(105, 0, 23),
(106, 0, 23),
(107, 0, 23),
(108, 0, 23),
(109, 0, 23),
(110, 0, 23),
(111, 0, 23),
(112, 0, 23),
(113, 0, 23),
(114, 0, 23),
(115, 0, 23),
(116, 0, 23),
(117, 0, 23),
(118, 0, 23),
(119, 0, 23),
(120, 0, 23),
(121, 0, 23),
(122, 0, 23),
(123, 0, 23),
(124, 0, 23),
(125, 0, 23),
(126, 0, 23),
(127, 0, 23),
(128, 0, 23),
(129, 0, 23),
(130, 0, 23),
(131, 0, 23),
(132, 0, 23),
(133, 0, 23),
(134, 0, 23),
(135, 0, 23),
(136, 0, 23),
(137, 0, 23),
(138, 0, 23),
(139, 0, 23),
(140, 0, 23),
(141, 0, 23),
(142, 0, 23),
(143, 0, 23),
(144, 0, 23),
(145, 0, 23),
(146, 0, 23),
(147, 0, 23),
(148, 0, 23),
(149, 0, 23),
(150, 0, 23),
(151, 0, 23),
(152, 0, 23),
(153, 0, 23),
(154, 0, 23),
(155, 0, 23),
(156, 0, 23),
(157, 0, 23),
(158, 0, 23),
(159, 0, 23),
(160, 0, 23),
(161, 0, 23),
(162, 0, 23),
(163, 0, 23),
(164, 0, 23),
(165, 0, 23),
(166, 0, 23),
(167, 0, 23),
(168, 0, 23),
(169, 0, 23),
(170, 0, 23),
(171, 0, 23),
(172, 0, 23),
(173, 0, 23),
(174, 0, 23),
(175, 0, 23),
(176, 0, 23),
(177, 0, 23),
(178, 0, 23),
(179, 0, 23),
(180, 0, 23),
(181, 0, 23),
(182, 0, 23),
(183, 0, 23),
(184, 0, 23),
(185, 0, 23),
(186, 0, 23),
(187, 0, 23),
(188, 0, 23),
(189, 0, 23),
(190, 0, 23),
(191, 0, 23),
(192, 0, 23),
(193, 0, 23),
(194, 0, 23),
(195, 0, 23),
(196, 0, 23),
(197, 0, 23),
(198, 0, 23),
(199, 0, 23),
(200, 0, 23),
(201, 0, 23),
(202, 0, 23),
(203, 0, 23),
(204, 0, 23),
(205, 0, 23),
(206, 0, 23),
(207, 0, 23),
(208, 0, 23),
(209, 0, 23),
(210, 0, 23),
(211, 0, 23),
(212, 0, 23),
(213, 0, 23),
(214, 0, 23),
(215, 0, 23),
(216, 0, 23),
(217, 0, 23),
(218, 0, 23),
(219, 0, 23),
(220, 0, 23),
(221, 0, 23),
(222, 0, 23),
(224, 0, 23),
(225, 0, 23),
(226, 0, 23),
(227, 0, 23),
(228, 0, 23),
(229, 0, 23),
(230, 0, 23),
(231, 0, 23),
(232, 0, 23),
(233, 0, 23),
(234, 0, 23),
(235, 0, 23),
(236, 0, 23),
(237, 0, 23),
(238, 0, 23),
(239, 0, 23),
(240, 0, 23),
(223, 6, 23),
(223, 7, 23),
(223, 8, 23),
(223, 9, 23),
(223, 10, 23),
(223, 11, 23),
(223, 2, 23),
(223, 17, 23),
(223, 20, 23),
(223, 21, 23),
(223, 30, 23),
(223, 46, 23),
(223, 52, 23),
(223, 3, 23),
(223, 60, 23);

[Ajeh]

I added that Zone you defined and with a Product with weight I get shipping for the Alaska and Hawaii in the shipping estimator without problem ...

What is the amount of your order that you are testing?

What method are you using to handle:

I running Zencart version 1.3.8a and I have my lower 48 zone setup and using free shipping for over $200.