ip2location facebbook  ip2location twitter  ip2location google+ ip2location github

IP Address Lookup in Bulk Using PHP and MySQL Database

In this tutorial, we demonstrate you on how to lookup IP address in bulk 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 LITE database, unzip the file follow the instruction in order to create database table. Please refer to DB11 LITE for further information.

Step 2: Run the below sample code.

	<?php

		// MYSQL configuration
		$mysql_server = "mysql_server.com";
		$mysql_user_name ="User ID";
		$mysql_user_pass ="password";

		// 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");

		//Display for user input
		echo "<html>";
		echo "<head><title>IP Query in Bulk</title></head>";
		echo "<body>
				<span>Upload IP list for validation:</span><br/><br/>
				<form action='' method='post' enctype='multipart/form-data'>
				<input name='uploaded_file' type='file' value='' /><br>
				<input type='submit' name='submit' value='Upload & Process' />
				</form>
			</body>";
		echo "</html>";


		//File submitted
		if(isset($_POST['submit']))
		{
			//Check for file error
			if($_FILES["uploaded_file"]["error"] > 0)
			{
				echo "Error :" .$_FILES["uploaded_file"]["error"]. "<br>";
			}
			else
			{
				echo "Input File Path :" , realpath(dirname(__FILE__))  ;
				echo "<br>";
				echo "File Name : " .$_FILES["uploaded_file"]["name"]. "<br>";
				echo "Type : " .$_FILES["uploaded_file"]["type"]. "<br>";
				echo "Size : " .($_FILES["uploaded_file"]["size"]/ 1024). "KB<br>";
			}

			//Name the output file
			if(file_exists( "result.csv"))
			{
				$duplicatefile = "result.csv";
				unlink($duplicatefile);
				echo "Duplicate file deleted ! <br>";
			}

			//Check if uploaded file exists
			if(file_exists( $_FILES["uploaded_file"]["name"]))
			{
				echo"Uploaded file already exist, Please make sure that both file's content are same. <br>"	;
			}
			else
			{
				move_uploaded_file($_FILES["uploaded_file"]["tmp_name"],
				 $_FILES["uploaded_file"]["name"]);
			}

			//Open file from its location
			$file =  $_FILES["uploaded_file"]["name"];
			$ipfile = fopen($file,"r") or exit("unable to open file!");

			//To split up the IP Address and fetch data from server
			while(! feof($ipfile))
			{
				$iplist = stream_get_line($ipfile,18,",");

				$ipno = Dot2LongIP($iplist);
				$query = "SELECT * FROM ip2location_db11 WHERE ip_to >= $ipno order by ip_to limit 1 ";

				if(!$query)
				{
					echo "Error";
				}

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


				while($row = mysqli_fetch_array($result,MYSQL_ASSOC))
					{
						$current = "\"$iplist\",\"$ipno\",\"{$row['country_code']}\",\"{$row['country_name']}\",\"{$row['region_name']}\",\"{$row['city_name']}\",\"{$row['latitude']}\",\"{$row['longitude']}\",\"{$row['zip_code']}\",\"{$row['time_zone']}\"" ;

						//Output file to the path you want
							$ans = "result.csv";
							$fp = fopen($ans,"a") or die("couldn't open $ans for writing");
							fwrite($fp,$current) or die ("couldn't write values to file!");
							fclose($fp);
					}
			}
					echo "Result File Path : ", realpath($ans);
					echo "<br>";

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


		// 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 {
			$ip = explode(".", $IPaddr);
			return ($ip[3] + $ip[2] * 256 + $ip[1] * 256 * 256 + $ip[0] * 256 * 256 * 256);
			}
		}

	?>
	

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

Step 2: Run the below sample code.

	<?php

		// MYSQL configuration
		$mysql_server = "mysql_server.com";
		$mysql_user_name ="User ID";
		$mysql_user_pass ="password";

		// 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");
 

		//Display for user input
		echo "<html>";
		echo "<head><title>IP Query in Bulk</title></head>";
		echo "<body>
				<span>Upload IP list for validation:</span><br/><br/>
				<form action='' method='post' enctype='multipart/form-data'>
				<input name='uploaded_file' type='file' value='' /><br>
				<input type='submit' name='submit' value='Upload & Process' />
				</form>
			</body>";
		echo "</html>";


		//File submitted
		if(isset($_POST['submit']))
		{
			//Check for file error
			if($_FILES["uploaded_file"]["error"] > 0)
			{
				echo "Error :" .$_FILES["uploaded_file"]["error"]. "<br>";
			}
			else
			{
				echo "Input File Path :" , realpath(dirname(__FILE__))  ;
				echo "<br>";
				echo "File Name : " .$_FILES["uploaded_file"]["name"]. "<br>";
				echo "Type : " .$_FILES["uploaded_file"]["type"]. "<br>";
				echo "Size : " .($_FILES["uploaded_file"]["size"]/ 1024). "KB<br>";
			}

			//Name the output file
			if(file_exists( "result.csv"))
			{
				$duplicatefile = "result.csv";
				unlink($duplicatefile);
				echo "Duplicate file deleted ! <br>";
			}

			//Check if uploaded file exists
			if(file_exists( $_FILES["uploaded_file"]["name"]))
			{
				echo"Uploaded file already exist, Please make sure that both file's content are same. <br>"	;
			}
			else
			{
				move_uploaded_file($_FILES["uploaded_file"]["tmp_name"],
				 $_FILES["uploaded_file"]["name"]);
			}

			//Open file from its location
			$file =  $_FILES["uploaded_file"]["name"];
			$ipfile = fopen($file,"r") or exit("unable to open file!");

			//To split up the IP Address and fetch data from server
			while(! feof($ipfile))
			{
				$iplist = stream_get_line($ipfile,100,",");

				$ipno = Dot2LongIPv6($iplist);
				$query = "SELECT * FROM ip2location_db11 WHERE ip_to >= $ipno order by ip_to limit 1 ";

				if(!$query)
				{
					echo "Error";
				}

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


				while($row = mysqli_fetch_array($result,MYSQL_ASSOC))
					{
						$current = "\"$iplist\",\"$ipno\",\"{$row['country_code']}\",\"{$row['country_name']}\",\"{$row['region_name']}\",\"{$row['city_name']}\",\"{$row['latitude']}\",\"{$row['longitude']}\",\"{$row['zip_code']}\",\"{$row['time_zone']}\"" ;

						//Output file to the path you want
							$ans = "result.csv";
							$fp = fopen($ans,"a") or die("couldn't open $ans for writing");
							fwrite($fp,$current) or die ("couldn't write values to file!");
							fclose($fp);
					}
			}
					echo "Result File Path : ", realpath($ans);
					echo "<br>";

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


		// 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.