[thelist] Country by IP address

Jason Handby jason_handby at illustraresearch.com
Tue Jun 15 07:10:02 CDT 2004

Hi Andy,

> Thanks for the feedback so far. I probably should have been a little 
> more specific as I've received quite a few emails either suggesting 
> commercial services or stats packages.
> I'm basically looking for some hints on how to write a simple php 
> script (as I'm not a very good php programmer) to examine the IP 
> address of a site visitor and guesstimate the visitors 
> country by using 
> a mySQL db lookup. It's for my personal site so I'm not really 
> interested in any paid services.

Even if you're going to script it yourself, you'll still need to get IP
address data from somewhere. When I last did this, I bought the GeoIP
database as a CSV file from MaxMind ( http://www.maxmind.com/app/country
-- only 50 dollars), imported it into SQL Server, and scripted the
look-up myself. You could easily do the same thing with MySQL.

Take a look at the sample CSV data file you can download from the page
above. Each record represents a continuous block of IP addresses. The
first two columns, "beginIp" and "endIp", give you the start and end
addresses of that block. The third and fourth columns, "beginIpNum" and
"endIpNum", are the same information as in the first two, but multiplied
up into one big integer. For example if you had the IP address


it would become

	I = (A * 256 * 256 * 256) + (B * 256 * 256) + (C * 256) + D

When I imported the CSV file into SQL Server, I made the "beginIpNum"
column of my table the primary key. Then in order to look up a user's IP
address I just converted it to a big number, I, as above, and then
queried my database:

	SELECT * FROM GeoIP WHERE I >= beginIpNum AND I <= endIpNum

I hope this helps. (I guess it will only help if you don't mind spending
50 dollars :-)  )


More information about the thelist mailing list