More and more companies have started to take their businesses online which bring about significant cost reductions as opposed to traditional businesses. In addition, there is now an opportunity to take your products or services and market them to a larger audience of prospective customers from all over the world. To become a successful global player, online business owners need automatic IP-Country detection for localized currency type which will help them to boost their online sales by removing the foreign currency conversion barrier and converting their website visitors into buyers.
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 the mysql command prompt. Run the 2 SQL command below to create the ‘IP2Location’ database and select it for use
mysql> CREATE DATABASE IP2Location ;
mysql> CONNECT IP2Location;
Step 2: Next, run the SQL statement below to create the ‘IPCountry’ table
mysql> CREATE TABLE IPCountry
--> (
--> ipFROM DOUBLE NOT NULL,
--> ipTO DOUBLE NOT NULL,
--> countrySHORT VARCHAR(2) NOT NULL,
--> countryLONG VARCHAR(100) NOT NULL,
--> PRIMARY KEY(ipFROM, ipTO)
--> );
Step 3: Run the SQL codes below to insert dummy data into the database
mysql> INSERT INTO IPCountry VALUES (0, 2130706431,'US','UNITED STATES');
mysql> 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, the import process is easier by using the LOAD DATA feature available in MYSQL.
PC:
mysql> LOAD DATA INFILE "/IPCountry.csv" INTO TABLE IPCountry FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
Linux/Unix:
mysql> LOAD DATA INFILE "/IPCountry.csv" INTO TABLE IPCountry FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
Step 4: Run the SQL statement below to create the 'Countries' table
mysql> CREATE TABLE Countries
--> (
--> TLD VARCHAR(2) NOT NULL,
--> Country VARHCAR(100) NOT NULL,
--> FIPS104 VARCHAR(2) NOT NULL,
--> ISO2 VARCHAR(2) NOT NULL,
--> ISO3 VARCHAR(3) NOT NULL,
--> ISONo INT NOT NULL,
--> Capital VARCHAR(100) NOT NULL,
--> Region VARCHAR(100) NOT NULL,
--> Currency VARCHAR(50) NOT NULL,
--> CurrencyCode VARCHAR(3) NOT NULL,
--> Population DOUBLE NOT NULL,
--> );
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. The import process is same as the IP-Country database by using the LOAD DATA feature available in MYSQL.
You can use the SQL statement below to import the country information into the database.
mysql> LOAD DATA INFILE "/Countries.csv" INTO TABLE Countries FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r';
Below is a sample code in PHP which will show you how to use the data to determine the correct currency to display the pricing.
<?php
// Replace this MYSQL server variables with actual configuration
$mysql_server = "mysql_server.com";
$mysql_user_name = "UserName";
$mysql_user_pass = "Password";
// Retrieve visitor IP address from server variable REMOTE_ADDR
$ipaddress = getenv(REMOTE_ADDR);
// Convert IP address to IP number for querying database
$ipno = Dot2LongIP($ipaddress);
// Connect to the database server
$link = mysql_connect($mysql_server, $mysql_user_name, $mysql_user_pass) or die("Could not connect to MySQL database");
// Connect to the IP2Location database
mysql_select_db("IP2Location") or die("Could not select database");
// SQL query string to match the recordset that the IP number fall between the valid range
$query = "SELECT * FROM IPCountry WHERE $ipno <= ipTO AND $ipno>=ipFROM";
// Execute SQL query
$result = mysql_query($query) or die("IP2Location Query Failed");
// Retrieve the recordset (only one)
$row = mysql_fetch_object($result);
// Keep the country information into two different variables
$countrySHORT = $row->countrySHORT;
$countryLONG = $row->countryLONG;
// SQL query string to match the recordset that the TLD with Country Short Name
$query = "SELECT * FROM Countries WHERE TLD='$countrySHORT';
// Execute SQL query
$result = mysql_query($query) or die("IP2Location Query Failed");
// Retrieve the recordset (only one)
$row = mysql_fetch_object($result);
// Keep the Currency Code in a variable
$currencyCode = $row->CurrencyCode;
// Free recordset and close database connection
mysql_free_result($result); mysql_close($link);
// If the visitors are from JP
if ($countrySHORT == "JP")
{
//Print Price: YEN 120.00
echo “Price: $currentCode 120.00”;
}
else
{
// Otherwise
//Print USD 1.00
echo “Price: $currentCode 1.00”;
}
exit;
// Function to convert IP address (xxx.xxx.xxx.xxx) to IP number (0 to 256^4-1)
function Dot2LongIP ($IPaddr) {
if ($IPaddr == "")
{
return 0;
} else {
$ips = split ("\.", "$IPaddr");
return ($ips[3] + $ips[2] * 256 + $ips[1] * 256 * 256 + $ips[0] * 256 * 256 * 256);
}
}
?>
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 the mysql command prompt. Run the 2 SQL command below to create the ‘IP2Location’ database and select it for use
mysql> CREATE DATABASE IP2Location ;
mysql> CONNECT IP2Location;
Step 2: Next, run the SQL statement below to create the ‘IPCountry’ table
mysql> CREATE TABLE IPCountry
--> (
--> ipFROM DOUBLE NOT NULL,
--> ipTO DOUBLE NOT NULL,
--> countrySHORT VARCHAR(2) NOT NULL,
--> countryLONG VARCHAR(100) NOT NULL,
--> PRIMARY KEY(ipFROM, ipTO)
--> );
Step 3: Run the SQL codes below to insert dummy data into the database
mysql> INSERT INTO IPCountry VALUES (0, 2130706431,'US','UNITED STATES');
mysql> 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, the import process is easier by using the LOAD DATA feature available in MYSQL.
PC:
mysql> LOAD DATA INFILE "/IPCountry.csv" INTO TABLE IPCountry FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
Linux/Unix:
mysql> LOAD DATA INFILE "/IPCountry.csv" INTO TABLE IPCountry FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
Step 4: Run the SQL statement below to create the 'Countries' table
mysql> CREATE TABLE Countries
--> (
--> TLD VARCHAR(2) NOT NULL,
--> Country VARHCAR(100) NOT NULL,
--> FIPS104 VARCHAR(2) NOT NULL,
--> ISO2 VARCHAR(2) NOT NULL,
--> ISO3 VARCHAR(3) NOT NULL,
--> ISONo INT NOT NULL,
--> Capital VARCHAR(100) NOT NULL,
--> Region VARCHAR(100) NOT NULL,
--> Currency VARCHAR(50) NOT NULL,
--> CurrencyCode VARCHAR(3) NOT NULL,
--> Population DOUBLE NOT NULL,
--> );
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. The import process is same as the IP-Country database by using the LOAD DATA feature available in MYSQL.
You can use the SQL statement below to import the country information into the database.
mysql> LOAD DATA INFILE "/Countries.csv" INTO TABLE Countries FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r';
Below is a sample code in PHP which will show you how to use the data to determine the correct currency to display the pricing.
<?php
// Replace this MYSQL server variables with actual configuration
$mysql_server = "mysql_server.com";
$mysql_user_name = "UserName";
$mysql_user_pass = "Password";
// Retrieve visitor IP address from server variable REMOTE_ADDR
$ipaddress = getenv(REMOTE_ADDR);
// Convert IP address to IP number for querying database
$ipno = Dot2LongIP($ipaddress);
// Connect to the database server
$link = mysql_connect($mysql_server, $mysql_user_name, $mysql_user_pass) or die("Could not connect to MySQL database");
// Connect to the IP2Location database
mysql_select_db("IP2Location") or die("Could not select database");
// SQL query string to match the recordset that the IP number fall between the valid range
$query = "SELECT * FROM IPCountry WHERE $ipno <= ipTO AND $ipno>=ipFROM";
// Execute SQL query
$result = mysql_query($query) or die("IP2Location Query Failed");
// Retrieve the recordset (only one)
$row = mysql_fetch_object($result);
// Keep the country information into two different variables
$countrySHORT = $row->countrySHORT;
$countryLONG = $row->countryLONG;
// SQL query string to match the recordset that the TLD with Country Short Name
$query = "SELECT * FROM Countries WHERE TLD='$countrySHORT';
// Execute SQL query
$result = mysql_query($query) or die("IP2Location Query Failed");
// Retrieve the recordset (only one)
$row = mysql_fetch_object($result);
// Keep the Currency Code in a variable
$currencyCode = $row->CurrencyCode;
// Free recordset and close database connection
mysql_free_result($result); mysql_close($link);
// If the visitors are from JP
if ($countrySHORT == "JP")
{
//Print Price: YEN 120.00
echo “Price: $currentCode 120.00”;
}
else
{
// Otherwise
//Print USD 1.00
echo “Price: $currentCode 1.00”;
}
exit;
// Function to convert IP address (xxx.xxx.xxx.xxx) to IP number (0 to 256^4-1)
function Dot2LongIP ($IPaddr) {
if ($IPaddr == "")
{
return 0;
} else {
$ips = split ("\.", "$IPaddr");
return ($ips[3] + $ips[2] * 256 + $ips[1] * 256 * 256 + $ips[0] * 256 * 256 * 256);
}
}
?>
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.
