In this tutorial, we demonstrate how to create an autocomplete for city search function based on IP address using PHP programming languages, AJAX and MySQL database. To explain more, when the user enters a character, a list of the suggestion which contain that character will be produced and shown up for user’s selection. It is useful when user wants to search for something because it can reduce the time used for entering the entire whole word.
To do autocomplete, first, we have to create a new database. The database used in this tutorial is IP2Location™ LITE IP-COUNTRY-REGION-CITY Database. This free database can be obtained from https://lite.ip2location.com/database/ip-country-region-city . After we download the data file, we will need to create a table for that
Below are the steps to set up the database and the sample codes
Step 1: Create ‘ip2location_db3’ table
CREATE TABLE `ip2location_db3` ( `ip_from` INT(10) NOT NULL, `ip_to` INT(10) NOT NULL, `country_code` CHAR(2) NOT NULL, `country_name` VARCHAR(64) NOT NULL, `region_name` VARCHAR(128) NOT NULL, `city_name` VARCHAR(128) NOT NULL, INDEX `idx_ip_from` (`ip_from`), INDEX `idx_ip_from_to` (`ip_from`, `ip_to`), INDEX `idx_ip_to` (`ip_to`), INDEX `idx_cc_rn` (`country_code`, `region_name`), INDEX `idx_cc_rn_cn` (`country_code`, `region_name`, `city_name`) ) COLLATE='utf8_general_ci' ENGINE=InnoDB;
Step 2: Import the data into table ‘ip2location_db3’
LOAD DATA LOCAL INFILE 'IP2LOCATION-LITE-DB3.CSV' INTO TABLE `ip2location_db3` FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 0 LINES;
Next, we will create PHP file with AJAX. Two PHP file will be created, namely index.php and fetch.php. The AJAX code in index.php will capture the user input and send it to fetch.php. The fetch.php will query the database to get a list based on user input and after that pass the list back to index.php. The code will be shown in Step 3.
Step 3: Create index.php file
<!DOCTYPE html>
<html>
<head>
<title>City Search Example Code Auto Complete</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-3-typeahead/4.0.2/bootstrap3-typeahead.min.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
</head>
<body>
<br /><br />
<div class="container" style="width:600px;">
<h2 align="center">City Search Example Code Auto Complete</h2>
<br /><br />
<form action="" method="post">
<label>Country: </label>
<?php
//connect to database
$connect = mysqli_connect("localhost", "root", "", "ip2location");
//retrieve countryName based on ipaddress
//Get the visitor IP address
$ip = $_SERVER['REMOTE_ADDR'];
//In case you are testing locally with 127.0.0.1,
//you can uncomment the below line to assign the IP address
//to 8.8.8.8 (or whatever) for your testing.
//$ip= "8.8.8.8";
// Function to convert IP address (xxx.xxx.xxx.xxx) to IP number (0 to 256^4-1)
function Dot2LongIP ($ip) {
if ($ip == ""){
return 0;
}else {
$ips = explode(".", $ip);
return ($ips[3] + $ips[2] * 256 + $ips[1] * 256 * 256 + $ips[0] * 256 * 256 * 256);
}
}
// Convert IP address to IP number for querying database
$ipno = Dot2LongIP($ip);
//start to query from database
$query = 'select DISTINCT country_name,country_code from ip2location_db3 where "'.$ipno.'"<= ip_to LIMIT 1';
$result = mysqli_query($connect, $query);
//check if query is sucesss
if(!empty($result)){
while($row = mysqli_fetch_assoc($result)){
echo '<label id="country_name">' . $row["country_name"] .'</label>';
//store country code in a variable for retrieve the region name and city name
$country_code = $row["country_code"];
}
}
?>
<br /><br />
<label>Region Name:</label>
<input type="text" name="region" id="region" class="form-control input-lg" autocomplete="off" placeholder="Type Region Name" />
<br /><br />
<label>City Name:</label>
<input type="text" name="city" id="city" class="form-control input-lg" autocomplete="off" placeholder="Type City Name" />
<br /><br />
<input class="btn btn-default" type="submit" value="submit">
</form>
</div>
</body>
</html>
<script>
$(document).ready(function(){
$('#region').typeahead({
source: function(query, result){
//call php variable into javascript by using php print method
var country = "<?php print($country_code); ?>";
$.ajax({
url:"fetch.php",
method:"POST",
data:{country_code:country,query:query},
dataType:"json",
success:function(data){
result($.map(data, function(item){
return item;
}));
}
})
}
});
$('#city').typeahead({
source: function(query1, result){
var name = document.getElementById("region").value;
//call php variable into javascript by using php print method
var country = "<?php print($country_code); ?>";
$.ajax({
url:"fetch.php",
method:"POST",
data:{country_code:country,region_name:name,query1:query1},
dataType:"json",
success:function(data){
result($.map(data, function(item){
return item;
}));
}
})
}
});
});
</script>
Step 4: Create fetch.php file
<?php
//connect to database
$connect = mysqli_connect("localhost", "root", "", "ip2location");
//select the region name based on the user input
if(empty($_POST["query1"])) {
//retrieve the country name from index.php
$country_code = $_POST['country_code'];
//retrieve user input to do autocomplete
$request = mysqli_real_escape_string($connect, $_POST["query"]);
$query = "select DISTINCT region_name from ip2location_db3 where country_code = '".$country_code."' AND region_name LIKE '{$request}%' GROUP BY region_name";
$result = mysqli_query($connect, $query);
$data = array();
if(mysqli_num_rows($result) > 0){
while($row = mysqli_fetch_assoc($result)){
$data[] = $row["region_name"];
}
echo json_encode($data);
}
}
else{
//select the city name based on the user input
//retrieve the country name from index.php
$country_code = $_POST['country_code'];
//retrieve user input to do autocomplete
$request = mysqli_real_escape_string($connect, $_POST["query1"]);
$region_name = mysqli_real_escape_string($connect, $_POST["region_name"]);
$query = "select DISTINCT city_name from ip2location_db3 where country_code = '".$country_code."' AND region_name = '".$region_name."' AND city_name LIKE '{$request}%' GROUP BY city_name";
$result = mysqli_query($connect, $query);
$data = array();
if(mysqli_num_rows($result) > 0){
while($row = mysqli_fetch_assoc($result)){
$data[] = $row["city_name"];
}
echo json_encode($data);
}
}
?>