JavaScript Autocomplete TextBox (autosuggest) from Database
by Vincy. Last modified on July 3rd, 2023.
AutoComplete is a feature to suggest relevant results on typing into a textbox – for example, Google search textbox autosuggest search phrases on keypress.
It can be enabled using client-side tools and attributes. The data for the autosuggest textbox can be static or dynamic.
The source possibility for loading remote data dynamically is either files or databases. This article uses the database as a source to have dynamic results at the backend.
The below example has an idea for a quick script for enabling the autocomplete feature. It uses JavaScript jQuery and jQuery UI libraries to implement this quickly.
The jQuery autocomplete() uses the PHP endpoint autocomplete.php script. Then, load the remote data into the textbox on the UI.
Example 1: Simple autocomplete
Quick example
<link rel="stylesheet"
href="https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.13.2/themes/base/jquery-ui.min.css" />
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script
src="https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.13.2/jquery-ui.min.js"></script>
<script>
$(document).ready(function(){
$( "#textbox" ).autocomplete({
source: "autocomplete.php",
minLength: 2
});
});
</script>
<input id="textbox" class="full-width" />
This PHP endpoint script reads the database results and forms the output JSON for the autocomplete textbox.
It receives the searched term from the UI and looks into the database for relevant suggestions.
autocomplete.php
<?php
$name = $_GET['term'];
$name = "%$name%";
$conn = mysqli_connect('localhost', 'root', '', 'phppot_autocomplete');
$sql = "SELECT * FROM tbl_post WHERE title LIKE ?";
$statement = $conn->prepare($sql);
$statement->bind_param('s', $name);
$statement->execute();
$result = $statement->get_result();
$autocompleteResult = array();
if (! empty($result)) {
while ($row = $result->fetch_assoc()) {
$autocompleteResult[] = $row["title"];
}
}
print json_encode($autocompleteResult);
?>
This database is for setting up the database created for this quick example. The following example also needs this database for displaying the autosuggest values.
Run the below database queries to get a good experience with the above code execution.
CREATE TABLE `tbl_post` (
`id` int(11) UNSIGNED NOT NULL,
`title` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
--
-- Dumping data for table `tbl_post`
--
INSERT INTO `tbl_post` (`id`, `title`) VALUES
(1, 'Button on click event capture.'),
(2, 'On key press action.'),
(3, 'Overlay dialog window.);
Example 2: Load autocomplete with ID
The AutoComplete function sends an additional parameter with the default term argument. That is to limit the number of results shown in the autocomplete textbox.
It returns the database results based on the searched term as a key-value pair. A JavaScript callback iterates the result and maps the key value as label-value pair.
It is helpful when the result id is required while selecting a particular item from the autosuggest list.
The below screenshot shows the item value and id is populated. This data is put into the textbox on selecting the autocomplete list item.
The below JavaScript code has two textboxes. One textbox is enabled with the autocomplete feature.
The JavaScript autocomplete calls the server-side PHP script when typing into that textbox. The callback gets the JSON output returned by the PHP script.
This JSON data contains an association of dynamic results with their corresponding id. On selecting the autocomplete result item, the select callback function accesses the UI.item object.
Using this object, it gets the id and post title from the JSON data bundle. Then this JavaScript callback function targets the UI textboxes to populate the title and id of the selected item.
<script>
$(document).ready(function() {
$("#textbox").autocomplete({
minlength: 3,
source: function(request, response) {
$.ajax({
url: "get-result-by-additional-param.php",
type: "POST",
dataType: "json",
data: { q: request.term, limit: 10 },
success: function(data) {
response($.map(data, function(item) {
return {
label: item.title,
value: item.postId
};
}));
}
});
},
select: function(event, ui) {
event.preventDefault();
$('#textbox').val(ui.item.label);
$('#itemId').val(ui.item.value);
}
});
});
</script>
<div class="row">
<label>Type for suggestion</label> <input id="textbox"
class="full-width" />
</div>
<div class="row">
<label>Item id</label> <input id="itemId" class="full-width" />
</div>
This PHP script receives the post parameters sent via the autocomplete function.
The search keyword and the result limit are sent from the source callback of the autocomplete initiation.
This PHP script substitutes those parameters into the database query execution process.
Once found the results, it bundles the array into a JSON format to print as an auto-suggestion list.
get-result-by-additional-param.php
<?php
$name = $_POST['q'];
$limit = $_POST['limit'];
$name = "%$name%";
$conn = mysqli_connect('localhost', 'root', '', 'phppot_autocomplete');
$sql = "SELECT * FROM tbl_post WHERE title LIKE ? LIMIT $limit";
$statement = $conn->prepare($sql);
$statement->bind_param('s', $name);
$statement->execute();
$result = $statement->get_result();
$autocompleteResult = array();
if (! empty($result)) {
$i = 0;
while ($row = $result->fetch_assoc()) {
$autocompleteResult[$i]["postId"] = $row["id"];
$autocompleteResult[$i]["title"] = $row["title"];
$i ++;
}
}
print json_encode($autocompleteResult);
?>
Example 3: AutoComplete with recent search
This example shows the autocomplete box with text and image data. The database for this example contains additional details like description and featured_image for the posts.
Use the above two examples for an elegant and straightforward autocomplete solution with text.
This example uses BootStrap and plain JavaScript without jQuery. It displays recent searches on focusing the autocomplete textbox.
Create AutoComplete UI with Bootstrap and JavaScript, Includes
See this HTML loads the autocomplete textbox and required JavaScript and CSS assets for the UI. The autocomplete.js handles the autosuggest request raised from the UI.
The autocomplete textbox has the onKeyPress and onFocus attributes. The onKeyPress attribute calls JavaScript to show an autosuggest list. The other attribute is for displaying recent searches on the focus event of the textbox.
autocomplete-with-search-history/index.php
<link
href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.0/dist/css/bootstrap.min.css"
rel="stylesheet"
integrity="sha384-gH2yIJqKdNHPEq0n4Mqa/HGKIhSkIHeL5AyhkYV8i59U5AR6csBvApHHNl/vI1Bx"
crossorigin="anonymous">
<script src="./assets/autocomplete.js"></script>
<style>
.post-icon {
width: 50px;
height: 50px;
border-radius: 50%;
margin-right: 15px;
}
.remove-link {
font-size: 0.75em;
font-style: italic;
color: #0000FF;
cursor: pointer;
}
</style>
<input id="textbox" class="form-control"
onkeyup="showSuggestionList(this.value)"
onfocus="showRecentSearch()" autocomplete="off" />
<span id="auto-suggestion-box"></span>
Get the autosuggest list from the tbl_post database table
The below JavaScript function is called on the keypress event of the autocomplete field. In the previous examples, it receives a JSON response to load the dynamic suggestion.
This script, it receives the HTML response from the endpoint. This HTML is with an unordered list of autosuggest items.
function showSuggestionList(searchInput) {
if (searchInput.length > 1) {
var xhttp = new XMLHttpRequest();
xhttp.open('POST', 'ajax-endpoint/get-auto-suggestion.php', true);
xhttp.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
xhttp.send("formData=" + searchInput);
xhttp.onreadystatechange = function() {
if (xhttp.readyState == 4 && xhttp.status == 200) {
document.getElementById('auto-suggestion-box').innerHTML = xhttp.responseText;
}
}
}
else {
document.getElementById('auto-suggestion-box').innerHTML = '';
}
}
ajax-endpoint/get-auto-suggestion.php
<?php
require_once __DIR__ . '/../lib/DataSource.php';
$dataSource = new DataSource();
if (isset($_POST["formData"])) {
$searchInput = filter_var($_POST["formData"], FILTER_SANITIZE_STRING);
$highlight = '<b>' . $searchInput . '</b>';
$query = "SELECT * FROM tbl_post WHERE title LIKE ? OR description LIKE ?
ORDER BY id DESC LIMIT 15";
$result = $dataSource->select($query, 'ss', array(
"%" . $searchInput . "%",
"%" . $searchInput . "%"
));
if (! empty($result)) {
?>
<ul class="list-group">
<?php
foreach ($result as $row) {
?>
<li class="list-group-item text-muted"
data-post-id="<?php echo $row["id"]; ?>"
onClick="addToHistory(this)" role="button"><img
class="post-icon" src="<?php echo $row["featured_image"]; ?>" /><span>
<?php echo str_ireplace($searchInput, $highlight, $row["title"]); ?>
</span></li>
<?php
}
?>
</ul>
<?php
}
}
?>
Add to search history
Selecting the suggested list item triggers this JavaScript function on click.
This function reads the post id and title added to the HTML5 data attribute. Then passes, these details to the server-side PHP script.
function addToHistory(obj) {
var selectedResult = obj.dataset.postId;
fetch("ajax-endpoint/add-to-history.php", {
method: "POST",
body: JSON.stringify({
selectedResult: selectedResult
})
}).then(function() {
document.getElementById('textbox').value = obj.innerText;
});
}
This PHP endpoint checks if the selected item is already added to the database history table.
In the database, the tbl_search_history stores the search history.
If data is not found in the database, then the search instance will be added to this table.
ajax-endpoint/add-to-history.php
<?php
require_once __DIR__ . '/../lib/DataSource.php';
$dataSource = new DataSource();
$post_data = json_decode(file_get_contents('php://input'), true);
$selectedResult = filter_var($post_data['selectedResult'], FILTER_SANITIZE_STRING);
if (isset($selectedResult)) {
$query = "SELECT * FROM tbl_search_history, tbl_post
WHERE tbl_search_history.post_id = tbl_post.id
AND tbl_post.id = ?";
$result = $dataSource->select($query, 'i', array(
$selectedResult
));
if (empty($result)) {
$query = "
INSERT INTO tbl_search_history
(post_id) VALUES (?)";
$result = $dataSource->insert($query, 'i', array(
$selectedResult
));
}
}
?>
Show search history by focusing on the autocomplete textbox
This function calls the PHP endpoint to fetch the stored search history. It also receives the HTML response from the server side.
The response HMTL is loaded into the autosuggest textbox in the UI.
function showRecentSearch() {
if (!(document.getElementById('textbox').value)) {
fetch("ajax-endpoint/show-search-history.php", {
method: "POST"
}).then(function(response) {
return response.text();
}).then(function(responseData) {
if (responseData != "") {
document.getElementById('auto-suggestion-box').innerHTML = responseData;
}
});
}
}
This PHP file joins the tbl_post and the tbl_search_history database tables. It is to filter the already searched keyword list.
ajax-endpoint/show-search-history.php
<?php
require_once __DIR__ . '/../lib/DataSource.php';
$dataSource = new DataSource();
$post_data = json_decode(file_get_contents('php://input'), true);
$query = "SELECT tbl_post.* FROM tbl_search_history, tbl_post WHERE tbl_search_history.post_id = tbl_post.id ORDER BY id DESC LIMIT 10";
$result = $dataSource->select($query);
?>
<ul class="list-group">
<?php
foreach ($result as $row) {
?>
<li class="list-group-item text-muted" role="button"><img class="post-icon" src="<?php echo $row["featured_image"]; ?>" /><span><?php echo $row["title"]; ?></span>
<span title="Remove from history" class="remove-link" onClick="removeFromHistory(this, <?php echo $row["id"]; ?>)">[remove]</span></li>
<?php
}
?>
</ul>
Remove history from the autosuggest textbox
The UI will display the recently searched post titles when focusing on the autocomplete textbox.
If the user wants to remove the recent searches, it is possible by this code.
The autosuggest entries have the remove link in the UI. On clicking the link, the corresponding record will be deleted.
function removeFromHistory(obj, postId) {
fetch("ajax-endpoint/remove-history.php", {
method: "POST",
body: JSON.stringify({
postId: postId
})
}).then(function() {
obj.parentNode.remove();
});
}
This PHP code removes the search instances stored in the tbl_search_history database. The delete request posts the record id to fire the delete action.
ajax-endpoint/remove-history.php
<?php
require_once __DIR__ . '/../lib/DataSource.php';
$dataSource = new DataSource();
$post_data = json_decode(file_get_contents('php://input'), true);
$postId = filter_var($post_data['postId'], FILTER_SANITIZE_STRING);
$query = "
DELETE FROM tbl_search_history WHERE post_id = ?";
$result = $dataSource->insert($query, 'i', array(
$postId
));
?>
Written by Vincy, a web developer with 15+ years of experience and a Masters degree in Computer Science. She specializes in building modern, lightweight websites using PHP, JavaScript, React, and related technologies. Phppot helps you in mastering web development through over a decade of publishing quality tutorials.
Hi, I'm Vincy. I help build websites, grow businesses, big and small. If that is what you want, contact me.
I'm currently available for freelance work.