ip2location facebbook  ip2location twitter  ip2location google+ ip2location github

Display San Francisco Bay Area

In this tutorial, we demonstrate how to extract Bay Area information based on their IP address using C#/VB.NET/PHP/Java programming languages and IP2Location MySQL database. In this tutorial, we use the IP2Location LITE DB3 CSV database to extract Bay Area information from the visitor's IP address. Free databases are available for download at https://lite.ip2location.com/

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

 

IPv4

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

Step 2: Download Bay Area CSV file from GitHub and upload to your SQL server in the same database.

Step 3: Run the below sample code.

 

		<?php
			if(isset($_POST['ip_address'])){
			$conn = new mysqli("localhost", "root", "", "sf_bay");
			if (mysqli_connect_errno()) {
				echo "<p>Connection failed:".mysqli_connect_error()."</p>\n";
			}

			$ip = $_POST['ip_address'];

			$sql = "SELECT db.country_name, db.region_name, db.city_name, b.bay_area
			FROM ip2location_db3 db, bay_area b
			WHERE db.region_name = b.region_name
			AND db.country_code = b.country_code
			AND inet_aton('$ip') <= db.ip_to
			limit 1";

			$result = mysqli_query($conn, $sql) or die (mysqli_error($conn));

			mysqli_close($conn);
			}
			?>
			<!DOCTYPE html>
			<html>
			<head>
				<title>San Francisco Bay Areas</title>
				<style>
					th, td{
						padding-right: 15px;
						text-align: left;
					}
				</style>
			</head>

			<body>
				<h2>San Francisco Bay Areas</h2>
				<p>Enter an IP address to extract bay area information</p>
				<form name="query" method="post">
					<label for="address">IP here: </label>
					<input type="text" name="ip_address" id="ip_address" size="40" value="<?= isset($_POST['ip_address']) ? htmlspecialchars($_POST['ip_address']) : '' ?>">
					<button type="submit">Run</button>
				</form>
				<br/>
				<table>
				<tr>
					<th>Country</th>
					<th>Region</th>
					<th>City</th>
					<th>Bay Area</th>
				</tr>
				<?php
				if (isset($ip)){
					while ($row = mysqli_fetch_assoc($result)){
						$country = $row['country_name'];
						$region = $row['region_name'];
						$city = $row['city_name'];
						$bay = $row['bay_area'];
						echo "<tr>";
						echo "<td>".$country."</td>";
						echo "<td>".$region."</td>";
						echo "<td>".$city."</td>";
						echo "<td>".$bay."</td>";
						echo "</tr>";
					}
					echo "</table>";
				}
			?>

			</body>
			</html>

			
Reading IP addresses and returning Bay Area information.
		void DisplayFileContents(HttpPostedFile file)
		  {
			Stream theStream = FileUpload1.PostedFile.InputStream;
			  StringBuilder display = new StringBuilder();
			  using (StreamReader sr = new StreamReader(theStream))
			  {
				  string line;
				  char[] delimiter1 = new char[] { ',' };

				  while ((line = sr.ReadLine()) != null)
				  {
					  string[] iplist = line.Split(delimiter1, StringSplitOptions.None);

					  foreach (string IP in iplist)
					  {
						  display.Append("IP Address : " + IP.ToString() + "\n");

						  string ip_addr = IP;

						  long ip_num = ip_to_number(ip_addr);
						  string query = "SELECT db.country_name, db.region_name, db.city_name, b.bay_area FROM ip2location_db3 db, bay_area b WHERE db.region_name = b.region_name AND db.country_code = b.country_code AND " + ip_num +  " <= db.ip_to limit 1";
						  string[] data = Get_Data(query);

						  string country_name = data[0];
						  string region_name = data[1];
						  string city_name = data[2];
						  string bay_area = data[3];

						  //Define the result file that you want to output
						  string filePath = Server.MapPath("~") + "result.csv";

						  using (StreamWriter Sw = File.AppendText(filePath))
						  {
							  string output = string.Format("\"" + IP + "\"" + "," + "\"" + country_name + "\"" + "," + "\"" + region_name + "\"" + "," + "\"" + city_name + "\"" + "," + "\"" + bay_area + "\"");

							  Console.WriteLine(output);
							  Sw.WriteLine(output);

						  }
						  Label3.Text = "Result File Path : " + Path.GetFullPath(filePath) + "
"; } } } Label4.Text = "Please DELETE the result file if you want to upload a new file!
"; }

Retrieving data
		public static string[] Get_Data(string query)
		  {
			  //Database connection string, replace lower capital with MySQL settings
			  string db_con_string = "SERVER=server;DATABASE=database;UID=userid;PASSWORD=password;";
			  using (MySqlConnection con = new MySqlConnection(db_con_string))
			  {
				  using (MySqlCommand cmd = new MySqlCommand())
				  {
					  cmd.CommandText = query;
					  using (MySqlDataAdapter sda = new MySqlDataAdapter())
					  {
						  cmd.Connection = con;
						  sda.SelectCommand = cmd;
						  using (DataSet ds = new DataSet())
						  {
							  DataTable dt = new DataTable();
							  sda.Fill(dt);
							  object country_name = dt.Rows[0]["country_name"];
							  object region_name = dt.Rows[0]["region_name"];
							  object city_name = dt.Rows[0]["city_name"];
							  object bay_area = dt.Rows[0]["bay_area"];
							  string[] data = { Convert.ToString(country_name), Convert.ToString(region_name), Convert.ToString(city_name), Convert.ToString(bay_area) };
							  return data;
						  }
					  }
				  }
			  }
		  }

		

Converting IP address to IP number
				public long ip_to_number(string ip_addr)
				  {
					  string[] ip_Sblock;
					  int[] ip_block = new int[4];
					  string[] separator = { "." };

					  ip_Sblock = ip_addr.Split(separator, StringSplitOptions.None);
					  for (int i = 0; i <= 3; i++)
					  {
						  ip_block[i] = Convert.ToInt32(ip_Sblock[i]);
					  }

					  return ip_block[0] * 16777216 + ip_block[1] * 65536 + ip_block[2] * 256 + ip_block[3];
				  }

			
Reading IP addresses and returning Bay Area information.
				Protected Sub DisplayFileContents(ByVal file As HttpPostedFile)
				Dim theStream As Stream = FileUpload1.PostedFile.InputStream
				Dim Display As StringBuilder = New StringBuilder()
				Using sr As New StreamReader(theStream)
					Dim line As String
					Dim Delimiter1() As Char = New Char() {","}
					While (1)
						line = sr.ReadLine()
						If line <> Nothing Then
							Dim iplist() As String = line.Split(Delimiter1, StringSplitOptions.None)
							For Each IP As String In iplist
								Display.Append("IP Address : " + IP.ToString() + "\n")

								Dim ip_addr As String = IP

								Dim ip_num As Long = ip_to_number(ip_addr)
								Dim query As String = query = "SELECT db.country_name, db.region_name, db.city_name, b.bay_area
								  FROM ip2location_db3 db, bay_area b
								  WHERE db.region_name = b.region_name
								  AND db.country_code = b.country_code
								  AND " & ip_num &  " <= db.ip_to limit 1"
								Dim data() As String = Get_Data(query)

								Dim country_name As String = data(0)
								Dim region_name As String = data(1)
								Dim city_name As String = data(2)
								Dim bay_area As String = data(3)

								'Define the result file that you want to output
								Dim filePath As String = Server.MapPath("~") + "result.csv"

								Using Sw As StreamWriter = System.IO.File.AppendText(filePath)
									Dim output As String = String.Format("""" + IP + """" + "," + """" + country_name + """" + "," + """" + region_name + """" + "," + """" + city_name + """" + "," + """" + bay_area + """")
									Console.WriteLine(output)
									Sw.WriteLine(output)
								End Using
								Label3.Text = "Result File Path : " + Path.GetFullPath(filePath) + "<br>"
							Next
						Else
							Exit While
						End If
					End While
				End Using
				Label4.Text = "Please DELETE the result file if you want to upload a new file! <br>"
			End Sub
			
Retrieving data
			Public Function Get_Data(ByVal query As String) As String()
			'Database connection string, replace lower capital with MySQL settings
			Dim db_con_string As String = "SERVER=server;DATABASE=database;UID=userid;PASSWORD=password;"
			Using con As New MySqlConnection(db_con_string)
				Using cmd As New MySqlCommand()
					cmd.CommandText = query
					Using sda As New MySqlDataAdapter()
						cmd.Connection = con
						sda.SelectCommand = cmd
						Using ds As New DataSet()
							Dim dt As New DataTable()
							sda.Fill(dt)
							Dim country_name As Object = dt.Rows(0)("country_name")
							Dim region_name As Object = dt.Rows(0)("region_name")
							Dim city_name As Object = dt.Rows(0)("city_name")
							Dim bay_area As Object = dt.Rows(0)("bay_area")
							Dim data() As String = {Convert.ToString(country_name), Convert.ToString(region_name), Convert.ToString(city_name), Convert.ToString(bay_area)}
							Return data
						End Using
					End Using
				End Using
			End Using
			End Function
			
Converting IP address to IP number
			Public Function ip_to_number(ByVal ip_addr As String)
				Dim ip_block() As String
				Dim ip_num As Long

				ip_block = Split(ip_addr, ".")
				ip_num = (ip_block(0) * (256 ^ 3)) + (ip_block(1) * (256 ^ 2)) + (ip_block(2) * 256) + ip_block(3)

				Return ip_num
			End Function
			
Reading IP addresses and returning Bay Area information.
				import java.sql.*;
				import java.util.Scanner;

				public class System
				{
					static final String driver = "com.mysql.jdbc.Driver";
					static final String host = "jdbc:derby://localhost/sf_bay";
					static final String username = "root";
					static final String password= "";

					public static void main(String[] args) throws Exception{
						Scanner sc = new Scanner(System.in);
						String ipAddress;
						System.out.println("Enter IP Address:");
						ipAddress = user_input.next();
						ipnum = ipToLong(ipAddress);

						Connection conn = null;
						Statement stmt = null;
						try {
							Class.forName(driver);
							conn = DriverManager.getConnection(host, username, password);
							stmt = conn.createStatement();
							String sql = "SELECT db.country_name, db.region_name, db.city_name, b.bay_area
							FROM ip2location_db3 db, bay_area b
							WHERE db.region_name = b.region_name
							AND db.country_code = b.country_code
							AND inet_aton(" + ipnum + ") <= db.ip_to
							limit 1";
							ResultSet rs = stmt.executeQuery(sql);

							while(rs.next()){
								String country  = rs.getString("country_name");
								String region = rs.getString("region_name");
								String city = rs.getString("city_name");
								String bayarea = rs.getString("bay_area");
								System.out.print("Country: " + country);
								System.out.print(", Region: " + region);
								System.out.print(", City: " + city);
								System.out.println(", Bay Area: " + bayarea);
							}
							rs.close();
							stmt.close();
							conn.close();
						}
						catch ( SQLException err ) {
							System.out.println( err.getMessage( ) );
						}
					}
				}

			
Converting IP address to IP number
			public long ipToLong(String ipAddress) {

				String[] ipAddressInArray = ipAddress.split("\\.");

				long result = 0;
				for (int i = 0; i < ipAddressInArray.length; i++) {
					int power = 3 - i;
					int ip = Integer.parseInt(ipAddressInArray[i]);
					result += ip * Math.pow(256, power);
				}
				return result;
			}


			

 


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.