Autocomplete textbox helps the user to pick an item from the suggestion list based on the input. It occupies less space on the page.
Data is not preloaded on the page. When the user types any character then data is loaded using AJAX.
In this tutorial, I show how you can create autocomplete textbox with MySQL database data using JavaScript and PHP.
Contents
- Table structure
- Database Configuration
- HTML – Create Autocomplete layout
- PHP – Return suggestion list
- JavaScript
- Demo
- Conclusion
1. Table structure
I am using users
table in the example. It has the following structure –
CREATE TABLE `users` ( `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `name` varchar(80) NOT NULL, `email` varchar(80) NOT NULL, `city` varchar(80) NOT NULL );
2. Database Configuration
Create a config.php
file for database configuration.
Completed Code
<?php $host = "localhost"; /* Host name */ $user = "root"; /* User */ $password = "root"; /* Password */ $dbname = "tutorial"; /* Database name */ // Create connection $con = new mysqli($host, $user, $password, $dbname); // Check connection if ($con->connect_error) { die("Connection failed: " . $con->connect_error); }
3. HTML – Create Autocomplete layout
Create a text element for searching. Define onkeyup
event that calls searchResult()
function.
Display suggestion list in <ul id="searchResult">
and in <div id="userDetail">
display details of the selected item from the suggestion list.
Completed Code
<style type="text/css"> .clear{ clear:both; margin-top: 20px; } .autocomplete{ width: 250px; position: relative; } .autocomplete #searchResult{ list-style: none; padding: 0px; width: 100%; position: absolute; margin: 0; background: white; } .autocomplete #searchResult li{ background: #F1F4FFFF; padding: 4px; margin-bottom: 1px; } .autocomplete #searchResult li:nth-child(even){ background: #A2A2A1FF; color: white; } .autocomplete #searchResult li:hover{ cursor: pointer; } .autocomplete input[type=text]{ padding: 5px; width: 100%; letter-spacing: 1px; } </style> <div class='autocomplete'> <div>Search Name </div> <div> <input type="text" id="txt_search" onkeyup="searchResult(this.value);"> </div> <!-- Suggestion List --> <ul id="searchResult"></ul> <div class="clear"></div> <!-- Show selected user Details --> <div id="userDetail"></div> </div>
4. PHP – Return suggestion list
Create ajaxfile.php
file.
From here handle 2 requests –
- suggestionList – Return records based on search value.
- userdetails – Return a user record.
$request == ‘suggestionList’
- Check if
search
is POST or not. If POST then assign it to$search
variable. - Select 5 records from the
users
table where$search
value matches in thename
field. - Loop on the fetched records and initialize
$return_arr
withid
andname
keys. - Pass user id in
id
key and user name inname
key. - Return
$return_arr
Array in JSON format.
$request == ‘userdetails’
- Check if
userid
is POST or not. If POST then assign it to$userid
variable. - Fetch a record from the
users
table whereid
is equals to$userid
. - Loop on the fetched record and initialize
$data
Array withid
,name
,email
, andcity
keys. - Assign
1
to$status
. - Initiailze
$response
Array with$status
and$data
. - Return
$response
Array in JSON format.
Completed Code
<?php include "config.php"; // Read POST data $postData = json_decode(file_get_contents("php://input")); $request = ""; if(isset($postData->request)){ $request = $postData->request; } // Search result if($request == 'suggestionList'){ $search = ""; if(isset($postData->search)){ $search = trim($postData->search); } $return_arr = array(); if(!empty($search)){ $search = mysqli_real_escape_string($con,$search); $sql = "SELECT id,name FROM users where name like '%".$search."%' order by name asc limit 5"; $result = mysqli_query($con,$sql); while($row = mysqli_fetch_assoc($result)){ $id = $row['id']; $name = $row['name']; $return_arr[] = array("id" => $id, "name" => $name); } } echo json_encode($return_arr); die; } // Get User data by ID if($request == 'userdetails'){ $userid = 0; if(isset($postData->userid)){ $userid = $postData->userid; } $data = array(); $status = 0; if(is_numeric($userid) && $userid > 0){ $userid = mysqli_real_escape_string($con,$userid); $sql = "SELECT * FROM users where id=".$userid; $result = mysqli_query($con,$sql); while($row = mysqli_fetch_assoc($result)){ $id = $row['id']; $name = $row['name']; $email = $row['email']; $city = $row['city']; $data = array( "id" => $id, "name" => $name, "email" => $email, "city" => $city, ); $status = 1; } } $response = array( 'status' => $status, 'data' => $data ); echo json_encode($response); die; }
5. JavaScript
Create 2 functions –
- searchResult – This function takes the input value as a parameter. It calls when
keyup
event triggers in the search box. - get_userdetails – This function takes userid as a parameter. It calls when an item is clicked from the suggestion list.
searchResult()
- If search variable value is not empty then send AJAX POST request to
ajaxfile.php
file. Here, pass{request: 'suggestionList',search: search}
asdata
. - On successful callback
JSON.parse()
thethis.responseText
and assign toresponse
variable. - Assign
response.length
tolen
variable. - If
len > 0
then empty the<ul id="searchResult">
and loop on theresponse
. - Create new
<li >
element. Here, passid
tovalue
, andname
toinnerHTML
. - Define
click
event on<li >
to fetch user details on click. - Append created
<li >
element in<ul id="searchResult">
.
get_userdetails
- Read clicked element value and text and assign it to the variables.
- Empty the
<ul id="searchResult">
and<div id="userDetail">
. - Update textbox value with clicked item text.
- Send AJAX POST request to
ajaxfile.php
file. Here, pass{request: 'userdetails',userid: userid}
asdata
. - On successful callback
JSON.parse()
thethis.responseText
and assign toresponse
variable. - If
response.status == 1
then read user data. Create<p >
element to display user id, name, email, and city. - Append created
<p >
elements to<div id="userDetail">
.
Completed Code
// Search results function searchResult(search){ if(search.length > 0){ // AJAX request var xhttp = new XMLHttpRequest(); xhttp.open("POST", "ajaxfile.php", true); xhttp.setRequestHeader("Content-Type", "application/json"); xhttp.onreadystatechange = function() { if (this.readyState == 4 && this.status == 200) { // Response var response = JSON.parse(this.responseText); var len = 0; if(response != null){ len = response.length; } if(len > 0){ // <ul id="searchResult"> element var searchResult = document.getElementById('searchResult'); // Empty the search result searchResult.innerHTML = ""; for( var i = 0; i<len; i++){ var id = response[i]['id']; var name = response[i]['name']; // Add <li > to searchResult var liel = document.createElement('li'); liel.value = id; liel.innerHTML = name; // Add click event to fetch details on click liel.addEventListener('click', get_userdetails); searchResult.appendChild(liel); } } } }; var data = {request: 'suggestionList',search: search}; xhttp.send(JSON.stringify(data)); }else{ document.getElementById('searchResult').innerHTML = ""; } } // Get clicked item Data function get_userdetails(evt){ // Read clicked <li > value and text var userid = evt.currentTarget.value; var text = evt.currentTarget.innerText; // Empty the search result and user Details document.getElementById('searchResult').innerHTML = ""; document.getElementById('userDetail').innerHTML = ""; // Update searchbox text with selected item document.getElementById('txt_search').value = text; // AJAX request var xhttp = new XMLHttpRequest(); xhttp.open("POST", "ajaxfile.php", true); xhttp.setRequestHeader("Content-Type", "application/json"); xhttp.onreadystatechange = function() { if (this.readyState == 4 && this.status == 200) { // Response var response = JSON.parse(this.responseText); // Check status if(response.status == 1){ // Read user data var data = response.data; var id = data.id; var name = data.name; var email = data.email; var city = data.city; // <div id="userDetail"> element var userDetail = document.getElementById('userDetail'); // Append <p > element to <div id="userDetail"> // ID var para_id = document.createElement('p'); para_id.innerHTML = 'ID : ' + id; userDetail.appendChild(para_id); // Name var para_name = document.createElement('p'); para_name.innerHTML = 'NAME : ' + name; userDetail.appendChild(para_name); // Email var para_email = document.createElement('p'); para_email.innerHTML = 'EMAIL : ' + email; userDetail.appendChild(para_email); // City var para_city = document.createElement('p'); para_city.innerHTML = 'City : ' + city; userDetail.appendChild(para_city); } } }; var data = {request: 'userdetails',userid: userid}; xhttp.send(JSON.stringify(data)); }
6. Demo
7. Conclusion
I showed how you can create autocomplete search box with only JavaScript. In the example, I am only loading 5 records in the search result which you can modify while implementing this on your project.
If data is not loading then check the code again and use the browser network tab to debug.
You can view the jQuery version of this tutorial here.
If you found this tutorial helpful then don't forget to share.