Display the nearest cities from visitor’s IP address using PHP and MySQL

In this tutorial, we demonstrate you on how to display the visitor’s current location and nearest city or cities based on their IP address using PHP programming languages, MySQL database and GeoDataSource™ Web Service.

First, we use IP2Location™ LITE database to lookup the latitude and logitude from the visitor’s IP address. Then we integrate with GeoDataSource™ Location Search Web Service to lookup for the nearest city/cities name by using latitude and longitude coordinate returned in the first lookup result.

Great data is an essential part of many apps built today. So, we recommend starting with the IP2Location™ DB11 LITE  IP-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE Database which is free! Unzip the datafile and import it into your MySQL table after you have downloaded it from https://lite.ip2location.com/database/ip-country-region-city-latitude-longitude-zipcode-timezone.

The GeoDataSource™ Location Search Web Service we use here is free too! For new user, before you can start using the this web service, you must sign up and get an API key first. Grab it at https://www.geodatasource.com/web-service/location-search.

Follow the steps below to set up the database and to perform the IP address to nearest city lookup.

Step 1: Create ‘ip2location_db11’ table.

CREATE DATABASE ip2location;
USE ip2location;
CREATE TABLE `ip2location_db11`(
	`ip_from` INT(10) UNSIGNED,
	`ip_to` INT(10) UNSIGNED,
	`country_code` CHAR(2),
	`country_name` VARCHAR(64),
	`region_name` VARCHAR(128),
	`city_name` VARCHAR(128),
	`latitude` DOUBLE,
	`longitude` DOUBLE,
	`zip_code` VARCHAR(30),
	`time_zone` VARCHAR(8),
	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 2: Import the data into table ‘ip2location_db11’.

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

Step 3: Sign up and download GeoDataSource™ Location Search Web Service to get your API key.

Step 4: Create php file nearestcity.php and run the sample codes.

<!DOCTYPE html>
<head>
<title>Nearest City / Cities</title>
</head>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>

<!--Toggle button to show and hide the output-->
<script>
$(document).ready(function() {
  $("#b2").click(function() {
    $("#cities").toggle();
  });
});
</script>
<body>
<!--Let visitor enters their IP address -->
<form action="nearestcity.php" method="post">
IP Address: <input type="text" name="ipaddress"><br>
<input type="submit" name="submit">
</form>
<div>
<?php

	//MySQL server configuration
	$server = "host";
	$username = "username";
	$password = "password";
	$database = "ip2location";
	
	//Connect to database server
	$conn = mysqli_connect($server, $username, $password, $database) or die("Could not connect to MySQL database");
	
	//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);
		}
	}
	
	if(isset($_POST['submit'])){
		//Retrieve visitor IP address
		$ipaddress = $_POST["ipaddress"];
	
		//Convert IP address to IP number for querying database
		$ipnum = Dot2LongIP ($ipaddress);
		
		//SQL query string to match the recordset that the IP number fall between the valid range
		$query = "SELECT * FROM ip2location_db11 WHERE $ipnum <= ip_to LIMIT 1";
	
		//Execute SQL query
		$result = mysqli_query($conn, $query) or die("IP2Location Query Failed");
		
		while($row = mysqli_fetch_assoc($result)){
			echo "<h3>Your current location: </h3>";
			echo "Country Code: " . $row['country_code'] . "<br>";
			echo "Country Name: " . $row['country_name'] . "<br>";
			echo "Region: " . $row['region_name'] . "<br>";
			echo "City: " . $row['city_name'] . "<br>";
			echo "Latitude: " . $row['latitude'] . "<br>";
			echo "Longitude: " . $row['longitude'] . "<br>";
			echo "Zip Code: " . $row['zip_code'] . "<br>";
			echo "Time Zone: " . $row['time_zone'] . "<br>";
			
			//Get the data of city & cities
			$apiKey = 'ENTER_YOUR_KEY';
			$params['format']   = 'json';
			$params['lat']      = $row['latitude'];
			$params['lng']      = $row['longitude'];
	
			$query2 = '';
	
			foreach($params as $key=>$value){
				$query2 .= '&' . $key . '=' . rawurlencode($value);
			}
	
			$rawJson = file_get_contents('https://api.geodatasource.com/city?key=' . $apiKey . $query2);
			$datas = array(json_decode ($rawJson));
	
			$rawJson2 = file_get_contents('https://api.geodatasource.com/cities?key=' . $apiKey . $query2);
			$datas2 = json_decode ($rawJson2);
			
			//One city
			echo "<h3>Nearest City</h3>";
			foreach($datas as $data):
				echo "<li>" . $data->city . "</li>";
			endforeach;
			
			//More cities
			echo "<h3>Nearest Cities</h3>";
			echo '<button id="b2" onClick="cities()">Your nearest cities</button>';
		}
	}
?>
<p id="cities" style="display:none"></p>
<script>
function cities(){
document.getElementById("cities").innerHTML
= "<?php 
foreach($datas2 as $data2):
    echo "<li>" . $data2->city . "</li><br>";
endforeach; 
?>" ;
}
</script>
</body>
</html>

 

Was this article helpful?

Related Articles