Location: PHPKode > scripts > Cities of the world > cities-of-the-world/cities_sql.php
Author:  Ahmet M. Okar (hide@address.com)
Package: Cities_of_the_World
File:    cities_sql.php
Desc:    PHP script to create a MySQL database and to import data from the file
         'cities.csv' into this database.

This program is free software; you can redistribute it and/or modify it under
the terms of the GNU General Public License as published by the Free Software
Foundation; either version 2 of the License, or any later version.

This program is distributed in the hope that it will be useful, but WITHOUT
ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with
this program; if not, write to the Free Software Foundation, Inc., 59 Temple
Place, Suite 330, Boston, MA 02111-1307 USA.

/*========================= EDIT THIS SECTION TO FIT YOUR SETTINGS ===========================*/ 
// the name of the db host, in most cases "localhost" would do fine.
$db_host = "localhost";
// the name of the db user required to connect to database. SELECT and FILE privileges should be granted to this user.
$db_user = "";
// the password required to connect to database.
$db_pass = "";
// the absolute path to the "cities.csv" file.
//   On Windows platforms, make sure to have 4 backslashes between each component, e.g. for the
//   file put in "c:\temp\cities.csv" you should assign "c:\\\\temp\\\\cities.csv";
//   On Unix/Linux platforms, assign simply the file path, e.g. "/www/home/temp/cities.csv";
//   Please uncomment (delete // at the beginning) and edit if necessary one of the following lines.

//$csv_file_path = "c:\\\\temp\\\\cities.csv";      // Statement for WINDOWS
//$csv_file_path = "/www/home/temp/cities.csv";     // Statement for Unix/Linux

// the name of the db that will hold the data.
$db_name = "countries_cities";
// the name of the table in which the data will be stored.
$db_table = "cities";
/*==================================== END OF EDITING ========================================*/ 

$create_db = "CREATE DATABASE $db_name";
$create_table =  "CREATE TABLE $db_table (unique_id MEDIUMINT(4) NOT NULL, local_id MEDIUMINT(3) NOT NULL, city VARCHAR(65) NOT NULL, cc VARCHAR(2) NOT NULL, country VARCHAR(35) NOT NULL, PRIMARY KEY (unique_id))";
$import_data = "LOAD DATA INFILE '$csv_file_path' INTO TABLE $db_table FIELDS TERMINATED BY ';'  LINES TERMINATED BY '\r\n'"; 
<html><head><title>Cities of the World</title></head><body>
$connect = @mysql_connect($db_host, $db_user, $db_pass);
if (mysql_error()) echo("failure to connect to db host [".$db_host."] =&gt; ".mysql_error()."<br>");
// to only append a table into an existing databese comment (add // at the behinning) next two lines.
$query = @mysql_query($create_db);
if (mysql_error()) echo("failure to create the db [".$db_name."] =&gt; ".mysql_error()."<br>");
$db = @mysql_select_db($db_name);
if (mysql_error()) echo("failure to select the db [".$db_name."] =&gt; ".mysql_error()."<br>");
$query = @mysql_query($create_table);
if (mysql_error()) echo("failure to create the table [".$db_table."] =&gt; ".mysql_error()."<br>");
$query = @mysql_query($import_data);
if (mysql_error()) echo("failure to import data from [".$csv_file_path."] into the table [".$db_table."] =&gt; ".mysql_error()."<br>");
if (!mysql_error()) echo ("All done...");
Return current item: Cities of the world