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

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) + "<br>";
              }
          }
      }
 
      Label4.Text = "Please DELETE the result file if you want to upload a new file! <br>";
  }

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];
  }

 

 

Was this article helpful?

Related Articles