Searching database for Pin code by entering location name and using Autocomplete

User will enter matching keys for searching a location. Using Autocomplete we can show the available names which the user can select. On selection of the location, full details like division, Region, Circle, Taluk, district, state with Pin Code will be displayed. For this a single table with all required data is maintained. Source of Autocomplete is name column storing the location. We kept one textbox to receive the user entered text.

<input type=text  id='t1' class='form-control'> 

<button value=Reset id=img1>Reset</button>

Posting the Entered chars to backend script

To get the suggestions for autocomplete we used source option. We are posting the user entered chars to backend PHP script search-keyword-backend.php. We getback the return value in lable and value pairs, this will be displayed to user as suggested list for its selection.

source: function (request, response) {
         $.ajax({
      url: "indexck.php",
         type: "POST",
             data: request,
			 dataType: 'json',
             success: function (data) {
                 response($.map(data, function (el) {
                     return {
                         label: el.label,
                         value: el.value
                     };
                 }));
             }
         });
    }

User selection one of the option from the suggested list.

From the suggested list once the user select one option the select event of autocomplete gets triggered.

select: function(event, ui) {
event.preventDefault();
    this.value = ui.item.label;
	//alert(ui.item.label);
$("#d2").load("indexck-dtl.php?id="+ui.item.value);

        return false;
  }
});

We also used another image as button to reset the input box.
We used load() to post the unique id of the record to backend script and get back the all the details of the record to display to user.

Here is the complete front end code to to post the data to backend script and get the output to display.

<script>
$(document).ready(function() {
////////////
$('#t1').focus();
///

$( "#t1" ).autocomplete({
	delay: 1000,
source: function (request, response) {
         $.ajax({
      url: "search-keyword-backend.php",
         type: "POST",
             data: request,
			 dataType: 'json',
             success: function (data) {
                 response($.map(data, function (el) {
                     return {
                         label: el.label,
                         value: el.value
                     };
                 }));
             }
         });
    },
select: function(event, ui) {
event.preventDefault();
    this.value = ui.item.label;
	//alert(ui.item.label);
$("#d2").load("indexck-dtl.php?id="+ui.item.value);

        return false;
  }
});
////////////
$('#img1').click(function(){
	$('#t1').focus();
$("#t1").val('');
})


///////
});
/////////////////////
</script>

Searching location within a State

Searching with keyword and additional parameter

We were posting the user entered keyword to backend script and getting the matching records as suggestions for user to select. Now we will send one more parameter, the state name along with the user entered chars to restrict the return suggestion limited to a particular state only. One dropdown list showing the state names is added for the user to select the name of the state to restrict the suggestions to that state only.

Here the complete script.

<script>
$(document).ready(function() {
////////////
$('#t1').focus();
///

$( "#t1" ).autocomplete({
	delay: 1000,
source: function (request, response) {
$.getJSON("indexck.php",{statename:$('#statename').val(),term:$('#t1').val()}, 
     response);
  },
select: function(event, ui) {
event.preventDefault();
    this.value = ui.item.label;
	//alert(ui.item.label);
	var statename = $("#statename").val();
	//alert(statename);
$("#d2").load("indexck-dtl.php?id="+ui.item.value+"&statename"+statename);
    return false;
  }
});
////////////
$('#img1').click(function(){
	$('#t1').focus();
$("#t1").val('');
$('#statename').prop('selectedIndex',0);
})
////////////////
$("#statename").change(function(){ 
$('input#t1').autocomplete("search");
})
///////
});
/////////////////////
</script>

Backend PHP script

There are two pages with PHP code to manage the database. The first one indexck.php manages the suggestions for autocomplete so users can select one of the options. The second one indexck-dtl.php receives the id of the record based on the user selection of option and then returns the full details of that record for display.

indexck.php

This page receives the user entered chars and the name of the state selected by dropdown listbox. ( in case of index-state.php page ). These two parameters are then used to create the sql query by using WHERE condition check using LIKE query to match user entered chars.

By entering DA [space] BA we can get records matching to both DA or BA.

While displaying the options we have combined officename and statename by using CONCAT function. Using the query through fetch_array() function, we filled an array of options by value and label pairs. The same array we use to crate json output to post back to our main scripts.

<?Php
$o_name=$_REQUEST['term'];  // receive the keyword or chars 
$statename=$_GET['statename']; // receive the state name

require "config.php";// connection to database 

$search  = array('&', ' ');
$replace = array('', '');

if(!ctype_alpha(str_replace($search,$replace,$o_name))){
echo "Data Error ";
exit;
}
if(strlen($statename) >0 ){
if(!ctype_alpha(str_replace($search,$replace,$statename))){
echo "Data Error ";
exit;
}
$q2= " AND statename='$statename' ";
}else {$q2='';}


$kt=explode(" ",$o_name);//Breaking the string to array of words
// Now let us generate the sql 
while(list($key,$val)=each($kt)){
if($val<>" " and strlen($val) > 0){$q .= " officename like '%$val%' or ";}
}// end of while
$q=substr($q,0,(strlen($q)-3));
$my_array=array();

$q1="select    CONCAT(officename,',',statename) as label, id as value from pincode_india where $q $q2 limit 0,30";

$result_set = $connection->query($q1);
while($row = $result_set->fetch_array(MYSQLI_ASSOC)){
$my_array[]=array("value"=>$row['value'],"label"=>$row['label']);
}

echo json_encode($my_array); 
?>

indexck-dtl.php

This file receives the ID of the record ( location ) as selected by user from the suggestion of autocomplete. Using this ID all details of the record is collected and posted back to main page.

Select Query to get details of the record

$id=$_GET['id'];

require "config.php";// connection to database 


$q1="select  *  from pincode_india where id=?";
if($stmt = $connection->prepare($q1)){
  $stmt->bind_param('i',$id);
  $stmt->execute();
   
   $result = $stmt->get_result();
   //echo "No of records : ".$result->num_rows."<br>";
   $row=$result->fetch_object();
   echo "<table class='table table-striped my_table' width=50%> <tr class='info'><th>Name</th><th>Value</th></tr>
   <tr ><td>Office Name</td><td>$row->officename</td></tr>
   <tr ><td>Division  Name</td><td>$row->divisionname</td></tr>
   <tr ><td>Region Name</td><td>$row->regionname</td></tr>
   <tr ><td>Circle Name</td><td>$row->circlename</td></tr>
   <tr ><td>Taluk</td><td>$row->taluk</td></tr>
   <tr ><td>District </td><td>$row->districtname</td></tr>
   <tr ><td>State</td><td>$row->statename</td></tr>
   <tr ><td>Pin Code</td><td>$row->pincode</td></tr>
   </table>
   ";
}else{
  echo $connection->error;
}

Files inside autocomplete-search.zip ( script )

config.php

MySQLi database connection details are kept here

index.php

Main file to search database on location column.

index-state.php

Main file to search database on location and state column.

indexck.php

PHP script to receive keywrods and state name to return matching options for the suggestion for the user to select.

indexck-dtl.php

PHP Script to get the ID of the user selected record and return the full details of the matching ID

sql_dump.txt

SQL dump to create the pincode table with few sample data

index.php

Main file to search database on location column.

templates/head.php
templates/bottom.php

Connects to CDN to use JQuery, Jquery UI, Bootstrap and other CSS files .

Most Popular JQuery Scripts
1	Two dependant list boxes
2	Calendar with Date Selection
3	Data change by Slider
4	Show & Hide element

Searching for address and pin code on a Database with Autocomplete.

Posting the Entered chars to backend script

User selection one of the option from the suggested list.

Searching location within a State

Backend PHP script

indexck.php

indexck-dtl.php

Files inside autocomplete-search.zip ( script )

1

2

3

4