More and more companies are realizing the importance of doing business online as a way of improving their market share. With this realization comes the need to deal with customers from around the world and their various currencies. This is when being able to show the pricing on a website in the visitor’s local currency would make the online shopping experience more user-friendly; eliminating the usual headache of foreign currency conversion.
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.
Step 4: Run the SQL statement below to create the 'Countries' table
CREATE TABLE [dbo].[Countries] (
[TLD] [nvarchar] (3),
[Country][nvarchar] (100),
[FIPS104][nvarchar] (2),
[ISO2] [nvarchar] (2),
[IOS3] [nvarchar] (3),
[Capital] [nvarchar] (100),
[Region] [nvarchar] (100),
[Currency] [nvarchar] (50),
[CurrencyCode] [nvarchar] (3),
[Population] [int]
) ON [PRIMARY]
GO
The country information is available for FREE at http://www.ip2location.com/countryinformation.aspx . It consists of the top level domain (TLD), ISO-3166, country, capital, region, currency, currency code and population. Download and load the data into your database. It's available in several formats such as Comma-delimited ASCII, Microsoft® Access & Microsoft® Excel.
In your ASP page, you will need to put in the codes below.
<%
Dim conn, myDSN, mySQL, rs, mySQL2, rs2
' 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")
' SQL query to lookup currency code
mySQL2 = "SELECT CurrencyCode FROM Countries WHERE TLD='" & countryName & "'"
Set rs2 = Server.CreateObject("ADODB.Recordset")
rs2.open mySQL2, conn
' assign currency code for reference
currencyCode = rs("currencyCode")
' close and free connections
rs.close
rs2.close
conn.close
set rs = nothing
set rs2 = nothing
Set conn = nothing
If CountryName = "JP" Then ' Visitor is from Japan
' Show price in YEN
Response.Write "Price: " & currencyCode & " 120.00"
Else ' Visitor is not from Japan
' Show price in USD
Response.Write "Price: " & currencyCode & " 1.00"
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 %>
What you have seen here today is just the tip of the iceberg of what you can do with the IP2Location™IP-Country database. You could even pair the code above with a forex service to get a real-time conversion rate for the currencies thus making your currency conversions even more up-to-date. Now that you have realized the power of the IP2Location™IP-Country database, you will benefit even more from the more advanced databases available from IP2Location. 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.
Step 4: Run the SQL statement below to create the 'Countries' table
CREATE TABLE [dbo].[Countries] (
[TLD] [nvarchar] (3),
[Country][nvarchar] (100),
[FIPS104][nvarchar] (2),
[ISO2] [nvarchar] (2),
[IOS3] [nvarchar] (3),
[Capital] [nvarchar] (100),
[Region] [nvarchar] (100),
[Currency] [nvarchar] (50),
[CurrencyCode] [nvarchar] (3),
[Population] [int]
) ON [PRIMARY]
GO
The country information is available for FREE at http://www.ip2location.com/countryinformation.aspx . It consists of the top level domain (TLD), ISO-3166, country, capital, region, currency, currency code and population. Download and load the data into your database. It's available in several formats such as Comma-delimited ASCII, Microsoft® Access & Microsoft® Excel.
In your ASP page, you will need to put in the codes below.
<%
Dim conn, myDSN, mySQL, rs, mySQL2, rs2
' 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")
' SQL query to lookup currency code
mySQL2 = "SELECT CurrencyCode FROM Countries WHERE TLD='" & countryName & "'"
Set rs2 = Server.CreateObject("ADODB.Recordset")
rs2.open mySQL2, conn
' assign currency code for reference
currencyCode = rs("currencyCode")
' close and free connections
rs.close
rs2.close
conn.close
set rs = nothing
set rs2 = nothing
Set conn = nothing
If CountryName = "JP" Then ' Visitor is from Japan
' Show price in YEN
Response.Write "Price: " & currencyCode & " 120.00"
Else ' Visitor is not from Japan
' Show price in USD
Response.Write "Price: " & currencyCode & " 1.00"
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 %>
What you have seen here today is just the tip of the iceberg of what you can do with the IP2Location™IP-Country database. You could even pair the code above with a forex service to get a real-time conversion rate for the currencies thus making your currency conversions even more up-to-date. Now that you have realized the power of the IP2Location™IP-Country database, you will benefit even more from the more advanced databases available from IP2Location. You can try out the online demo at http://www.ip2location.com/demo.aspx and see for yourself what you are missing.
