ISO 3166 Country List

Here is a small script that I put together to grab the XML list from the ISO website and convert it into an SQL statement. The idea is that the script can be used to just obtain a list for web projects or it can be setup to run periodically to make sure that a list is always kept up to date. Obviously none of the associated checks to make that work have been written into this code so you will need to add it yourself (ie. checking that if a country has been removed that you are not going to break referential integrity etc).

It is a really simple script so just use it as you wish, but make sure you reference the original source. It is not your work and you may not pass it off as such. This script requires PHP5 as it makes use of the SimpleXML functions. As you can tell this script is really more of a utility script and not for general consumption. There are some comments thrown in but it is a very simple script so you should not have too much trouble with it.

<?php
//Get ISO list of countries
$iso_xml_zip_file_url = 'http://www.iso.org/iso/';
$iso_xml_zip_filename = 'iso_3166-1_list_en.zip';
$iso_xml_zip_new_filename = 'iso_3166-1_list_en_'.date('Y-m-d').'.zip';
$iso_xml_filename = 'iso_3166-1_list_en.xml';
$download_directory = './temp/';

//setup database variables
$table_name = 'countries';
$country_column_name = 'original';
$country_column_capitalised_name = 'name';
$country_column_alpha = 'alpha';

//download the file
if(!file_exists($download_directory.$iso_xml_zip_new_filename))
    copy($iso_xml_zip_file_url.$iso_xml_zip_filename, $download_directory.$iso_xml_zip_new_filename) or die('Could not copy '.$iso_xml_zip_file_url.$iso_xml_zip_filename.' to '.$download_directory.$iso_xml_zip_new_filename);

$za = new ZipArchive();
$za->open($download_directory.$iso_xml_zip_new_filename) or die('Cannot open ZIP file - possibly corrupt.');
$xml = $za->getFromName($iso_xml_filename) or die('Cannot extract XML file ('.$iso_xml_filename.') - perhaps the ZIP is corrupt.');

//clean up the names of the elements so they will work as objects
$xml = str_replace(array('ISO_3166-1_', '-2_Code_element', '_name'), '', $xml);
$xml = simplexml_load_string($xml);

$SQL = '';
foreach($xml->Entry as $entry) {
    $SQL .= "INSERT INTO `$table_name` SET
            `$country_column_name` = '".mysql_escape_string($entry->Country)."',
            `$country_column_capitalised_name` = '".mysql_escape_string(ucwords(strtolower($entry->Country)))."',
            `$country_column_alpha` = '".mysql_escape_string($entry->Alpha)."';\n\n";
}

print '<pre>'.$SQL.'</pre>';

if(!file_exists($download_directory.$iso_xml_zip_new_filename))
    copy($iso_xml_zip_file_url.$iso_xml_zip_filename, $download_directory.$iso_xml_zip_new_filename) or die('Could not copy '.$iso_xml_zip_file_url.$iso_xml_zip_filename.' to '.$download_directory.$iso_xml_zip_new_filename);

?>

There is some sample output from this script for download as well: 2007008221iiso33166ssql