Auto Complete Search Mysql-Php then Fill Another Text Input

Auto Complete Search Mysql-Php. I am having difficulty in making this way. But ultimately everything can be resolved and I will share it for you... 😊

Desain

I have data form like this:

Because I already have city and country data, I want to use this auto complete search method to fill the city and country text field.

Database

Create two table then relate both (example: tbl_members and tbl_location)

Application

dbconfig.php => connector database

<?php
$database_host = "localhost";
$database_user = "root"; //your db username
$database_password = ""; //your db password 
$database_name = "dbtest"; //your database

try{
$connect_db = new PDO("mysql:host={$database_host};dbname={$database_name}",$database_user,$database_password);
$connect_db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e){
echo $e->getMessage();
}
include_once 'class.view.php';
$view = new view($connect_db);
?>

class.view.php => display data to index.php

<?php
class view
{
 private $db;
 function __construct($connect_db){
  $this->db = $connect_db;
 }

 public function dataview($query){
  $stmt = $this->db->prepare($query);
        $stmt->execute();
        if($stmt->rowCount()>0){
   while($row=$stmt->fetch(PDO::FETCH_ASSOC)){ ?>
    <tr>
     <td><?php print($row['id_member']); ?></td>
     <td><?php print($row['full_name']); ?></td>
     <td><?php print($row['email']); ?></td>
     <td><?php print($row['position']); ?></td>
     <td><?php print($row['city']); ?></td>
     <td><?php print($row['country']); ?></td>
    </tr><?php
    } ?>
  <?php
  }else{
   ?>
            <tr><td colspan="16">No Data Found ...</td></tr>
            <?php
  }
 }
}

auto_search.php => load data from tbl_location

<?php

 require_once 'dbconfig.php';

 $keyword = trim($_REQUEST['term']); // keyword on input city

 $sugg_json = array();    // this is for displaying json data as a autosearch suggestion
 $data_json = array();     // this is for stroring mysql results in json string
 

 $keyword = preg_replace('/\s+/', ' ', $keyword); // it will replace multiple spaces from the input.

 $query = 'SELECT id_location, city, country FROM tbl_location WHERE city LIKE :term';
 
 $stmt = $connect_db->prepare( $query );
 $stmt->execute(array(':term'=>"%$keyword%"));
 
 if ( $stmt->rowCount()>0 ) {
  
  while($rowResult = $stmt->fetch(PDO::FETCH_ASSOC)) {
      $data_json["id"] = $rowResult['id_location'];
      $data_json["value"] = $rowResult['city'];
      $data_json["label"] = $rowResult['city'];
   $data_json["country"] = $rowResult['country'];
      array_push($sugg_json, $data_json);
  }
  
 } else {
     $data_json["id"] = "";
     $data_json["value"] = "";
     $data_json["label"] = "";
  $data_json["country"] = "";
     array_push($sugg_json, $data_json);
 }
 
 $dataOutput = json_encode($sugg_json, JSON_UNESCAPED_SLASHES); 
 print $dataOutput;

index.php =>In this file there are 3 main parts
data form for add data to database

 <form class="form-horizontal" action="add_data.php" method="post">
 <fieldset>
 <!-- Form Name -->
 <legend align="right">Add Data Here</legend>
 <div class="col-md-6">
  <!-- Text input-->
  <div class="form-group">
   <label class="col-md-4 control-label" for="full_name">Full Name</label>  
   <div class="col-md-8">
   <input id="full_name" name="full_name" placeholder="Full Name" class="form-control input-md" required="" type="text">
   <span class="help-block"> </span>  
   </div>
  </div>

  <!-- Text input-->
  <div class="form-group">
   <label class="col-md-4 control-label" for="email">Email</label>  
   <div class="col-md-8">
   <input id="email" name="email" placeholder="Email" class="form-control input-md" required="" type="text">
   <span class="help-block"> </span>  
   </div>
  </div>

  <!-- Text input-->
  <div class="form-group">
   <label class="col-md-4 control-label" for="position">Position</label>  
   <div class="col-md-8">
   <input id="position" name="position" placeholder="Position" class="form-control input-md" required="" type="text">
   <span class="help-block"> </span>  
   </div>
  </div>
  
 </div>
 <div class="col-md-6">
  <!-- Text input-->
  <input id="id_location" name="id_location" type="hidden">
  <div class="form-group">
   <label class="col-md-4 control-label" for="city">City</label>  
   <div class="col-md-8">
   <input id="city" name="city" placeholder="City" class="form-control input-md" required="" type="text">
   <span class="help-block"> </span>  
   </div>
  </div>

  <!-- Text input-->
  <div class="form-group">
   <label class="col-md-4 control-label" for="country">Country</label>  
   <div class="col-md-8">
   <input id="country" name="country" placeholder="Country" class="form-control input-md" required="" type="text">
   <span class="help-block"> </span>  
   </div>
  </div>
  <div class="form-group" >
   <div class="col-md-8 col-md-offset-4" align="right">
    <button type="submit" name="btn_add" class="btn btn-sm btn-info"><i class="glyphicon glyphicon-plus"></i> &nbsp; Add</button>
   </div>
   
  </div>
 </div>
 
 </fieldset>
</form>

Display data => this part to display data from tbl_members and tbl_location with JOIN query

<div class="row">
 <table class="table table-bordered table-hover">   
  <thead class="thead-default">  
   <tr>
    <th class="col-md-1"><center> #</center></th>
    <th class="col-md-3"><center> Name</center></th>
    <th class="col-md-2"><center> Email</center></th>
    <th class="col-md-2"><center> Position </center></th>
    <th class="col-md-2"><center> City </center></th>
    <th class="col-md-2"><center> Country </center></th>
   </tr>
  </thead>
  <?php
   require_once 'dbconfig.php';
   
   $query = "SELECT tbl_members.id_member, tbl_members.full_name, tbl_members.email, tbl_members.position, tbl_location.city, tbl_location.country
       FROM tbl_members INNER JOIN tbl_location ON tbl_members.id_location=tbl_location.id_location"; 
    
   $view->dataview($query); 
  ?>
 </table> 
 <br>
</div>

Auto search (jquery)=>load data from auto_search.php process. this parts need some libraries (jquery UI). you can download it or load from this sources

<link rel="stylesheet" href="http://code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>

<script>
$(document).ready(function(){
 
 $('#city').autocomplete({
     source: "auto_search.php",
     minLength: 2,
     select: function(event, ui) {
         var id = ui.item.id;
   var city = ui.item.value;
   var country = ui.item.country;
         if (id != '') {
             $("#city").val(city);
    $("#country").val(country);
    $("#id_location").val(id);
         }
     },
     open: function(event, ui) {
         $(".ui-autocomplete").css("z-index", 1000)
     },
    
 }) 
});
</script>

add_data.php =>add data to the database. There is a section to handling if the city we write in city text field has not been recorded on tbl_location. Maybe I'll explain if anyone asks about it.

<?php

 error_reporting( ~E_NOTICE ); // avoid notice
 
 require_once 'dbconfig.php';
 
 if(isset($_POST['btn_add']))
 {
  $full_name = $_POST['full_name'];// user name
  $email = $_POST['email'];
  $position = $_POST['position'];
  $id_location = $_POST['id_location'];
  $city = $_POST['city'];
  $country = $_POST['country'];
  
  if ($id_location!=""){
   $query ="INSERT INTO tbl_members(full_name, email, position, id_location) 
            VALUES(:full_name, :email, :position, :id_location)"; 
      
   $stmt = $connect_db->prepare($query);
   $stmt->bindParam(':full_name',$full_name);
   $stmt->bindParam(':email',$email);
   $stmt->bindParam(':position',$position);
   $stmt->bindParam('id_location',$id_location);
   
   if($stmt->execute())
   {
    echo ("<SCRIPT LANGUAGE='JavaScript'>
       window.alert('Succesfully Updated')
       window.location.href='index.php';
       </SCRIPT>"); 
    header("location:index.php");    
    exit();
   }
   else
   { 
    echo ("<SCRIPT LANGUAGE='JavaScript'>
       window.alert('Error Insert Data')
       window.location.href='index.php';
       </SCRIPT>");
    exit();
   }
  }else{
   $id_location=time();
   $query ="INSERT INTO tbl_members(full_name, email, position, id_location) 
            VALUES(:full_name, :email, :position, :id_location)"; 
   $stmt = $connect_db->prepare($query);
   $stmt->bindParam(':full_name',$full_name);
   $stmt->bindParam(':email',$email);
   $stmt->bindParam(':position',$position);
   $stmt->bindParam(':id_location',$id_location);
   
   if($stmt->execute()){
    $query ="INSERT INTO tbl_location(id_location, city, country) VALUES(:id_location, :city, :country)"; 
    $stmt = $connect_db->prepare($query);
    $stmt->bindParam(':id_location',$id_location);
    $stmt->bindParam(':city',$city);
    $stmt->bindParam(':country',$country);
    if($stmt->execute())
    {
     echo ("<SCRIPT LANGUAGE='JavaScript'>
       window.alert('Succesfully Updated')
       window.location.href='index.php';
       </SCRIPT>");
     exit();
    }
    else
    {
     echo ("<SCRIPT LANGUAGE='JavaScript'>
       window.alert('Succesfully Updated')
       window.location.href='index.php';
       </SCRIPT>");
     exit();
     
    } 
   }else{
    echo ("<SCRIPT LANGUAGE='JavaScript'>
       window.alert('Succesfully Updated')
       window.location.href='index.php';
       </SCRIPT>");
    exit();
   }
   
   
  }
   
 }
?>