Online advertising is another way to promote company products. It’s
very important to show the right advertisements to the right consumers to have
an optimum respond. A company selling their products in Japan showing their
advertisement to visitors from United States is totally ineffective. On the
other hand, localized advertisements catch visitor attention and improve sales.
In this tutorial, we use the IP2Location™IP-Country database to lookup country
of origin from the visitor's IP address. Instead of loading the full database
with 50000+ records, we could simplify this tutorial with assumption only two
different IP address ranges in the world. IP addresses 0.0.0.0 -
126.255.255.255 originate from United States. Meanwhile, IP addresses 127.0.0.0
- 255.255.255.255 originate from Japan. Here we are creating a database
"IP2Location" with table "IPCountry" that consists of two IP address range
records.
Step 1: Start SQL Query Analyzer. Create and connect to 'IP2Location' database
CREATE DATABASE IP2Location;
USE IP2Location;
Step 2: Create 'IPCountry' table
CREATE TABLE [dbo].[IPCountry] (
[ipFROM] [float] NOT NULL ,
[ipTO] [float] NOT NULL ,
[countrySHORT] [nvarchar] (2),
[countryLONG] [nvarchar] (64)
) ON [PRIMARY]
GO
Step 3. Insert dummy records to 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://ip2location.com.
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.
<%
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 JP
response.write "<img src=\"Japan.jpg\" border=\"0\" width=\"100\" height=\"200\">"
Else
' Visitor is not from Japan
' Show other advertisement
response.write "<img src=\"US.jpg\" border=\"0\" width=\"100\" height=\"200\">"
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
%>