The IP to Country Database

As a complete beginner to SQL, I have just learnt the following, how to set up a database to use this, from scratch. PLEASE NOTE that the csv file you download is supplied with DOS line endings. You must convert these to UNIX line endings or modify the 'terminated by' parameter accordingly.

Assuming you can download and install MySQL or another database without problem, connect as the root user issue these commands:

sql> CREATE DATABASE databasename;
sql> USE databasename;
sql> CREATE TABLE iptocountry (ip_from int(4), ip_to int(4), country_code2 char(2), country_code3 char(3), country_name varchar(50));
sql> load data infile '/path/to/ip-to-country.csv' into table iptocountry fields terminated by ',' enclosed by '"' lines terminated by '\n';

You should now see: "Query OK, 53416 rows affected" or another number which should corrispond exactly with the number of lines in your csv file. Verify this is the case. If not, issue the command "sql> delete from iptocountry" to remove the records, then tweak your load data file command and try again.

To verify you have got good data in your database, try this:

sql> select * from iptocountry limit 10;

You should see the first ten fields of the database printed out. verify they are identical to the first lines of your csv file. To finish off, issue this command:

sql> optimize table iptocountry

That will optimise your table in case you have deleted lots of records, and generally make sure it is fast and efficient. Warning! Note the american spelling of optimise. MySQL (for one) does not understand the normal spelling.

I hope this helps others. It took me about three hours to install, learn and get SQL working. Refer to php.net for instructions on querying databases from PHP.

I looked around & didn't saw any article that'll guide me about importing records in a database from the csv file. It can be a pain in the back if you are a first timer. I learned it the hard way but thought that I'd post this here so that other first timers don't have any problem.

Here's it is then
-------------------------

First of all, convert the csv file into MS-Excel workbook & save it.
To convert the CSV file, simply open it in MS-Excel & then select
File---> SaveAs & save it as Excel Workbook.

Then make a database in MS-Access.
Right click in the empty white area where it displays tables. Select Import from the context menu that pops up. In the import file dialog, select the file type to Microsoft Excel & then browse to the location where you have saved the excel workbook. Select the workbook file & click Import.

The Import Spreadsheet dialog appears. Make sure the checkbox stating 'First Row Contains Column Headings' is unchecked. Click next. It asks where you would like to store data. the new table option is selected by default. Click next.

In the field options, name the fields by selecting each field or let it remain like that. You can always change them later. Click Next. It asks to set a primary key. Choose 'No Primary Key'. Click Next. Enter the table name you want to create. Click on Finish.

The data is imported quickly(depends on your processor & memory). The tablename that you entered is displayed in the tables list. Double click to open it. Or you can open it in Design View to change the field names.

For convenience below are the suggested field names:-

name 1st field as ip_from & check that its data type is number & is double.
name 2nd field as ip_to & check that its data type is number & is double.
name 3rd field as country_code2 & check that its data type is text.
name 4th field as country_code3 & check that its data type is text.
name 5th field as country_name & check that its data type is text.


______________________________
Mystiq
^-------------^
A new Face everyday

anyone know the proper query for this? i've searched all through mySQL.com and everything i've found doesn't return a result.

COPY FROM '/ip-to-country.csv' WITH DELIMITER ',' CSV QUOTE '"'

--- GET THE FILE : ---

wget http://ip-to-country.webhosting.info/downloads/ip-to-country.csv.zip

unzip ip-to-country.zip; mv ip-to-country.csv iptocountry.csv; rm -f ip-to-country.zip

--- GIVE THE PERMISSIONS : ---

chown mysql.mysql iptocountry.csv; chmod 666 iptocountry.csv

--- CREATE DATABASE AND TABLE : ---

CREATE DATABASE databasename;

USE databasename;

CREATE TABLE `iptocountry` (
  `IP_FROM` double NOT NULL default '0',
  `IP_TO` double NOT NULL default '0',
  `COUNTRY_CODE2` char(2) NOT NULL default '',
  `COUNTRY_CODE3` char(3) NOT NULL default '',
  `COUNTRY_NAME` varchar(50) NOT NULL default '',
  KEY `CODE` (`IP_FROM`,`IP_TO`,`COUNTRY_CODE2`)
) ENGINE=MyISAM;

--- LOAD DATA FILE INTO TABLE : ---

LOAD DATA INFILE '/path/to/csv/iptocountry.csv' INTO TABLE `iptocountry` FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

Now you should see :

Query OK, 65290 rows affected (0.52 sec)
Records: 65290  Deleted: 0  Skipped: 0  Warnings: 0

or another number which should corrispond exactly with the number of lines in your csv file. To verify you have got good data in your table, try this:

SELECT * FROM `iptocountry` LIMIT 10;

--- REMOVE THE CSV FILE : ---

rm -f iptocountry.csv

NOTE: You need root privileges to this!

Anybody else out there using PHP 5? :)

For CSV -> SQLite transfer you have the choice of inputting 60,000+ entries into the db one by one, or using .import from the commandline. Assuming you're sane and will use .import, you'll need to change the separator. You could fiddle with the .separator command in the SQLite commandline client (get it from sqlite.org and make sure it's the right version!), but having the SQLite separator defined as a comma doesn't work here.

There are a lot of ways you could clean up the data for SQLite, but having the CSV data use the default SQLite separator seems to make the most sense. This works, anyway:

php -a
Interactive mode enabled

<php
$data = file('ip-to-country.csv');
foreach ($data as $line) {
    $line = trim($line, '"');
    $line = str_replace("\"\r\n", "\n", $line);
    $new[] = str_replace('","', '|', $line);
}
file_put_contents('cleaned.csv', $new);
?>
^Z    /* note: that's CTRL Z in case you got confused there... */

sqlite3 ip_tables.db
SQLite version 3.3.7
Enter ".help" for instructions
sqlite> create table iptocountry (
   ...> ip_from int(4),
   ...> ip_to int(4),
   ...> country_code2 char(2),
   ...> country_code3 char(3),
   ...> country_name varchar(50));
sqlite> .import cleaned.csv iptocountry

/* - and test the data - */
sqlite> select * from iptocountry where country_name = 'VIET NAM';
985268224|985399295|VN|VNM|VIET NAM
1063573680|1063573695|VN|VNM|VIET NAM
.....  30-odd entries later  ....
3741057024|3741319167|VN|VNM|VIET NAM
sqlite> select country_name from iptocountry where ip_to = 3741319167;
VIET NAM
sqlite> .quit