Canadian Provinces in MySQL Table
I posted a list of U.S. State Abbreviations up here a while ago, then found myself needing the Canadian provinces as well. Here's the MySQL code to easily add them to that table of abbreviations and names. Hope it saves you some time!
1. CREATE TABLE IF NOT EXISTS `geo_states` ( 2. `name` varchar(50) NOT NULL DEFAULT '', 3. `abv` char(2) NOT NULL DEFAULT '', 4. `country` char(2) NOT NULL, 5. `latitude` decimal(9,6) DEFAULT NULL, 6. `longitude` decimal(9,6) DEFAULT NULL, 7. `slug` varchar(50) NOT NULL, 8. ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 9. 10. INSERT INTO `geo_states` (`name`, `abv`, `country`, `slug`) VALUES 11. ('Alberta','AB','CA','alberta'); 12. ('British Columbia','BC','CA','british-columbia'); 13. ('Manitoba','MB','CA','manitoba'); 14. ('New Brunswick','NB','CA','new-brunswick'); 15. ('Newfoundland and Labrador','NL','CA','newfoundland-and-labrador'); 16. ('Northwest Territories','NT','CA','northwest-territories'); 17. ('Nova Scotia','NS','CA','nova-scotia'); 18. ('Nunavut','NU','CA','nunavut'); 19. ('Ontario','ON','CA','ontario'); 20. ('Prince Edward Island','PE','CA','prince-edward-island'); 21. ('Quebec','QC','CA','quebec'); 22. ('Saskatchewan','SK','CA','saskatchewan'); 23. ('Yukon','YT','CA','yukon'); 24. 25. ALTER TABLE `geo_states` ADD PRIMARY KEY (`abv`,`country`);
This data can also be found in our combined countries, states, provinces, and oceans MySQL dump. There is also a collection of Countries MySQL data posted here on the blog.