Logicationz Tech

More Intelligent Solutionz…!

Country Name and ISO 3166 Code MySQL Import File

Download iso_country_list.sql (18662 bytes, Last updated Sat Dec 01 23:19:11 2007)
Download usps_states_list.sql (4202 bytes, Last updated Sat Dec 01 23:19:11 2007)

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.

January 8, 2010 Posted by | Helpful Tips and Tricks, MySql | , , | 1 Comment