Determine Country from IP Address

The web has no national boundaries, and most websites will attract visitors from all around the globe.

I needed to be able to determine the country location of each visitor to a site. Although you can never expect to achieve a 100% correct answer, you can get close.

A client of mine wanted to provide an free incentive for every visitor who landed on a particular page of his site. He knew from his web stats that at least 80% of site visitors were non UK. As all his business, video production, was UK based, he didn't want the expense of providing the free CD showreel to overseas visitors.

I needed a database that maps IP addresses to a country. There are several sources to choose from, I used IP2Location which costs $49 and includes free updates for a year. Data is provided in Access, CSV and MySQL formats. This how the first few rows of the data looks

ipFROM	ipTO	countrySHORT
0	33996343	-
33996344	33996351	UK
33996352	50331647	-
50331648	67277055	US
67277056	67277119	CA

The total number records is about 60,000. The database is constantly updated.

I used a Access database on the server as it was simple to use, there was no setup unlike SQL server and with less than 100 visitors per day there was no performance concerns.

The code, written in VB ASP, used ADO to perform the database lookup. Here is the code I use

' This sample code is provided as is, for aiding understanding.
' I make no warranty about its fitness for purpose.
' If you find it useful please use it.
' Don't expect me to sort out any problems unless you are prepared to pay me.
' Written by Paul Stanley Software http://www.pssuk.com

' Set IP string, 1st check for proxy
sIP = Request.ServerVariables("HTTP_X_FORWARDED_FOR")
if sIP = "" then
sIP = Request.ServerVariables("REMOTE_ADDR")
end if

' Handle extended IP addresses
nComma = Instr(sIP, ",")
if nComma > 0 then
sIP = Left(sIP, nComma - 1)
end if

set oConnIP = CreateObject("ADODB.Connection")
sConn = "Driver={Microsoft Access Driver (*.mdb)}; Dbq="
sConn = sConn & Server.MapPath(".") & "\IPCountry.mdb"
sConn = sConn & "; Uid=admin; Pwd="
oConnIP.Open sConn

set oRSip = CreateObject("ADODB.RecordSet")
oRSip.ActiveConnection = oConnIP
oRSip.CursorLocation = adUseClient

nIP = Dot2LongIP(sIP)
sSQL = "SELECT [CountryShort] FROM [IPCOUNTRY] WHERE " & CStr(nIP) & " BETWEEN [ipFrom] AND [ipTo];"
oRSip.Open sSQL, oConnIP, adOpenForwardOnly, adLockReadOnly

sCountry = oRSip("CountryShort")

function Dot2LongIP (ByVal DottedIP)
Dim i, pos, PrevPos, num
' Convert IP String like "1.12.124.22" to 32 bit integer

if len(DottedIP) < 7 or isnull(DottedIP) or DottedIP = "unknown" then
    Dot2LongIP = 0
else
    PrevPos = 0
    for i = 1 To 4
        pos = InStr(PrevPos + 1, DottedIP, ".", 1)
        if pos = 0 then
            If i = 4 Then
                pos = Len(DottedIP) + 1
            else
                Dot2LongIP = 0
                exit function
            end If
        end if
        num = Int(Mid(DottedIP, PrevPos + 1, pos - PrevPos - 1))
        PrevPos = pos
        Dot2LongIP = ((num Mod 256) * (256 ^ (4 - i))) + Dot2LongIP
    next
end If

end function

There are many other reasons why you might it is useful to determine the country location of your site visitor:

Web log statistics and analysis

Providing localised contact information

Help identify possible credit card fraud

7 Needham Way, Skelmersdale, Lancashire, WN8 6PR 01695 720562

info@pssuk.com

Bespoke Software Development - Custom database & web business applications developer Lancashire North West, NW UK
Manchester, Liverpool, Warrington, St Helens, Southport, Preston, Blackburn, Bolton, Blackpool, Lancaster, Skelmersdale, Runcorn