IP-to-Country DB Structure from CSV file.

Rollo Tamasi

hi guys,

I'm setting up a system which detects what country a user is from by running a query on the IP-to-Country database which i downloaded as a CSV file from www.ip-to-country.webhosting.info .

The script i'm using to query the database uses this sql:

$country_query  = "SELECT country_code2,country_name, FROM iptoc ".
         "WHERE IP_FROM<=inet_aton('$REMOTE_ADDR') ".
          "AND IP_TO>=inet_aton('$REMOTE_ADDR') ";

The CSV file has the following layout:

"33996344","33996351","GB","GBR","UNITED KINGDOM"
"50331648","69956103","US","USA","UNITED STATES"
"69956104","69956111","BM","BMU","BERMUDA"

The documentation for the CSV file states that " The CSV file contains four fields:

* Begining of IP address range
* Ending of IP address range
* Two-character country code based on ISO 3166
* Three-character country code based on ISO 3166
* Country name based on ISO 3166 "

So how should i setup my database tables/fields? Obviously i have to have an ID field which auto increments, and i will be adding a field for paths for flags, but what other fields should i have? This is the SQL i'm working with now:

CREATE TABLE `tables` (
`id` INT( 5 ) NOT NULL AUTO_INCREMENT ,
`country_code2` INT( 2 ) NOT NULL ,
`country_code3` INT( 3 ) NOT NULL ,
`country_name` VARCHAR( 55 ) NOT NULL ,
`IP_FROM` INT( 12 ) NOT NULL ,
`IP_TO` VARCHAR( 12 ) NOT NULL ,
`flag` INT( 12 ) NOT NULL ,
PRIMARY KEY ( `id` )
) CHARACTER SET = latin1;

Also how do i import all the values from the CSV file? I've never used one before.

Thanks

Serbian

There was a thread a couple of days ago about MaxMind's GeoIP which does the same thing as you have above. I would recommed downloading MaxMind's version as they have a guide on setting up MySQL and importing the CSV on their site.

Rollo Tamasi

cheers serbian.

I'm getting stuck at this step:

Next we run the MySQL import utility. Of course you also use another utility to import the contents of the csv file into the csv table. mysqlimport --fields-terminated-by=,
--fields-optionally-enclosed-by=\"
--lines-terminated-by=\n
--host=<hostname>
--user=<username>
--password=<password>
geoip csv.csv
geoip.csv: Records: 63809 Deleted: 0 Skipped: 0 Warnings: 0

mneylon

Rollo
What's causing issues exactly?

Rollo Tamasi

sorry, i should have been more clear.

I don't actually understand that step.

mneylon

The instructions are for using the mysql command line import utility:

http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html

http://www.die.net/doc/linux/man/man1/mysqlimport.1.html

http://sunsite.mff.cuni.cz/MIRRORS/ftp.mysql.com/doc/en/LOAD_DATA.html&e=9797

http://www.informit.com/articles/article.asp%3Fp%3D174365%26seqNum%3D3&e=9797

jmcc

Rollo Tamasi wrote:

[code]
CREATE TABLE `tables` (
`id` INT( 5 ) NOT NULL AUTO_INCREMENT ,
`country_code2` INT( 2 ) NOT NULL ,
`country_code3` INT( 3 ) NOT NULL ,
`country_name` VARCHAR( 55 ) NOT NULL ,
`IP_FROM` INT( 12 ) NOT NULL ,
`IP_TO` VARCHAR( 12 ) NOT NULL ,

Surely this should be:

IP_TO INT(12) NOT NULL,

Also:
`country_code2` CHAR( 2 ) NOT NULL ,
`country_code3` CHAR( 3 ) NOT NULL ,

Also there is no index set and if you are using a full db, you might need it.

Regards...jmcc

Serbian

Would it be any easier if I included the Database as a series of SQL Statements?

GeoIP.zip

Rollo Tamasi

cheers serbian

Rollo Tamasi

Any chance the comment above can be deleted as it's a spam comment?

democrates

If speed or space is an issue my tack would be to limit the ip lookup table fields to ip from and to in decimal and 2 letter country code (just drop the other fields after the full import). The dot format ip's seem redundant to me in this kind of application.
Anyway that get's that table down to around one fifth of the size before indices so less disk access overhead.

A seperate table can hold the ISO3166 data with 2-letter cc as the primary key so you can join it if you need three-letter code or country name, that's a small table with a small key so lookups will be fast. /2c