{"id":127142,"date":"2022-08-09T17:56:09","date_gmt":"2022-08-09T17:56:09","guid":{"rendered":"https:\/\/phppot.com\/?p=18826"},"modified":"2022-08-09T17:56:09","modified_gmt":"2022-08-09T17:56:09","slug":"javascript-autocomplete-textbox-autosuggest-from-database","status":"publish","type":"post","link":"https:\/\/sickgaming.net\/blog\/2022\/08\/09\/javascript-autocomplete-textbox-autosuggest-from-database\/","title":{"rendered":"JavaScript Autocomplete TextBox (autosuggest) from Database"},"content":{"rendered":"
by Vincy<\/a>. Last modified on August 9th, 2022.<\/div>\n

AutoComplete is a feature to suggest relevant results on typing into a textbox. For example, Google search textbox autosuggest search phrases on keypress.<\/p>\n

It can be enabled using client-side tools and attributes. The data for the autosuggest textbox can be static or dynamic.<\/p>\n

For loading remote data dynamically, the source possibility is either files or databases. This article uses the database as a source to have dynamic results<\/a> at the backend.<\/p>\n

The below example has an idea for a quick script for enabling the autocomplete feature. It uses JavaScript jQuery and jQuery UI<\/a> libraries to implement this easily.<\/p>\n

The jQuery autocomplete() uses the PHP endpoint autocomplete.php <\/em>script. Then, load the remote data into the textbox on the UI.<\/p>\n

View Demo<\/a><\/p>\n

Example 1: Simple autocomplete<\/h2>\n
\n

Quick example<\/h3>\n
<link rel=\"stylesheet\" href=\"https:\/\/cdnjs.cloudflare.com\/ajax\/libs\/jqueryui\/1.13.2\/themes\/base\/jquery-ui.min.css\" \/>\n<script src=\"https:\/\/code.jquery.com\/jquery-3.6.0.min.js\"><\/script>\n<script src=\"https:\/\/cdnjs.cloudflare.com\/ajax\/libs\/jqueryui\/1.13.2\/jquery-ui.min.js\"><\/script>\n<script>\n$(document).ready(function(){ $( \"#textbox\" ).autocomplete({ source: \"autocomplete.php\", minLength: 2 });\n});\n<\/script>\n<input id=\"textbox\" class=\"full-width\" \/>\n<\/code><\/pre>\n<\/div>\n
This PHP endpoint script reads the database results<\/a> and forms the output JSON for the autocomplete textbox.<\/p>\n
It receives the searched term from the UI and looks into the database for relevant suggestions.<\/p>\n
autocomplete.php<\/p>\n
<?php\n$name = $_GET['term'];\n$name = \"%$name%\";\n$conn = mysqli_connect('localhost', 'root', '', 'phppot_autocomplete');\n$sql = \"SELECT * FROM tbl_post WHERE title LIKE ?\";\n$statement = $conn->prepare($sql);\n$statement->bind_param('s', $name);\n$statement->execute();\n$result = $statement->get_result();\n$autocompleteResult = array();\nif (! empty($result)) { while ($row = $result->fetch_assoc()) { $autocompleteResult[] = $row[\"title\"]; }\n}\nprint json_encode($autocompleteResult);\n?>\n<\/code><\/pre>\n
This database is for setting up the database created for this quick example. The next example also needs this database for displaying the autosuggest values.<\/p>\n
Run the below database queries for getting a good experience with the above code execution.<\/p>\n
CREATE TABLE `tbl_post` ( `id` int(11) UNSIGNED NOT NULL, `title` text DEFAULT NULL\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; --\n-- Dumping data for table `tbl_post`\n-- INSERT INTO `tbl_post` (`id`, `title`) VALUES\n(1, 'Button on click event capture.'),\n(2, 'On key press action.'),\n(3, 'Overlay dialog window.);\n<\/code><\/pre>\n
\"javascript
Example 2: Load autocomplete with ID<\/h2>\n
The AutoComplete function sends an additional parameter with the default term<\/em> argument. That is to limit the number of results shown in the autocomplete textbox.<\/p>\n
It returns the database results based on the searched term<\/a> as a key-value pair. A JavaScript callback iterates the result and maps the key-value as label-value pair.<\/p>\n
It is helpful when the result id is required while selecting a particular item from the autosuggest list.<\/p>\n
The below screenshot shows the item value and id is populated. This data is put into the textbox on selecting the autocomplete list item.<\/p>\n
\"autocomplete<\/p>\n
The below JavaScript code has two textboxes. One textbox is enabled with the autocomplete feature.<\/p>\n
On typing into that textbox, the JavaScript autocomplete calls the server-side PHP script. The callback gets the JSON output returned by the PHP script.<\/p>\n
This JSON data contains an association of dynamic results with their corresponding id. On selecting the autocomplete result item, the select<\/em> callback function access the UI.item<\/em> object.<\/p>\n
Using this object, it gets the id and post title from the JSON data bundle<\/a>. Then this JavaScript callback function targets the UI textboxes to populate the title and id of the selected item.<\/p>\n
<script>\n$(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); } });\n});\n<\/script>\n<div class=\"row\"> <label>Type for suggestion<\/label> <input id=\"textbox\" class=\"full-width\" \/>\n<\/div>\n<div class=\"row\"> <label>Item id<\/label> <input id=\"itemId\" class=\"full-width\" \/>\n<\/div>\n<\/code><\/pre>\n
This PHP script receives the post parameters sent via the autocomplete function.<\/p>\n
The search keyword and the result limit are sent from the source callback of the autocomplete initiation.<\/p>\n
This PHP script substitutes those parameters into the database query execution process.<\/p>\n
Once found the results, it bundles the array into a JSON format to print as an auto-suggestion list.<\/p>\n
get-result-by-additional-param.php<\/p>\n
<?php\n$name = $_POST['q'];\n$limit = $_POST['limit'];\n$name = \"%$name%\";\n$conn = mysqli_connect('localhost', 'root', '', 'phppot_autocomplete');\n$sql = \"SELECT * FROM tbl_post WHERE title LIKE ? LIMIT $limit\";\n$statement = $conn->prepare($sql);\n$statement->bind_param('s', $name);\n$statement->execute();\n$result = $statement->get_result();\n$autocompleteResult = array();\nif (! empty($result)) { $i = 0; while ($row = $result->fetch_assoc()) { $autocompleteResult[$i][\"postId\"] = $row[\"id\"]; $autocompleteResult[$i][\"title\"] = $row[\"title\"]; $i ++; }\n}\nprint json_encode($autocompleteResult);\n?>\n<\/code><\/pre>\n
Example 3: AutoComplete with recent search<\/h2>\n
This example shows the autocomplete box with text and image data. The database for this example contains additional details like description<\/em> and featured_image<\/em> for the posts.<\/p>\n
If you want a sleek and straightforward autocomplete solution with text, then use the above two examples.<\/p>\n
This example uses BootStrap and plain JavaScript without jQuery<\/strong>. It displays recent searches<\/a> on focusing the autocomplete textbox.<\/p>\n
Create AutoComplete UI with Bootstrap and JavaScript Includes<\/h3>\n
See this HTML loads the autocomplete textbox and required JavaScript and CSS assets for the UI. The autocomplete.js<\/em> handles the autosuggest request raised from the UI.<\/p>\n
The autocomplete textbox has the onKeyPress<\/em> and onFocus<\/em> attributes. The onKeyPress<\/em> attribute calls JavaScript to show an autosuggest list. The other attribute is for displaying recent searches on the focus event of the textbox.<\/p>\n
autocomplete-with-search-history\/index.php<\/p>\n
<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\">\n<script src=\".\/assets\/autocomplete.js\"><\/script>\n<style>\n.post-icon { width: 50px; height: 50px; border-radius: 50%; margin-right: 15px;\n} .remove-link { font-size: 0.75em; font-style: italic; color: #0000FF; cursor: pointer;\n}\n<\/style>\n<input id=\"textbox\" class=\"form-control\" onkeyup=\"showSuggestionList(this.value)\" onfocus=\"showRecentSearch()\" autocomplete=\"off\" \/>\n<span id=\"auto-suggestion-box\"><\/span>\n<\/code><\/pre>\n
Get the autosuggest list from the tbl_post database table<\/h3>\n
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.<\/p>\n
In this script, it receives the HTML response from the endpoint. This HTML is with an unordered list of autosuggest items.<\/p>\n
function showSuggestionList(searchInput) { if (searchInput.length &gt; 1) { var xhttp = new XMLHttpRequest(); xhttp.open('POST', 'ajax-endpoint\/get-auto-suggestion.php', true); xhttp.setRequestHeader(&quot;Content-type&quot;, &quot;application\/x-www-form-urlencoded&quot;); xhttp.send(&quot;formData=&quot; + searchInput); xhttp.onreadystatechange = function() { if (xhttp.readyState == 4 &amp;&amp; xhttp.status == 200) { document.getElementById('auto-suggestion-box').innerHTML = xhttp.responseText; } } } else { document.getElementById('auto-suggestion-box').innerHTML = ''; }\n}\n<\/code><\/pre>\n
ajax-endpoint\/get-auto-suggestion.php<\/p>\n
<?php\nrequire_once __DIR__ . '\/..\/lib\/DataSource.php';\n$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)) { ?>\n<ul class=\"list-group\">\n<?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>\n<?php } ?>\n<\/ul>\n<?php }\n}\n?>\n<\/code><\/pre>\n
\"javascript<\/p>\n
Add to search history<\/h3>\n
When selecting the suggested list item, it triggers this JavaScript function on click.<\/p>\n
This function reads the post id and title added to the HTML5 data attribute<\/a>. Then passes these details to the server-side PHP script.<\/p>\n
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; });\n}\n<\/code><\/pre>\n
This PHP endpoint checks if the selected item is already added to the database history table.<\/p>\n
In the database, the tbl_search_history<\/em> stores the search history.<\/p>\n
If data is not found in the database, then the search instance will be added to this table.<\/p>\n
ajax-endpoint\/add-to-history.php<\/p>\n
<?php\nrequire_once __DIR__ . '\/..\/lib\/DataSource.php';\n$dataSource = new DataSource(); $post_data = json_decode(file_get_contents('php:\/\/input'), true);\n$selectedResult = filter_var($post_data['selectedResult'], FILTER_SANITIZE_STRING);\nif (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 )); }\n}\n?>\n<\/code><\/pre>\n
Show search history by focusing on the autocomplete textbox<\/h3>\n
This function calls the PHP endpoint to fetch the stored search history. It also receives the HTML response from the server side.<\/p>\n
The response HMTL is loaded into the autosuggest textbox in the UI.<\/p>\n
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; } }); }\n}\n<\/code><\/pre>\n
In this PHP file, it joins the tbl_post<\/em> and the tbl_search_history<\/em> database tables. It is to filter the already searched keyword list.<\/p>\n
ajax-endpoint\/show-search-history.php<\/p>\n
<?php\nrequire_once __DIR__ . '\/..\/lib\/DataSource.php';\n$dataSource = new DataSource(); $post_data = json_decode(file_get_contents('php:\/\/input'), true);\n$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); ?>\n<ul class=\"list-group\">\n<?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>\n<?php } ?>\n<\/ul>\n<\/code><\/pre>\n
Remove history from the autosuggest textbox<\/h3>\n
When focusing on the autocomplete textbox, the UI will display the recently searched post titles.<\/p>\n
If the user wants to remove the recent searches, it is possible by this code.<\/p>\n
The autosuggest entries have the remove<\/em> link in the UI. On clicking the link, the corresponding record will be deleted.<\/p>\n
function removeFromHistory(obj, postId) { fetch(\"ajax-endpoint\/remove-history.php\", { method: \"POST\", body: JSON.stringify({ postId: postId }) }).then(function() { obj.parentNode.remove(); });\n}\n<\/code><\/pre>\n
This PHP code removes the search instances stored in the tbl_search_history<\/em> database. The delete request posts the record id to fire the delete action<\/a>.<\/p>\n
ajax-endpoint\/remove-history.php<\/p>\n
<?php\nrequire_once __DIR__ . '\/..\/lib\/DataSource.php';\n$dataSource = new DataSource(); $post_data = json_decode(file_get_contents('php:\/\/input'), true);\n$postId = filter_var($post_data['postId'], FILTER_SANITIZE_STRING);\n$query = \" DELETE FROM tbl_search_history WHERE post_id = ?\"; $result = $dataSource->insert($query, 'i', array( $postId\n));\n?>\n<\/code><\/pre>\n
\"autocomplete
View Demo<\/a>Download<\/a><\/p>\n
  <\/p>\n
\n
Popular Articles<\/h2>\n<\/p><\/div>\n
 \u2191 Back to Top<\/a> <\/p>\n","protected":false},"excerpt":{"rendered":"
by Vincy. Last modified on August 9th, 2022. 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. For loading remote data […]<\/p>\n","protected":false},"author":2,"featured_media":127143,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[65],"tags":[],"class_list":["post-127142","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-php-updates"],"_links":{"self":[{"href":"https:\/\/sickgaming.net\/blog\/wp-json\/wp\/v2\/posts\/127142","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sickgaming.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sickgaming.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sickgaming.net\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/sickgaming.net\/blog\/wp-json\/wp\/v2\/comments?post=127142"}],"version-history":[{"count":0,"href":"https:\/\/sickgaming.net\/blog\/wp-json\/wp\/v2\/posts\/127142\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sickgaming.net\/blog\/wp-json\/wp\/v2\/media\/127143"}],"wp:attachment":[{"href":"https:\/\/sickgaming.net\/blog\/wp-json\/wp\/v2\/media?parent=127142"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sickgaming.net\/blog\/wp-json\/wp\/v2\/categories?post=127142"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sickgaming.net\/blog\/wp-json\/wp\/v2\/tags?post=127142"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}