Setting up Zone Definitions

[jabbah]

Hi Everyone,

just wanted to share a bit of SQL with anyone who wants to setup zones definitions...

I needed a zone that included the whole world except those countries already listed in other zone definitions. This is of course a pain in the ...

So I did this:

Find the geo_zone_id of the zone definition. In my case 10.

insert all countries into it:

INSERT INTO `zenc_zones_to_geo_zones` (zone_country_id,geo_zone_id,zone_id,date_added) SELECT countries_id,10,0,now() FROM zenc_countries

Now remove all the countries that are already in use:

delete from `zenc_zones_to_geo_zones` t1,`zenc_zones_to_geo_zones` t2
where t1.geo_zone_id=10
and t1.zone_country_id = t2.zone_country_id
and t2.geo_zone_id!=10

And you are done!

Quite easy really, but it took me some tinkering before getting it right.

And oh yeah, don't forget to backup your tables before doing this!!!

[tvsupport]

This was just what I was looking for, however there was a small amend needed in the sql to actually delete the duplicate rows , as below in bold;

delete t1 from `zen_zones_to_geo_zones` t1, `zen_zones_to_geo_zones` t2

Many thanks Jabbah

[jabbah]

You are welcome!

I am happy it was usefull to someone.

[yoppo]

Could you explain how to do this? Is it via phpmyadmin?

Thanks a million!

[jabbah]

Yes this can be done with phpMyAdmin, but I think if you are asking this question this code is not for you (no offence). You must be sure about what you are doing to use his.
If you want to give it a go, MAKE A BACKUP OF YOUR DATABASE FIRST!

Good luck!