Creating autocomplete textbox using JQuery, PHP and MySQL
The use of autocomplete is very helpful for users when will choose / fill something, typing all characters not required , just type a few characters then it will appear a kind of list that we may want. The Autocomplete widgets provides suggestions while you type into the field and of course the input process becomes faster.
Here is the final result we will create in the autocomplete tutorial, as shown in Figure 1 below:
Step-by-Step Guide : Create Autocomplete
1. Create MySQL Database TableGive name of database
"dbkota", then create table "city" which contain field : id and nama_kota like script below:
CREATE TABLE IF NOT EXISTS `kota` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`nama_kota` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;INSERT INTO `kota` (`id`, `nama_kota`) VALUES
(1, 'Banda Aceh'),(2, 'Meda'),(3, 'Padang'),(4, 'Jambi'),(5, 'Bengkulu'),(6, 'Bandar Lampung'),
(7, 'Batam'),(8, 'Palembang'),(9, 'Riau'),(10, 'Jakarta'),(11, 'Surabaya'),(12, 'Semarang');2. Create index.php file
This index.php file is the main file that will display the input for the user. In this file we also call library typehead.js and autoajax.php.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta name="author" content="ilmu-detil.blogspot.com">
<title>Tutorial Autocomplete</title>
<link rel="stylesheet" href="assets/css/bootstrap.css">
<link rel="stylesheet" href="assets/css/ilmudetil.css">
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-3-typeahead/4.0.1/bootstrap3-typeahead.min.js"></script>
</head>
<body>
<div class="navbar navbar-default navbar-fixed-top">
<div class="container">
<div class="navbar-header">
<button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse">
<span class="icon-bar"></span><span class="icon-bar"></span><span class="icon-bar"></span>
</button>
<a class="navbar-brand" href="index.html">
Pusat Ilmu Secara Detil</a>
</div>
<div class="navbar-collapse collapse">
<ul class="nav navbar-nav navbar-left">
<li class="clr1 active"><a href="index.html">Home</a></li>
<li class="clr2"><a href="">Programming</a></li>
<li class="clr3"><a href="">English</a></li>
</ul>
</div>
</div>
</div>
</br></br></br></br>
<div class ="container">
<div class="row">
<div class="col-md-6">
<div class="panel panel-default">
<div class="panel-heading">Autocomplete Nama Kota</div>
<div class="panel-body">
<div class="row">
<div class="col-md-8" style="padding-top:25px">
<input type="text" class="typeahead form-control" required="required" name="inputnama" placeholder="Nama Kota">
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<script>
$('input.typeahead').typeahead({
source: function (query, process) {
return $.get('ajaxauto.php', { query: query }, function (data) {
console.log(data);
data = $.parseJSON(data);
return process(data);
});
}
});
</script>
</body>
</html>Ouput from this second step will produce an interface like Figure 1 above. But it is not equipped with autocomplete function. To provide its autocomplete function, we have to create a file again (ajaxauto.php).
So this index.php file only accepts an input or some characters from the user, then thrown into ajaxauto.php file to check whether the data is located in database or not.
3. Create ajaxauto.php file
This file contains the connection information to the database first in order to establish a connection to mysql. Then the data thrown by the index.php file is checked into the dabatase. The found data is made into JSON, then sent back to the index.php file to display in textbox.
<?php
$con=mysqli_connect("localhost","root","","dbkota");
$sql = "SELECT nama_kota FROM kota
WHERE nama_kota LIKE '%".$_GET['query']."%'
LIMIT 10";
$result = mysqli_query($con,$sql);
$json = [];
while($row = $result->fetch_assoc()){
$json[] = $row['nama_kota'];
}
echo json_encode($json);
?>Note the above code snippet in the query:
$sql = "SELECT nama_kota FROM kota
WHERE nama_kota LIKE '%".$_GET['query']."%'
LIMIT 10"The query above is the one that will match each character whether it is exist in database or not. Code
LIKE '%. $ _ GET [' query ']. "%' will check characters whether in front, behind, in second or any position of a word.For example: Jakarta, Jambi, Sumbawa. So when we type the character b, then it only displayed the choice of Sumbawa and Jambi (because both cities contain the character b), while Jakarta doesn't have character b.
If we want only the first letter selected, then we change the query into:
$sql = "SELECT nama_kota FROM kota
WHERE nama_kota LIKE '".$_GET['query']."%'
LIMIT 10"The output for only selected based on front position characters is shown in Figure.2 below:
