Why redirect web visitors based on country

There are times when it is useful to redirect a visitor to different default web page based on the visitor’s country of origin. One practical usage is to redirect visitor to web page with the language recognized by the visitor. This article shows you how to build up such a system using ASP (server side scripting language) and MS-SQL (IP address to country lookup database).

Let us take a simple case study. Company XYZ is multi-national company with major customers from United States and Japan. The company official website is developed in both English and Japanese languages. The default page is in English language and visitor can switch to Japanese by changing the default language option. There exists a potential risk when a Japanese visitor does not understand English and he could not navigate the web site.

So let us developed a simple solution to help Company XYZ redirecting all Internet traffic from country Japan to the Japanese language site. Meanwhile it drives the traffic from the rest of the world to the corporate web site in English.

How to redirect web visitors based on country

In this tutorial, we use the IP2Location™ IP-Country database to lookup country of origin from the visitor’s IP address.

Step 1: Start the SQL Server Management Studio. Run the following SQL commands.

CREATE DATABASE ip2location;
USE ip2location;

Step 2: Create ‘ip_country’ table

CREATE TABLE [ip2location].[dbo].[ip_country] (
            [ip_from] bigint NOT NULL,
            [ip_to] bigint NOT NULL,
            [country_code] nvarchar(2) NOT NULL,
            [country_name] nvarchar(64) NOT NULL
            ) ON [PRIMARY]
            GO

Step 3. Import the data into the table.

BULK INSERT [ip2location].[dbo].[ip_country]
    FROM 'C:\[path to your CSV file]\IPCountry.CSV'
    WITH
    (
        FORMATFILE = 'C:\[path to your DB1.FMT file]\DB1.FMT'
    )
GO

NOTE: You will need to copy the FMT code below and save it as a file named DB1.FMT on your computer.

10.0
5
1 SQLCHAR 0 1 “\”” 0 first_double_quote Latin1_General_CI_AI
2 SQLCHAR 0 20 “\”,\”” 1 ip_from “”
3 SQLCHAR 0 20 “\”,\”” 2 ip_to “”
4 SQLCHAR 0 2 “\”,\”” 3 country_code Latin1_General_CI_AI
5 SQLCHAR 0 64 “\”\r\n” 4 country_name Latin1_General_CI_AI
Run the below SQL command to create a clustered index on the ip_to field.

CREATE UNIQUE CLUSTERED INDEX [ip_to] ON [ip2location].[dbo].[ip_country]
(
    [ip_to] ASC
) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

The full version of IP-Country database is available for subscription at $49/year at https://ip2location.com/databases/db1-ip-country.

Sample Codes:

<%
  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 myDSN
 
  ' 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 TOP 1 * FROM [ip2location].[dbo].[ip_country] WHERE ip_to >=" & IPno & " ORDER BY ip_to"
 
  Set rs = Server.CreateObject("ADODB.Recordset")
  rs.open mySQL, conn
 
  ' assign country code for reference
  countryCode = rs("country_code")
 
  ' close and free connections
  rs.close
  conn.close
  set rs = nothing
  Set conn = nothing
 
  If countryCode = "JP" Then
  ' Visitor is from Japan
  ' Redirect the URL to Google Japanese site
  Response.Redirect "http://www.google.co.jp"
  Else
  ' Visitor is not from Japan
  ' Redirect the URL to Google international site
  Response.Redirect "http://www.google.com"
  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
%>