<?php
 
   $db_info = array(
      'host' => 'localhost',
      'name' => 'EDIT ME',
      'user' => 'EDIT ME',
      'pass' => 'EDIT ME'
   );
 
   error_reporting(E_ALL);
   ob_end_clean();
 
   function out($string) {
      echo $string;
      flush();
   }
 
   out("<pre>");
 
   function map_fields($table, $row, $index) {
      if ($index == 0)
         return NULL;
      $retval = array();
      switch ($table) {
         case 'country':
            $retval = array(
               'id' => $row[0],
               'name' => $row[1]
            );
            break;
         case 'region':
            $retval = array(
               'id' => $row[0],
               'country_id' => $row[1],
               'name' => $row[2]
            );
            break;
         case 'city':
            $retval = array(
               'id' => $row[0],
               'country_id' => $row[1],
               'region_id' => $row[2],
               'name' => $row[3]
            );
            break;
         default:
            exit("Fatal: Called map_fields with unsupported table: $table\n\n");
      }
      return $retval;
   }
 
   function do_mysql_query($query) {
      if (!mysql_query($query))
         exit("\n\nFatal: do_mysql_query failed with MySQL error: " .mysql_error() ."\n---------------------\nAnd query: $query\n\n");
   }
 
   function create_table($table) {
      out("Creating table: $table...");
      do_mysql_query("
         DROP TABLE IF EXISTS codehead_$table
      ");
      $query = "";
      switch ($table) {
         case 'country':
            $query = "
               CREATE TABLE codehead_country (
                  id INT NOT NULL PRIMARY KEY,
                  name VARCHAR(100),
                  index (name)
               )
            ";
            break;
         case 'region':
            $query = "
               CREATE TABLE codehead_region (
                  id INT NOT NULL PRIMARY KEY,
                  country_id INT NOT NULL,
                  name VARCHAR(100),
                  index (name),
                  index (country_id, name)
               )
            ";
            break;
         case 'city':
            $query = "
               CREATE TABLE codehead_city (
                  id INT NOT NULL PRIMARY KEY,
                  country_id INT NOT NULL,
                  region_id INT NOT NULL,
                  name VARCHAR(100),
                  index (name),
                  index (region_id, name),
                  index (country_id, region_id, name)
               )
            ";
            break;
         default:
            exit("Fatal: Called create_table with unsupported table: $table\n\n");
      }
      do_mysql_query($query);
      out("Done!\n");
   }
 
   function empty_table($table) {
      do_mysql_query("DELETE FROM codehead_$table");
   }
 
   function insert_row_into_table($table, $row) {
      $query = "";
      switch ($table) {
         case 'country':
            $id = intval($row['id']);
            $name = mysql_real_escape_string($row['name']);
            $query = "
               INSERT INTO codehead_country VALUES (
                  $id,
                  '$name'
               )
            ";
            break;
         case 'region':
            $id = intval($row['id']);
            $country_id = intval($row['country_id']);
            $name = mysql_real_escape_string($row['name']);
            $query = "
               INSERT INTO codehead_region VALUES (
                  $id,
                  $country_id,
                  '$name'
               )
            ";
            break;
         case 'city':
            $id = intval($row['id']);
            $country_id = intval($row['country_id']);
            $region_id = intval($row['region_id']);
            $name = mysql_real_escape_string($row['name']);
            $query = "
               INSERT INTO codehead_city VALUES (
                  $id,
                  $country_id,
                  $region_id,
                  '$name'
               )
            ";
            break;
         default:
            exit("Fatal: Called insert_row_into_db with unsupported table: $table\n\n");
      }
      do_mysql_query($query);
   }
 
   $base_dir = dirname(__FILE__);
 
   $tables = array(
      $base_dir .'/Countries.txt' => 'country',
      $base_dir .'/Regions.txt' => 'region',
      $base_dir .'/Cities.txt' => 'city'
   );
 
   $db = mysql_connect($db_info['host'], $db_info['user'], $db_info['pass']);
   if (!$db)
      exit("Fatal: Couldn't connect to MySQL...\n\n");
   if (!mysql_selectdb($db_info['name']))
      exit("Fatal: Couldn't select database...\n\n");
 
   foreach ($tables as $file => $table) {
      if (($fp = fopen($file, 'r')) !== false) {
         create_table($table);
         out("Inserting rows into table: $table");
         $index = 0;
         while (($data = fgetcsv($fp, 10000)) !== false) {
            if ($index == 0) {
               ++$index;
               continue;
            }
            $row = map_fields($table, $data, $index);
            insert_row_into_table($table, $row);
            ++$index;
            out(".");
         }
         fclose($fp);
         out("Done!\n");
      } else
         exit("Fatal: Couldn't open file: $file\n\n");
   }
 
   out("All done!\n\n");
   mysql_close($db);
 
?>