You Are Here Home > Web Development

Web Development

Importing GeoWorldMap Database Into MySQL Using PHP

First download the database from:

http://www.geobytes.com/freeservices.htm

Then, create a folder in your server where you can access it via a browser and upload the files in there, then save this script as import.php and upload it into the same folder and then point your browser to this PHP script you just uploaded. Note that you must edit the script and add your own database name/username/password to it before uploading.

<?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);
 
?>
Importing GeoWorldMap Database Into MySQL Using PHP
Comments (1)   Filed under: PHP,Web Development   Posted by: Hamid

PHP: Problem With Displaying French Accented Characters; black diamond…

If you have this problem and your accented characters are being replaced by black diamonds with question marks in them and you tried EVERYTHING you could find and nothing worked and no one seems to know what’s going on and you think it’s PHP or Apache that is causing this issue and you tried changing their configuration directives and you are pulling your hair out then this could be your editor!

Go to it’s preferences, most of them have a section for font encoding, for example in Komodo Edit, go to:

Preferences > Fonts and Colors > Under the Fonts tab > There is the font encoding, choose UTF-8

After this step you might have to change the encoding of the current file, again most editors should be able to do this, refer to your editor’s docs for more info on this, but here is how to do this in Komodo Edit:

Open File > Edit > Current File Settings > In File Settings Box > Change Encoding To UTF-8 > Save

and Voila!

PHP: Problem With Displaying French Accented Characters; black diamond…
Comments (1)   Filed under: Komodo Edit,PHP,Web Design,Web Development   Posted by: Hamid
« Newer Posts — Older Posts »