Solved

IP-Country DB Tables

Posted on 2006-07-13
2
555 Views
Last Modified: 2013-12-12
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 .

I found a script which queries the database using the following syntax:
$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
0
Comment
Question by:blaadom
2 Comments
 
LVL 40

Accepted Solution

by:
RQuadling earned 125 total points
ID: 17106702
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
count download link and run update query 9 55
simplest php form 3 59
Amazon Redshift 2 25
PHP - AJAX and MySQL it works only if the value is a number 12 34
This article will explain how to display the first page of your Microsoft Word documents (e.g. .doc, .docx, etc...) as images in a web page programatically. I have scoured the web on a way to do this unsuccessfully. The goal is to produce something …
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now