Firstly download the IP-To-Country Database and unzip it
Then create a table in oracle
CREATE TABLE IPTOCOUNTRY(
IP_FROM NUMBER,
IP_TO NUMBER,
COUNTRY_CODE2 CHAR(2 BYTE),
COUNTRY_CODE3 CHAR(3 BYTE),
COUNTRY_NAME VARCHAR2(50 BYTE),
REGISTRY VARCHAR2(30 BYTE)
) TABLESPACE USERS;
ALTER TABLE IPTOCOUNTRY ADD (
CONSTRAINT PK_IPTOCOUNTRY PRIMARY KEY (IP_FROM, IP_TO)
USING INDEX
TABLESPACE USERS);
Load the data using the following sqlldr control file
(hint edit the paths)
OPTIONS (SKIP=132, DIRECT=TRUE)
LOAD DATA
INFILE 'C:\downloads\geoip\IpToCountry.csv'
BADFILE 'C:\downloads\geoip\IpToCountry.bad'
DISCARDFILE 'C:\downloads\geoip\IpToCountry.dsc'
INTO TABLE "IPTOCOUNTRY"
WHEN (1) <> '#'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' AND '"'
( IP_FROM,
IP_TO,
REGISTRY,
assigned filler,
COUNTRY_CODE2,
COUNTRY_CODE3,
COUNTRY_NAME)
then once that's loaded, you can then query the database, but you need to convert the ip address ( IE CGI.REMOTE_ADDR ) to a long ip format (ip2long in php, inet_aton in mysql), here's a CFMX library IPConvert for CFMX that will convert dotted quads to long numeric ip addresses and back.
then you just need to do a simple database query
SELECT *
FROM iptocountry
WHERE ip_from <= <cfqueryparam value="#ip_long#" cfsqltype="CF_SQL_NUMERIC">
and ip_to >= <cfqueryparam value="#ip_long#" cfsqltype="CF_SQL_NUMERIC">
</cfquery>
#q_ip.COUNTRY_CODE3# is the country name and #q_ip.COUNTRY_CODE2# is the two letter equivalent which can be used with thse small country flags or large country flags simply with the following code ("ZZ" are unassigned countries)
<fif q_ip.recordcount and q_ip.COUNTRY_CODE2 neq "ZZ">
<cfoutput>
#q_ip.COUNTRY_CODE3#
<img src="#q_ip.COUNTRY_CODE2#.png"
width="20" height="13" alt="">
</cfoutput>
<CFELSE>
unknown
</cfif>
No comments:
Post a Comment