Online advertisements are a big revenue earner for most websites. So it is no wonder that most of them employ ways to detect their web visitor’s country so that the advertisements can be targeted to its niche audience. By doing so, the probability of a sale from the online advertisement increases tremendously as the targeted audience would most likely find that advertisement to be relevant and maybe even helpful.
In this tutorial, we will use the IP2Location™IP-Country database to determine the country of origin for the web visitor just by checking the IP address. Instead of loading the full database with 50000+ records, we could simplify this tutorial with the assumption that only two different IP address ranges in the world. IP addresses of the range 0.0.0.0 - 126.255.255.255 originates from the United States. Meanwhile, IP addresses of the range 127.0.0.0 - 255.255.255.255 originates from Japan. Here we are creating a database called "IP2Location" with a table called "IPCountry" which contains our two IP address range records.
Step 1: Start Microsoft SQL Server Management Studio.
Now, login to your database instance and then click on the New Query button. Copy the 2 lines of SQL codes below into the query window and press Execute to run the codes. This will create the ‘IP2Location’ database and select it for use.
CREATE DATABASE IP2Location
GO
USE IP2Location
GO
Step 2: Next, run the SQL statement below to create the ‘IPCountry’ table.
CREATE TABLE[dbo].[IPCountry] (
[ipFROM] [float] NOTNULL ,
[ipTO] [float] NOT NULL ,
[countrySHORT] [nvarchar] (2),
[countryLONG] [nvarchar] (64)
) ON [PRIMARY]
GO
Step 3: Run the SQL codes below to insert dummy data into the database.
INSERT INTO IPCountry VALUES (0, 2130706431,'US','UNITED STATES');
INSERT INTO IPCountry VALUES (2130706432, 4294967295,'JP','JAPAN');
The full version of IP-Country database is available for subscription at $49/year from http://www.ip2location.com/ip-country.aspx. If you have the full version of IP2Location™IP-Country database, import process is easy done by using the Database Transformation Service (DTS) in MS-SQL.
In your ASP page, you will need to put in the codes below.
<%
Dim conn, myDSN, mySQL, rs
' SQL database connection. NOTE: Replace servername, username and password to your own values.
Set conn = Server.CreateObject("ADODB.Connection")
myDSN="DRIVER={SQLServer};SERVER=servername;UID=username;PWD=password;DATABASE=IP2Location"
conn.open myDNS
' retrieve visitor IP address and translate it to IP address number
IPno = Dot2LongIP(Request.ServerVariables("REMOTE_ADDR"))
' SQL query to lookup valid range of IP address
mySQL = "SELECT countrySHORT FROM IPCountry WHERE " & IPno & " BETWEEN ipFROM AND ipTO"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.open mySQL, conn
' assign country name for reference
countryName = rs("countrySHORT")
' close and free connections
rs.close
conn.close
set rs = nothing
Set conn = nothing
If CountryName = "JP" Then
' Visitor is from Japan
' Show advertisement from Japan
response.write "<img src=""Japan.jpg"" border=""0"" height=""200"" width=""100"">"
Else
' Visitor is not from Japan
' Show other advertisement
response.write "<img src=""US.jpg"" border=""0"" height=""200"" width=""100"">"
End If
%>
Function Dot2LongIP (ByVal DottedIP)
Dim i, pos
Dim PrevPos, num
If DottedIP = "" Then
Dot2LongIP = 0
Else
For i = 1 To 4
pos = InStr(PrevPos + 1, DottedIP, ".", 1)
If i = 4 Then
pos = Len(DottedIP) + 1
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
%>
IP2Location also carries a wide variety of databases which are more advanced and has more types of data which could also be used to further narrow down your target audience. You can try out the online demo at http://www.ip2location.com/demo.aspx and see for yourself what you are missing.
In this tutorial, we will use the IP2Location™IP-Country database to determine the country of origin for the web visitor just by checking the IP address. Instead of loading the full database with 50000+ records, we could simplify this tutorial with the assumption that only two different IP address ranges in the world. IP addresses of the range 0.0.0.0 - 126.255.255.255 originates from the United States. Meanwhile, IP addresses of the range 127.0.0.0 - 255.255.255.255 originates from Japan. Here we are creating a database called "IP2Location" with a table called "IPCountry" which contains our two IP address range records.
Step 1: Start Microsoft SQL Server Management Studio.
Now, login to your database instance and then click on the New Query button. Copy the 2 lines of SQL codes below into the query window and press Execute to run the codes. This will create the ‘IP2Location’ database and select it for use.
CREATE DATABASE IP2Location
GO
USE IP2Location
GO
Step 2: Next, run the SQL statement below to create the ‘IPCountry’ table.
CREATE TABLE[dbo].[IPCountry] (
[ipFROM] [float] NOTNULL ,
[ipTO] [float] NOT NULL ,
[countrySHORT] [nvarchar] (2),
[countryLONG] [nvarchar] (64)
) ON [PRIMARY]
GO
Step 3: Run the SQL codes below to insert dummy data into the database.
INSERT INTO IPCountry VALUES (0, 2130706431,'US','UNITED STATES');
INSERT INTO IPCountry VALUES (2130706432, 4294967295,'JP','JAPAN');
The full version of IP-Country database is available for subscription at $49/year from http://www.ip2location.com/ip-country.aspx. If you have the full version of IP2Location™IP-Country database, import process is easy done by using the Database Transformation Service (DTS) in MS-SQL.
In your ASP page, you will need to put in the codes below.
<%
Dim conn, myDSN, mySQL, rs
' SQL database connection. NOTE: Replace servername, username and password to your own values.
Set conn = Server.CreateObject("ADODB.Connection")
myDSN="DRIVER={SQLServer};SERVER=servername;UID=username;PWD=password;DATABASE=IP2Location"
conn.open myDNS
' retrieve visitor IP address and translate it to IP address number
IPno = Dot2LongIP(Request.ServerVariables("REMOTE_ADDR"))
' SQL query to lookup valid range of IP address
mySQL = "SELECT countrySHORT FROM IPCountry WHERE " & IPno & " BETWEEN ipFROM AND ipTO"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.open mySQL, conn
' assign country name for reference
countryName = rs("countrySHORT")
' close and free connections
rs.close
conn.close
set rs = nothing
Set conn = nothing
If CountryName = "JP" Then
' Visitor is from Japan
' Show advertisement from Japan
response.write "<img src=""Japan.jpg"" border=""0"" height=""200"" width=""100"">"
Else
' Visitor is not from Japan
' Show other advertisement
response.write "<img src=""US.jpg"" border=""0"" height=""200"" width=""100"">"
End If
%>
Function Dot2LongIP (ByVal DottedIP)
Dim i, pos
Dim PrevPos, num
If DottedIP = "" Then
Dot2LongIP = 0
Else
For i = 1 To 4
pos = InStr(PrevPos + 1, DottedIP, ".", 1)
If i = 4 Then
pos = Len(DottedIP) + 1
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
%>
IP2Location also carries a wide variety of databases which are more advanced and has more types of data which could also be used to further narrow down your target audience. You can try out the online demo at http://www.ip2location.com/demo.aspx and see for yourself what you are missing.
