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