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...).

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".

mysql -u username -ppassword database_name < iso_country_list.sql

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)
);

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';

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';

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

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