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