Geo IP identification

The Geo IP database is available at Maxmind and allows to trace your home city from IP addresses. Here is a quick and dirty script to upload the Geo IP data into MySQL:

 1:
 2:
 3:
 4:
 5:
 6:
 7:
 8:
 9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
23:
@echo off
rem set path to mysql binaries
set i=C:\Programme\XAMPP\xampp\mysql\bin\

rem SQL statement
echo CREATE TABLE GeoLiteCity (locId DOUBLE,country CHAR(15),region CHAR(15),city CHAR(15),postalCode CHAR(15),latitude DOUBLE,longitude DOUBLE,dmaCode DOUBLE,areaCode DOUBLE); >1.SQL
echo CREATE TABLE GeoLiteCityBlocks (startIpNum DOUBLE,endIpNum DOUBLE,locId DOUBLE); >1.SQL

rem create database
%i%mysql <1.SQL
%i%mysql <2.SQL

rem drop SQL statement
del 1.SQL>nul
del 2.SQL>nul

rem import data
%i%mysqlimport --fields-terminated-by="," --fields-optionally-enclosed-by="\"" --lines-terminated-by="\n" --host=localhost --user=root test d:\GeoLiteCity.csv
%i%mysqlimport --fields-terminated-by="," --fields-optionally-enclosed-by="\"" --lines-terminated-by="\n" --host=localhost --user=root test d:\GeoLiteCityBlocks.csv

pause
exit

I would also put an index on loc_id. Finally the database should be available as

SELECT city
FROM GeoLiteCity INNER JOIN GeoLiteCityBlocks ON GeoLiteCity.locID = GeoLiteCityBlocks.locID
WHERE $myIP >= startIpNum AND $myIP <= endIpNum;

where $myIP is calculated as

substr($_SERVER['REMOTE_ADDR'],0,3) * 16777216 +
substr($_SERVER['REMOTE_ADDR'],4,3) * 65536 +
substr($_SERVER['REMOTE_ADDR'],8,3) * 256 +
substr($_SERVER['REMOTE_ADDR'],12,3)