UK County Selection

Virtuemart very naively places England, Ireland, Scotland and Wales as counties under Great Britain, now us who live here know that to be wrong and so this hack enable consumers in the UK to select their country and then select their county.

The modifications below flow throughout Virtuemart without interfering with any code, so all of you orders, emails and confirmations so the correct addresses with full county name.

i.e.
Cust Name
123 The Road
Line 2
Their Town
County
Country
Postcode

Database alteration

1) Need to make some alterations to the database (enlarging fields, saves messing with the core Virtuemart code)

• alter table jos_vm_state change state_2_code state_2_code VARCHAR(64);
• alter table jos_vm_country change country_2_code country_2_code varchar(64);
• alter table jos_vm_country change country_3_code country_3_code varchar(64);

2) Now insert the country names:

INSERT INTO jos_vm_country (country_name, country_2_code, country_3_code) VALUES
('England', 'England', 'England'),
('Scotland', 'Scotland', 'Scotland'),
('Wales', 'Wales', 'Wales'),
('Northern Ireland', 'Northern Ireland', 'Northern Ireland');

3) Check the insert worked ok and use the data to modify provided queries if needed

SELECT * FROM jos_vm_country WHERE country_name = 'England';
SELECT * FROM jos_vm_country WHERE country_name = 'Scotland';
SELECT * FROM jos_vm_country WHERE country_name = 'Wales';
SELECT * FROM jos_vm_country WHERE country_name = 'Northern Ireland';

Make sure the values above return 240, 241, 242 & 243 respectively, if not then you will need to change the numbers below.

County Inserts

Note: Stolen from the Paypal payment page

INSERT INTO jos_vm_state (country_id, state_name) VALUES

(240, 'Avon'),
(240, 'Bedfordshire'),
(240, 'Berkshire'),
(240, 'Bristol'),
(240, 'Buckinghamshire'),
(240, 'Cambridgeshire'),
(240, 'Cheshire'),
(240, 'Cleveland'),
(240, 'Cornwall'),
(240, 'Cumbria'),
(240, 'Derbyshire'),
(240, 'Devon'),
(240, 'Dorset'),
(240, 'Durham'),
(240, 'East Riding of Yorkshire'),
(240, 'East Sussex'),
(240, 'Essex'),
(240, 'Gloucestershire'),
(240, 'Greater Manchester'),
(240, 'Hampshire'),
(240, 'Herefordshire'),
(240, 'Hertfordshire'),
(240, 'Humberside'),
(240, 'Isle of Wight'),
(240, 'Isles of Scilly'),
(240, 'Kent'),
(240, 'Lancashire'),
(240, 'Leicestershire'),
(240, 'Lincolnshire'),
(240, 'London'),
(240, 'Merseyside'),
(240, 'Middlesex'),
(240, 'Norfolk'),
(240, 'North Yorkshire'),
(240, 'Northamptonshire'),
(240, 'Northumberland'),
(240, 'Nottinghamshire'),
(240, 'Oxfordshire'),
(240, 'Rutland'),
(240, 'Shropshire'),
(240, 'Somerset'),
(240, 'South Yorkshire'),
(240, 'Staffordshire'),
(240, 'Suffolk'),
(240, 'Surrey'),
(240, 'Tyne and Wear'),
(240, 'Warwickshire'),
(240, 'West Midlands'),
(240, 'West Sussex'),
(240, 'West Yorkshire'),
(240, 'Wiltshire'),
(240, 'Worcestershire');

INSERT INTO jos_vm_state (country_id, state_name) VALUES

(241, 'Aberdeen City'),
(241, 'Aberdeenshire'),
(241, 'Angus'),
(241, 'Argyll and Bute'),
(241, 'Borders'),
(241, 'Clackmannan'),
(241, 'Dumfries and Galloway'),
(241, 'East Ayrshire'),
(241, 'East Dunbartonshire'),
(241, 'East Lothian'),
(241, 'East Renfrewshire'),
(241, 'Edinburgh City'),
(241, 'Falkirk'),
(241, 'Fife'),
(241, 'Glasgow (City of)'),
(241, 'Highland'),
(241, 'Inverclyde'),
(241, 'Midlothian'),
(241, 'Moray'),
(241, 'North Ayrshire'),
(241, 'North Lanarkshire'),
(241, 'Orkney'),
(241, 'Perthshire and Kinross'),
(241, 'Renfrewshire'),
(241, 'Roxburghshire'),
(241, 'Shetland'),
(241, 'South Ayrshire'),
(241, 'South Lanarkshire'),
(241, 'Stirling'),
(241, 'West Dunbartonshire'),
(241, 'West Lothian'),
(241, 'Western Isles');


INSERT INTO jos_vm_state (country_id, state_name) VALUES

(242, 'Blaenau Gwent'),
(242, 'Bridgend'),
(242, 'Caerphilly'),
(242, 'Cardiff'),
(242, 'Carmarthenshire'),
(242, 'Ceredigion'),
(242, 'Conwy'),
(242, 'Denbighshire'),
(242, 'Flintshire'),
(242, 'Gwynedd'),
(242, 'Isle of Anglesey'),
(242, 'Merthyr Tydfil'),
(242, 'Monmouthshire'),
(242, 'Neath Port Talbot'),
(242, 'Newport'),
(242, 'Pembrokeshire'),
(242, 'Powys'),
(242, 'Rhondda Cynon Taff'),
(242, 'Swansea'),
(242, 'Torfaen'),
(242, 'The Vale of Glamorgan'),
(242, 'Wrexham');

INSERT INTO jos_vm_state (country_id, state_name) VALUES
(243, 'Antrim'),
(243, 'Armagh'),
(243, 'Down'),
(243, 'Fermanagh'),
(243, 'Londonderry'),
(243, 'Tyrone');