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)