Display Visitor’s Local Time Using PHP and MySQL Database

In this tutorial, we demonstrate you on how to display visitor’s local time based on their IP address using PHP programming languages and IP2Location MySQL database. In this tutorial, we use the IP2Location LITE database to lookup country of origin from the visitor’s IP address. Free databases are available for download at IP2Location LITE database.

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

Step 1: Download IP2Location DB11 LITE database, unzip the file follow the instruction in order to create database table.

Step 2: Run the below 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
		$ip = $_SERVER['REMOTE_ADDR'];

		// Convert IP address to IP number for querying database
		$ipno = Dot2LongIP($ip);
		
		// 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 time_zone, country_code FROM ip2location_db11 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 time zone and country code as variables
		$country_code = $row['country_code'];
		$time_zone = $row['time_zone'];
		
		// Free recordset and close database connection
		mysqli_free_result($result); 
		mysqli_close($link);

		$utc_time = gmdate("H:i:s");
		$utc_h = explode(':', $utc_time);

		if (strcmp($time_zone, "-") == 0) {
			$localdate = $utc_time;
		}
		else {
			$time = explode(':', $time_zone);

			if ($utc_h[0] + $time[0] < 0) {
				$hour = $utc_h[0] + 24 + $time[0];
			}
			elseif ($utc_h[0] + $time[0] >= 24) {
				$hour = $utc_h[0] + $time[0] - 24;
			}
			else {
				$hour = $utc_h[0] + $time[0];
			}

			$localdate = $hour . gmdate(":i:s");
		}

		date_default_timezone_set("Asia/Kuala_Lumpur");
		$date = date_create($localdate);
		echo 'Country Code: ' . $country_code . '<br />';
		echo 'Local Time: ' . date_format($date, "H:i:s"). '<br />';
		echo 'Server Time: ' . strftime("%H:%M:%S") . '<br />';
		echo 'UTC Time: ' . gmdate("H:i:s") . '<br />';

		// 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: Download IP2Location DB11 LITE database, unzip the file follow the instruction in order to create database table.

Step 2: Run the below 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
		$ip = $_SERVER['REMOTE_ADDR'];

		// Convert IP address to IP number for querying database
		$ipno = Dot2LongIPv6($ip);

		// 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 time_zone, country_code FROM ip2location_db11 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 time zone and country code as variables
		$country_code = $row['country_code'];
		$time_zone = $row['time_zone'];
		
		// Free recordset and close database connection
		mysqli_free_result($result); 
		mysqli_close($link);

		$utc_time = gmdate("H:i:s");
		$utc_h = explode(':', $utc_time);

		if (strcmp($time_zone, "-") == 0) {
			$localdate = $utc_time;
		}
		else {
			$time = explode(':', $time_zone);

			if ($utc_h[0] + $time[0] < 0) {
				$hour = $utc_h[0] + 24 + $time[0];
			}
			elseif ($utc_h[0] + $time[0] >= 24) {
				$hour = $utc_h[0] + $time[0] - 24;
			}
			else {
				$hour = $utc_h[0] + $time[0];
			}

			$localdate = $hour . gmdate(":i:s");
		}

		date_default_timezone_set("Asia/Kuala_Lumpur");
		$date = date_create($localdate);
		echo 'Country Code: ' . $country_code . '<br />';
		echo 'Local Time: ' . date_format($date, "H:i:s"). '<br />';
		echo 'Server Time: ' . strftime("%H:%M:%S") . '<br />';
		echo 'UTC Time: ' . gmdate("H:i:s") . '<br />';

		// 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;
		}
	?>

Was this article helpful?

Related Articles