How can I Remove Counties from Drop Down List?

[limelites]

This is the SQL patch I've used in an attempt to bring the UK counties up to date. I'm still missing some but I've made the mistake of adding DERRY and LONDONDERRY which are just different names for the same county. What would be the SQL patch to remove a particular coutny?

DROP TABLE IF EXISTS zones;
CREATE TABLE zones (
  zone_id int(11) NOT NULL auto_increment,
  zone_country_id int(11) NOT NULL default '0',
  zone_code varchar(32) NOT NULL default '',
  zone_name varchar(32) NOT NULL default '',
  PRIMARY KEY  (zone_id),
  KEY idx_zone_country_id_zen (zone_country_id),
  KEY idx_zone_code_zen (zone_code)
) TYPE=MyISAM;
INSERT INTO zones VALUES (NULL, 222, 'Aberdeenshire', 'Aberdeenshire');
INSERT INTO zones VALUES (NULL, 222, 'Anglesey', 'Anglesey');
INSERT INTO zones VALUES (NULL, 222, 'Angus', 'Angus');
INSERT INTO zones VALUES (NULL, 222, 'Argyll', 'Argyll');
INSERT INTO zones VALUES (NULL, 222, 'Avon', 'Avon');
INSERT INTO zones VALUES (NULL, 222, 'Ayrshire', 'Ayrshire');
INSERT INTO zones VALUES (NULL, 222, 'Banffshire', 'Banffshire');
INSERT INTO zones VALUES (NULL, 222, 'Bedfordshire', 'Bedfordshire');
INSERT INTO zones VALUES (NULL, 222, 'Berkshire', 'Berkshire');
INSERT INTO zones VALUES (NULL, 222, 'Berwickshire', 'Berwickshire');
INSERT INTO zones VALUES (NULL, 222, 'Borders', 'Borders');
INSERT INTO zones VALUES (NULL, 222, 'Breconshire', 'Breconshire');
INSERT INTO zones VALUES (NULL, 222, 'Buckinghamshire', 'Buckinghamshire');
INSERT INTO zones VALUES (NULL, 222, 'Bute', 'Bute');
INSERT INTO zones VALUES (NULL, 222, 'Caernarvonshire', 'Caernarvonshire');
INSERT INTO zones VALUES (NULL, 222, 'Caithness', 'Caithness');
INSERT INTO zones VALUES (NULL, 222, 'Cardiganshire', 'Cardiganshire');
INSERT INTO zones VALUES (NULL, 222, 'Carmarthenshire', 'Carmarthenshire');
INSERT INTO zones VALUES (NULL, 222, 'Cambridgeshire', 'Cambridgeshire');
INSERT INTO zones VALUES (NULL, 222, 'Central', 'Central');
INSERT INTO zones VALUES (NULL, 222, 'Cheshire', 'Cheshire');
INSERT INTO zones VALUES (NULL, 222, 'Clackmannanshire', 'Clackmannanshire');
INSERT INTO zones VALUES (NULL, 222, 'Cleveland', 'Cleveland');
INSERT INTO zones VALUES (NULL, 222, 'Clwyd', 'Clwyd');
INSERT INTO zones VALUES (NULL, 222, 'Conwy', 'Conwy');
INSERT INTO zones VALUES (NULL, 222, 'Cornwall', 'Cornwall');
INSERT INTO zones VALUES (NULL, 222, 'County Antrim', 'County Antrim');
INSERT INTO zones VALUES (NULL, 222, 'County Armagh', 'County Armagh');
INSERT INTO zones VALUES (NULL, 222, 'County Down', 'County Down');
INSERT INTO zones VALUES (NULL, 222, 'County Fermanagh', 'County Fermanagh');
INSERT INTO zones VALUES (NULL, 222, 'County Tyrone', 'County Tyrone');
INSERT INTO zones VALUES (NULL, 222, 'Cumbria', 'Cumbria');
INSERT INTO zones VALUES (NULL, 222, 'Denbighshire', 'Denbighshire');
INSERT INTO zones VALUES (NULL, 222, 'Derbyshire', 'Derbyshire');
INSERT INTO zones VALUES (NULL, 222, 'Derry', 'Derry');
INSERT INTO zones VALUES (NULL, 222, 'Devon', 'Devon');
INSERT INTO zones VALUES (NULL, 222, 'Dorset', 'Dorset');
INSERT INTO zones VALUES (NULL, 222, 'Dumfries and Galloway', 'Dumfries and Galloway');
INSERT INTO zones VALUES (NULL, 222, 'Dumfriesshire', 'Dumfriesshire');
INSERT INTO zones VALUES (NULL, 222, 'Durham', 'Durham');
INSERT INTO zones VALUES (NULL, 222, 'Dyfed', 'Dyfed');
INSERT INTO zones VALUES (NULL, 222, 'East Lothian', 'East Lothian');
INSERT INTO zones VALUES (NULL, 222, 'East Sussex', 'East Sussex');
INSERT INTO zones VALUES (NULL, 222, 'Essex', 'Essex');
INSERT INTO zones VALUES (NULL, 222, 'Fife', 'Fife');
INSERT INTO zones VALUES (NULL, 222, 'Flintshire', 'Flintshire');
INSERT INTO zones VALUES (NULL, 222, 'Glamorgan', 'Glamorgan');
INSERT INTO zones VALUES (NULL, 222, 'Gloucestershire', 'Gloucestershire');
INSERT INTO zones VALUES (NULL, 222, 'Grampian', 'Grampian');
INSERT INTO zones VALUES (NULL, 222, 'Greater London', 'Greater London');
INSERT INTO zones VALUES (NULL, 222, 'Greater Manchester', 'Greater Manchester');
INSERT INTO zones VALUES (NULL, 222, 'Guernsey', 'Guernsey');
INSERT INTO zones VALUES (NULL, 222, 'Gwent', 'Gwent');
INSERT INTO zones VALUES (NULL, 222, 'Gwynedd County', 'Gwynedd County');
INSERT INTO zones VALUES (NULL, 222, 'Hampshire', 'Hampshire');
INSERT INTO zones VALUES (NULL, 222, 'Herefordshire', 'Herefordshire');
INSERT INTO zones VALUES (NULL, 222, 'Hertfordshire', 'Hertfordshire');
INSERT INTO zones VALUES (NULL, 222, 'Highlands and Islands', 'Highlands and Islands');
INSERT INTO zones VALUES (NULL, 222, 'Humberside', 'Humberside');
INSERT INTO zones VALUES (NULL, 222, 'Inverness', 'Inverness');
INSERT INTO zones VALUES (NULL, 222, 'Isle of Man', 'Isle of Man');
INSERT INTO zones VALUES (NULL, 222, 'Isle of Wight', 'Isle of Wight');
INSERT INTO zones VALUES (NULL, 222, 'Jersey', 'Jersey');
INSERT INTO zones VALUES (NULL, 222, 'Kent', 'Kent');
INSERT INTO zones VALUES (NULL, 222, 'Kincardineshire', 'Kincardineshire');
INSERT INTO zones VALUES (NULL, 222, 'Kinross-shire', 'Kinross-shire');
INSERT INTO zones VALUES (NULL, 222, 'Kirkcudbrightshire', 'Kirkcudbrightshire');
INSERT INTO zones VALUES (NULL, 222, 'Lanarkshire', 'Lanarkshire');
INSERT INTO zones VALUES (NULL, 222, 'Lancashire', 'Lancashire');
INSERT INTO zones VALUES (NULL, 222, 'Leicestershire', 'Leicestershire');
INSERT INTO zones VALUES (NULL, 222, 'Lincolnshire', 'Lincolnshire');
INSERT INTO zones VALUES (NULL, 222, 'London', 'London');
INSERT INTO zones VALUES (NULL, 222, 'Londonderry', 'Londonderry');
INSERT INTO zones VALUES (NULL, 222, 'Lothians', 'Lothians');
INSERT INTO zones VALUES (NULL, 222, 'Midlothian', 'Midlothian');
INSERT INTO zones VALUES (NULL, 222, 'Monmouthshire', 'Monmouthshire');
INSERT INTO zones VALUES (NULL, 222, 'Montgomeryshire', 'Montgomeryshire');
INSERT INTO zones VALUES (NULL, 222, 'Merionethshire', 'Merionethshire');
INSERT INTO zones VALUES (NULL, 222, 'Merseyside', 'Merseyside');
INSERT INTO zones VALUES (NULL, 222, 'Mid Glamorgan', 'Mid Glamorgan');
INSERT INTO zones VALUES (NULL, 222, 'Moray', 'Moray');
INSERT INTO zones VALUES (NULL, 222, 'Nairnshire', 'Nairnshire');
INSERT INTO zones VALUES (NULL, 222, 'Norfolk', 'Norfolk');
INSERT INTO zones VALUES (NULL, 222, 'North Yorkshire', 'North Yorkshire');
INSERT INTO zones VALUES (NULL, 222, 'Northamptonshire', 'Northamptonshire');
INSERT INTO zones VALUES (NULL, 222, 'Northumberland', 'Northumberland');
INSERT INTO zones VALUES (NULL, 222, 'Nottinghamshire', 'Nottinghamshire');
INSERT INTO zones VALUES (NULL, 222, 'Orkney', 'Orkney');
INSERT INTO zones VALUES (NULL, 222, 'Oxfordshire', 'Oxfordshire');
INSERT INTO zones VALUES (NULL, 222, 'Peebleshire', 'Peebleshire');
INSERT INTO zones VALUES (NULL, 222, 'Perthshire', 'Perthshire');
INSERT INTO zones VALUES (NULL, 222, 'Powys', 'Powys');
INSERT INTO zones VALUES (NULL, 222, 'Pembrokeshire', 'Pembrokeshire');
INSERT INTO zones VALUES (NULL, 222, 'Radnorshire', 'Radnorshire');
INSERT INTO zones VALUES (NULL, 222, 'Renfrewshire', 'Renfrewshire');
INSERT INTO zones VALUES (NULL, 222, 'Ross & Cromarty', 'Ross & Cromarty');
INSERT INTO zones VALUES (NULL, 222, 'Roxburghshire', 'Roxburghshire');
INSERT INTO zones VALUES (NULL, 222, 'Rutland', 'Rutland');
INSERT INTO zones VALUES (NULL, 222, 'Selkirkshire', 'Selkirkshire');
INSERT INTO zones VALUES (NULL, 222, 'Shetland', 'Shetland');
INSERT INTO zones VALUES (NULL, 222, 'Shropshire', 'Shropshire');
INSERT INTO zones VALUES (NULL, 222, 'Somerset', 'Somerset');
INSERT INTO zones VALUES (NULL, 222, 'South Glamorgan', 'South Glamorgan');
INSERT INTO zones VALUES (NULL, 222, 'South Yorkshire', 'South Yorkshire');
INSERT INTO zones VALUES (NULL, 222, 'Staffordshire', 'Staffordshire');
INSERT INTO zones VALUES (NULL, 222, 'Strathclyde', 'Strathclyde');
INSERT INTO zones VALUES (NULL, 222, 'Stirlingshire', 'Stirlingshire');
INSERT INTO zones VALUES (NULL, 222, 'Sutherland', 'Sutherland');
INSERT INTO zones VALUES (NULL, 222, 'Suffolk', 'Suffolk');
INSERT INTO zones VALUES (NULL, 222, 'Surrey', 'Surrey');
INSERT INTO zones VALUES (NULL, 222, 'Tayside', 'Tayside');
INSERT INTO zones VALUES (NULL, 222, 'Tyne and Wear', 'Tyne and Wear');
INSERT INTO zones VALUES (NULL, 222, 'Warwickshire', 'Warwickshire');
INSERT INTO zones VALUES (NULL, 222, 'West Dumbartonshire', 'East Dumbartonshire');
INSERT INTO zones VALUES (NULL, 222, 'West Glamorgan', 'West Glamorgan');
INSERT INTO zones VALUES (NULL, 222, 'West Lothian', 'West Lothian');
INSERT INTO zones VALUES (NULL, 222, 'West Midlands', 'West Midlands');
INSERT INTO zones VALUES (NULL, 222, 'West Sussex', 'West Sussex');
INSERT INTO zones VALUES (NULL, 222, 'West Yorkshire', 'West Yorkshire');
INSERT INTO zones VALUES (NULL, 222, 'Wigtownshire', 'Wigtownshire');
INSERT INTO zones VALUES (NULL, 222, 'Wiltshire', 'Wiltshire');
INSERT INTO zones VALUES (NULL, 222, 'Worcestershire', 'Worcestershire'); 

Better still, doesn anyone have a link to an SQL patch that would give a county drop down that's seperated by ENGLAND, SCOTLAND, IRELAND, WALES sub headings within the list, like the one eBay uses?

[limelites]

Basically, what would the SQL syntax be to remove this:

DROP TABLE IF EXISTS zones;
CREATE TABLE zones (
  zone_id int(11) NOT NULL auto_increment,
  zone_country_id int(11) NOT NULL default '0',
  zone_code varchar(32) NOT NULL default '',
  zone_name varchar(32) NOT NULL default '',
  PRIMARY KEY  (zone_id),
  KEY idx_zone_country_id_zen (zone_country_id),
  KEY idx_zone_code_zen (zone_code)
) TYPE=MyISAM;
INSERT INTO zones VALUES (NULL, 222, 'Aberdeenshire', 'Aberdeenshire'); 

Anyone?

[DrByte]

You are in dangerous territory here.

Dropping and rebuilding the table with different data all the time is fraught with cascading side effects, including potentially rendering all your existing customers' and orders' address data invalid or pointing to different places. Do you really want to run the risk of having customers who live in London suddenly having their addresses point them to living in Liverpool?

The zone_id assigned when loading those records is used relationally by other tables which use the data. So ... quite simply ... DO NOT blindly drop and rebuild tables with different data than they originally contained.

[limelites]

Good advice, I didn't think of that. Is it do-able though? If so then I'll just post a job on the freelancer site I use as there are a couple of good ZC pro's on there who've been very helpful in the past.

[DrByte]

So, it's "good advice" to not do it but you intend to "do it anyway"?

[limelites]

Well, not exactly. I'd get a pro to do it rather than doing it myself. Surely a pro could do it?

[DrByte]

Basically, what would the SQL syntax be to remove this:

DROP TABLE IF EXISTS zones;
CREATE TABLE zones (
  zone_id int(11) NOT NULL auto_increment,
  zone_country_id int(11) NOT NULL default '0',
  zone_code varchar(32) NOT NULL default '',
  zone_name varchar(32) NOT NULL default '',
  PRIMARY KEY  (zone_id),
  KEY idx_zone_country_id_zen (zone_country_id),
  KEY idx_zone_code_zen (zone_code)
) TYPE=MyISAM;
INSERT INTO zones VALUES (NULL, 222, 'Aberdeenshire', 'Aberdeenshire'); 

Anyone?

No SQL required. Just go to Admin->Locations/Taxes->Zones, find the one you want to delete, and delete it.

[limelites]

Seems over complex. I'll leave it as it. Thanks for the admin link, never used that before but very uesful for deleting obsolete counties and even adding missing ones.