Zones for USA help - Contiguous - Free shipping

***************Solved******************* Read 2nd reply below.
Trying to figure out if there is a fast way or an extension to create a Geo zone that has the 49 contiguous states (+DC) so that I can offer that Geo Zone free shipping. As for the other US regions, I will use weight based calculation. If not, this should be an easy extension for someone (I think). A way to create groups (geo zones) quickly and be able to subtract/add groups from each other. There should be a search option to bring up all regions with "united states" and then a selection box to select zones, a drop down menu to choose an existing geo zone or create a new one, and then a "add to Geo Zone" button.

Start here, http://forum.opencart.com/viewtopic.php?f=20&t=7099 . . .

First, I will say that Geo Zones should be called Zone Groups. Because that's what it is and it makes more sense to refer to it that way.
Next, the post by ambrosite is the key. http://forum.opencart.com/viewtopic.php ... 97#p142297

I have summarized his post below:

You can set up an complex Geo Zone in a half second by running a SQL query directly against the database. First of all, setup the Geo Zone in the admin as you normally would, but don't add any countries or zones to it. Next, you need to know the geo_zone_id of the Geo Zone you just created. You can find it at the end of the URL when you edit the Geo Zone:

http: //www.mystore/admin/index.php?route=local ... ne_id=[b]4[/b]

Now, run the following SQL query using a tool like phpMyAdmin. Make sure you edit the query with the table prefix you selected during the OpenCart installation ('oc_' for my database). You need to enter the correct geo_zone_id in the third field on the second line (it was '4' in my case):

Code: Select all

INSERT INTO oc_zone_to_geo_zone (country_id, zone_id, geo_zone_id, date_added, date_modified)
SELECT oc_country.country_id, 0, 4, NOW(), NOW()
FROM oc_country
WHERE oc_country.name NOT IN ('United States','Canada');

This code adds all the zones not in the US or Canada to Geo Zone created (id=4).

Now, we can tweak it a little to add all the zones that are in United States country_id = 223
Notice I'm now selecting from the oc_zone table. Now, all the states and regions for the US are in that Geo Zone. It is now easy to remove the few that I don't want in there.

Now, if we wanted to add only the Contiguous state + DC, here it is, added to Geo Zone ID = 10: What I had to do was grab the Zone IDs for each state +DC and put it in the WHERE and the colom "oc_zone.zone_id". Now to create a new Geo Zone of the other US states + territories to Geo Zone id=11
This is straight forward, even for people that are not familiar with SQL like myself. I just picked it up. If you are unsure, follow these steps:
1. log onto you service provider who is hosting your domain. I'm using http://www.1and1.com.
2. find where the databases are and select the database for opencart. Click the phpmyadmin button. This will launch the database in phpmyadmin. Click on the tab that says SQL at the top. In the box there, copy and paste the code above into it and hit the "Go" button. You should see a message at the top that says something like "Inserted rows: 25
Inserted row id: 708 (Query took 0.0020 sec)".
3. Now go back into your opencart admin panel and look at the Geo Zone. You should see all the added zones there. Note, you should create test Geo Zones first just to test it out.

Hope this helps.
Mark
http://www.Vorticy.com is the main shop with several other brand sites like http://www.cornhole2.com