ip2location facebbook  ip2location twitter  ip2location google+ ip2location github

Display Visitor's Country Currency Using PHP and MySQL Database

The number of companies moved their business online is increasing. Selling products to world wide is dealing with multi national customers and currencies. However, some customers may have difficulty to convert the currency displayed to their origin currency. It’s user friendly to show the pricing in a website in visitor origin currency. With the origin currency, visitors can navigate faster and feel comfortable to make comparison.

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 "ip_country" that consists of two IP address range records.

Below are the steps to set up the database for both IPv4 and IPv6 data and the sample codes

Step 1: Create and connect to 'ip2location' database

			CREATE DATABASE ip2location;
			USE ip2location;
			

Step 2: Create 'ip_country' table

			CREATE TABLE `ip_country`(
			`ip_from` INT(10) UNSIGNED,
			`ip_to` INT(10) UNSIGNED,
			`country_code` CHAR(2),
			`country_name` VARCHAR(64),
			INDEX `idx_ip_from` (`ip_from`),
			INDEX `idx_ip_to` (`ip_to`),
			INDEX `idx_ip_from_to` (`ip_from`, `ip_to`)
			) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
			

Step 3. Import the IP2Location LITE IP-COUNTRY Database into table ‘ip_country’, you can get the Lite database at https://lite.ip2location.com/database/ip-country

			LOAD DATA LOCAL	
			INFILE 'IP2LOCATION-LITE-DB1.CSV'
			INTO TABLE	`ip_country`
			FIELDS TERMINATED BY ','
			ENCLOSED BY '"'
			LINES TERMINATED BY '\r\n'
			IGNORE 0 LINES;
			

The full version of IP-Country database is available for subscription at $49/year from https://www.ip2location.com/databases/db1-ip-country. 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.

Step 4: Download country information at here. Create ip2location_country_information table:

			CREATE TABLE `ip2location_country_information`(
			`country_code` CHAR(2),
			`capital` VARCHAR(50),
			`total_area` DOUBLE,
			`population` INT(10) UNSIGNED,
			`idd_code` VARCHAR(5),
			`currency_code` CHAR(3),
			`currency_name` VARCHAR(50),
			`lang_code` CHAR(2),
			`lang_name` VARCHAR(50),
			`cctld` CHAR(2),
			INDEX `idx_country_code` (`country_code`)
			) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
 
			LOAD DATA LOCAL
			INFILE 'IP2LOCATION-COUNTRY-INFORMATION.CSV' INTO TABLE `ip2location_country_information`
			CHARACTER SET latin1
			FIELDS TERMINATED BY ','
			ENCLOSED BY '"'
			LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
			

Sample Code:

	<?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 = $_SERVER['REMOTE_ADDR'];

		// Convert IP address to IP number for querying database
		$ipno = Dot2LongIP($ipaddress);

		// Connect to the database server
		$link = mysqli_connect($mysql_server, $mysql_user_name, $mysql_user_pass) or die("Could not connect to MySQL database");

		// Connect to the IP2Location database
		mysqli_select_db($link,"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 ip_country WHERE $ipno <= ip_to LIMIT 1";


		// Execute SQL query
		$result = mysqli_query($link,$query) or die("IP2Location Query Failed");

		// Retrieve the recordset (only one)
		$row = mysqli_fetch_assoc($result);

		// Keep the country information into two different variables
		$country_code = $row['country_code'];
		$country_name = $row['country_name'];

		// SQL query string to match the recordset that the tld with Country Short Name
		$query = "SELECT * FROM ip2location_country_information WHERE country_code ='$country_code'";


		// Execute SQL query
		$result = mysqli_query($link,$query) or die("IP2Location Query Failed");

		// Retrieve the recordset (only one)
		$row = mysqli_fetch_assoc($result);

		// Keep the Currency name in a variable
		$currency_name = $row['currency_name'];

		// Free recordset and close database connection
		mysqli_free_result($result); 
		mysqli_close($link);

		// Display currency information
			echo "The IP address : $ipaddress<br/><br/>";
			echo "Visitor From : $country_name<br/><br/>";
			echo "Your Currency is : $currency_name<br/><br/>";
			
			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 = explode(".", $IPaddr);
		   return ($ips[3] + $ips[2] * 256 + $ips[1] * 256 * 256 + $ips[0] * 256 * 256 * 256);
		 }
		}

	?>
		

 

Step 1: Create and connect to 'ip2location' database

		CREATE DATABASE ip2location;
		USE ip2location;
		

Step 2: Create 'ip_country' table

			CREATE TABLE `ip_country`(
			`ip_from` DECIMAL(39,0) UNSIGNED,
			`ip_to` DECIMAL(39,0) UNSIGNED,
			`country_code` CHAR(2),
			`country_name` VARCHAR(64),
			INDEX `idx_ip_from` (`ip_from`),
			INDEX `idx_ip_to` (`ip_to`),
			INDEX `idx_ip_from_to` (`ip_from`, `ip_to`)
			) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
		

Step 3. Import the IP2Location LITE IP-COUNTRY IPV6 Database into table ‘ip_country’, you can get the Lite database at https://lite.ip2location.com/database/ip-country

		
		LOAD DATA LOCAL	
		INFILE 'IP2LOCATION-LITE-DB1.IPV6.CSV'
		INTO TABLE	`ip_country`
		FIELDS TERMINATED BY ','
		ENCLOSED BY '"'
		LINES TERMINATED BY '\r\n'
		IGNORE 0 LINES;
		

The full version of IP-Country database is available for subscription at $49/year from https://www.ip2location.com/databases/db1-ip-country. 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.

Step 4: Download country information at here. Create ip2location_country_information table:

		CREATE TABLE `ip2location_country_information`(
		`country_code` CHAR(2),
		`capital` VARCHAR(50),
		`total_area` DOUBLE,
		`population` INT(10) UNSIGNED,
		`idd_code` VARCHAR(5),
		`currency_code` CHAR(3),
		`currency_name` VARCHAR(50),
		`lang_code` CHAR(2),
		`lang_name` VARCHAR(50),
		`cctld` CHAR(2),
		INDEX `idx_country_code` (`country_code`)
		) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

		LOAD DATA LOCAL
		INFILE 'IP2LOCATION-COUNTRY-INFORMATION.CSV' INTO TABLE `ip2location_country_information`
		CHARACTER SET latin1
		FIELDS TERMINATED BY ','
		ENCLOSED BY '"'
		LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
		

Sample Code:

	<?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 = $_SERVER['REMOTE_ADDR'];

		// Convert IP address to IP number for querying database
		$ipno = Dot2LongIPv6($ipaddress);
		
		// Connect to the database server
		$link = mysqli_connect($mysql_server, $mysql_user_name, $mysql_user_pass) or die("Could not connect to MySQL database");

		// Connect to the IP2Location database
		mysqli_select_db($link,"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 ip_country WHERE $ipno <?= ip_to LIMIT 1";
		
		// Execute SQL query
		$result = mysqli_query($link,$query) or die("IP2Location Query Failed");

		// Retrieve the recordset (only one)
		$row = mysqli_fetch_assoc($result);

		// Keep the country information into two different variables
		$country_code = $row['country_code'];
		$country_name = $row['country_name'];

		// SQL query string to match the recordset that the tld with Country Short Name
		$query = "SELECT * FROM ip2location_country_information WHERE country_code ='$country_code'";
				
		// Execute SQL query
		$result = mysqli_query($link,$query) or die("IP2Location Query Failed");

		// Retrieve the recordset (only one)
		$row = mysqli_fetch_assoc($result);

		// Keep the Currency name in a variable
		$currency_name = $row['currency_name'];
		
		// Free recordset and close database connection
		mysqli_free_result($result); 
		mysqli_close($link);

		// Display currency information
		echo "The IP address : $ipaddress<?br/><?br/>";
		echo "Visitor From : $country_name<?br/><?br/>";
		echo "Your Currency is : $currency_name<?br/><?br/>";
		
		exit;

		// Function to convert IP address to IP number (IPv6)
		function Dot2LongIPv6 ($IPaddr) {
			$int = inet_pton($IPaddr);
			$bits = 15;
			$ipv6long = 0;
			while($bits >= 0){
				$bin = sprintf("%08b", (ord($int[$bits])));
				if($ipv6long){
					$ipv6long = $bin . $ipv6long;
				}
				else{
					$ipv6long = $bin;
				}
				$bits--;
			}
			$ipv6long = gmp_strval(gmp_init($ipv6long, 2), 10);
			return $ipv6long;					
		}
	?>

 


Do you like this article? Share it with others by clicking the social media buttons below. We will write more articles related to this topic.