Country Name and ISO 3166 Code MySQL Import File

Download iso_country_list.sql (18662 bytes, Last updated Tue Sep 16 21:42:49 2014)
Download usps_states_list.sql (4202 bytes, Last updated Tue Sep 16 21:42:49 2014)

Background:
I recently had a project at work which required a web-based user registration sort of feature. Because the project had an international flavor, I had to let people from every country register. And since we were going to need to generate reports broken down by country and eventually make mailing labels from the data stored in our MySQL database, I wanted the data to be pretty clean. Thus, I didn't want people to either have to or be able to type in their country of origin by hand. I wanted a select list they could choose from.

What I wound up doing was making a table which listed the names and ISO 3166 codes of all the countries of the world. I then referenced this table in my main user registration table as a foreign key. This would allow us to look at all the people that had registered from Europe, let's say, and then make graphs with the two-letter ISO codes on them for clarity. It should work out pretty well.

To make a long story short, I realized that this was the fourth time I had made such a table, and the fourth time I had done it from scratch (and it was the third time I had done it by writing a perl script which screen-scraped a list off the web). This is silly and a waste of time. So I decided to put my SQL file up here where I can get it later if I needed it. Since sharing is good, I made it public here so other people can get it if they find it useful (I didn't find much when I went googling for such a list...).

Updates:
11/14/06:
It's been brought to my attention that the SQL file shows Taiwan as a "Province of China", and that some people (the Taiwanese in particular) have some sensitivity towards the issue of Taiwan's sovereignty.

After some consideration, I've decided that it's not my place to second-guess a standards body and a world governing organization by editing information coming from the U.N. and ISO. Because the SQL file is generated automatically from those sources, and because both bodies hold Taiwan to be a province of China, I'm leaving the SQL as is with respect to Taiwan.

If you have a certain insight into the topic, or deal with those who may be sensitive to the issue, you might want to edit the SQL file such that Taiwan is listed simply as "Taiwan".

3/16/04:
If you're looking for U.S. and Canada telephone area codes, try a site called areacodedownload.com.

10/8/03:
More mail, more changes. I got a couple requests for non-upper case country names (like you'd use in text or something. It's not really an easy thing to do in SQL, so I added it to the perl script that makes the SQL insert file. There's now another column called 'printable_name' and it has the country names with their proper capitalization. This seems kind of redundant to me, but it's not a very big file, and not a very big database table. Besides, the extra storage space needed by the 'redundant' column is probably preferrable to the CPU overhead caused by making printable names in code or SQL at the time of the select.

5/20/03:
I've been getting a lot of email recently and ask if I have the 3-letter and 3-digit codes for each country (this is what is contained in ISO 3166-2). ISO charges for 3166-2, and up until now I've just been grabbing the free 3166-1 list off their web site. I did some looking into it, and I found a place that has the extra codes, for free.

It turns out that ISO gets their country names from the United Nations' country list. Well, it's really pretty easy for me to get that info and correlate it. In fact, there's a Perl module that helps make this happen. The Geography::Countries module aims to give you the county names, the 2-letter codes, the 3-letter codes, and the 3-digit numeric codes. The country names were slightly different than what ISO hands out, but the two-letter codes were the same. (I suspect that the module is slightly out of date, another reason why I want to keep getting the data off the web.) Because of this, and in the interests of backward compatibility, I decided to keep the ISO names.

As an aside, there are 12 countries (as of 5/20/03) that don't have the ISO 3166-2 data. Since the data are free, the price is right for what I did manage to get. Anyway, run SELECT name FROM country WHERE iso3 IS NULL; if you want to see which ones don't have the 3-letter/digit codes.

I've updated the perl script to create a SQL file which includes the new 3166-2 info where available.

1/1/03:
I've had a few people email me and ask if I had a similar SQL import file for U.S. Postal Service state names and their abbreviations. I didn't have an actual downloadable file, but it was made easily enough with mysqldump. It's linked up there at the top of the page. You use it exactly like the country import file, and a select list can be made with only a little bit of jiggering to the example code I've given below.

I did a little googling for such a file and couldn't find much. Oddly enough, the ones that I did find were incomplete since they didn't have the Armed Forces "states" or territories/protectorates like Samoa and Virgin Islands. Granted, probably not many people do business with those in Puerto Rico or the Republic of Palau, but if you want to have a drop-down of U.S. states and you don't provide the lesser-known ones you make it hard for those people to use your site. This SQL file has the names and two-letter abbreviations for all the states that the USPS considers "domestic".

Uses:
To import this into a MySQL database, just do like so:
mysql -u username -ppassword database_name < iso_country_list.sql
If you have create permissions on 'database_name', you'll then have a table called 'country' which has all your country info. You'd use this table in another table like so:
CREATE TABLE register (
  name VARCHAR(255) NOT NULL,
  address1 VARCHAR(255) NOT NULL,
  address2 VARCHAR(255),
  address3 VARCHAR(255),
  city VARCHAR(150) NOT NULL,
  state VARCHAR(100),
  zip VARCHAR(20),
  country_iso CHAR(2) REFERENCES country (iso)
);
Then you can do selects and such against all the tables. The table above is purely hypothetical, by the way. I made it just now off the top of my head, so you'll probably want to use something which is better tailored to your specific needs (phone number, two-character states, indexes for faster selects with 'where' clauses, etc).

Some code also might come in handy. Here's a snippet of PHP which will make an HTML select list from all the countries listed in your database:

@ $db = mysql_pconnect($hostname, $dbusername, $dbpasswd);
if (!$db) {
    print "Failed to connect to the database.  Please try again later.\n";
    exit;
}
mysql_select_db($dbname);
$sql = "select id,name from country order by iso";
if (!(@ $result = mysql_query($sql))) {
    print "There was an error running your query:<p>\n". $sql . "<p>\n";
}
print '<select name="country"><br>\n';
print '<option value="">  Choose One   ';
while ($row = mysql_fetch_row($result)) {
    $country_iso   = $row[0];
    $country_name  = $row[1];

    print "<option value=\"$country_iso\"";
    if ($_REQUEST["country"] == $country_iso) { print " selected"; }
    print "> $country_name <br>\n";
}
print '</select>\n';
Obviously, you'll have to supply all the connection variables and such. This code is useful for PHP scripts which call themselves, by the way. It checks the incoming $_REQUEST array for value for the 'country' element and if it finds one which matches the value stored in the database, then it makes that choice selected. This is handy if you do error checking on your scripts. I usually check for required fields and if one is not found, I just call the PHP bits which made my form initially. By "keeping" the original values, the form elements will be set to what they were when the form was first filled out. It's a nice feature and good code re-use. Oh yeah, your script can also check to see if the country has been left blank, since the "Choose One" option has an explicitly empty value. Without it, the choice would literally be "Choose One".

One more side effect of this snippet is that the value of the options in the select list will be the id number of the country. This is (mostly) good, because when you go to insert the form contents into the database, you simply insert this numerical value in your main table (the 'country_id' field in my pretend table above). This avoids having to look up the numerical code of a two-letter code or a country name and insert that. It's one less trip to the database, and so quick and handy. (Although it presents a race condition which may make the choice of convenience one you don't want to make: if something updates the table between the time the user loads the initial form and when they submit the form, they might be submitting the wrong id number.) When you do selects on the 'register' table, you just do a plain natural join on the country table, like so:

select applicant.name,city,country.name
from applicant,country
where country_id=country.id and country.iso='my';

select applicant.name,city,country.name
from applicant,country
where country_id=country.id and country.name='Malaysia';
Those queries will do the same thing: find the name, city and country name for all the registrants from Malaysia. The first one uses the country code (good if looping through like from an array of ISO codes or something) and the second is by canonical country name (good if you're doing something like giving someone a web-based form with a select list of country names --- although you could use the code snippet above to access the id numbers directly).

If you wanted to do this with Perl or Python, the concepts are identical, BTW.

Contact Info:
If you have questions about any of this, you can get a hold of Bill Rhodes at iso_country_list@27.org. I can't really answer general code questions, but if you have something really specific about this stuff then I'll try to help out.

I've also got some more projects you can look at too if you're so inclined.