Saturday, March 20, 2010

Inserting a list of countries into a database

It's pretty common to need a list of countries for a web application. Here's a method one could use to populate the 'country' table in a MySQL database. The only other tool necessary is a text editor with regular expression capabilities such as Notepad++.

First you create the 'country' table in your database. Below I have the 'name' column with 200 characters length, but if you want to save a few bytes you could trim that down quite a bit.

CREATE TABLE country (country_id int(10) unsigned NOT NULL AUTO_INCREMENT,
  name varchar(200) NOT NULL,
  PRIMARY KEY (country_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Next you find a list of countries. One such list is the ISO 3166-1 country list. According to the site this list is provided free for non-commercial use. The data in the text file version is formatted with each country name followed by a semicolon and then the ISO 3166-1-alpha-2 code for each country.

Since some countries have apostrophes in their names you need to do a search for them and replace each occurrence with \' to escape them.

In order to prepare the escaped data for insertion you need to do another replace, but this time using the search string ^(.+);[A-Z][A-Z]$ and the replace string INSERT INTO country (name) VALUES ('\1');. Take a look at the Notepad++ regular expression documentation for more details.

If you want to insert the country codes into another column you could use the search string ^(.+);([A-Z][A-Z])$ and the replace string INSERT INTO country (name, code) VALUES ('\1', '\2');

No comments:

Post a Comment