+- Sick Gaming (https://www.sickgaming.net)
+-- Forum: Programming (https://www.sickgaming.net/forum-76.html)
+--- Forum: PHP Development (https://www.sickgaming.net/forum-82.html)
+--- Thread: [Tut] JavaScript Autocomplete TextBox (autosuggest) from Database (/thread-99787.html)
[Tut] JavaScript Autocomplete TextBox (autosuggest) from Database - xSicKxBot - 08-10-2022
JavaScript Autocomplete TextBox (autosuggest) from Database
<div style="margin: 5px 5% 10px 5%;"><img src="https://www.sickgaming.net/blog/wp-content/uploads/2022/08/javascript-autocomplete-textbox-autosuggest-from-database.jpg" width="550" height="303" title="" alt="" /></div><div><div class="modified-on" readability="7.0909090909091"> by <a href="https://phppot.com/about/">Vincy</a>. Last modified on August 9th, 2022.</div>
<p>AutoComplete is a feature to suggest relevant results on typing into a textbox. For example, Google search textbox autosuggest search phrases on keypress.</p>
<p>It can be enabled using client-side tools and attributes. The data for the autosuggest textbox can be static or dynamic.</p>
<p>For loading remote data dynamically, the source possibility is either files or databases. This article uses the database as a <a href="https://phppot.com/php/how-to-generate-dynamic-xml-sitemap-for-website-in-php/">source to have dynamic results</a> at the backend.</p>
<p>The below example has an idea for a quick script for enabling the autocomplete feature. It uses JavaScript <a href="https://jqueryui.com/autocomplete/" target="_blank" rel="noopener">jQuery and jQuery UI</a> libraries to implement this easily.</p>
<p>The jQuery autocomplete() uses the PHP endpoint <em>autocomplete.php </em>script. Then, load the remote data into the textbox on the UI.</p>
<p><a class="demo" href="https://phppot.com/demo/javascript-autocomplete-textbox-database/">View Demo</a></p>
<h2>Example 1: Simple autocomplete</h2>
<div class="post-section-highlight" readability="36">
<h3>Quick example</h3>
<pre class="prettyprint"><code class="language-html"><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" />
</code></pre>
</div>
<p>This PHP endpoint script <a href="https://phppot.com/mysql/mysql-fetch-using-php/">reads the database results</a> and forms the output JSON for the autocomplete textbox.</p>
<p>It receives the searched term from the UI and looks into the database for relevant suggestions.</p>
<p class="code-heading">autocomplete.php</p>
<pre class="prettyprint"><code class="language-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);
?>
</code></pre>
<p>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>
<p>Run the below database queries for getting a good experience with the above code execution.</p>
<pre class="prettyprint"><code class="language-sql">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.);
</code></pre>
<h2><img loading="lazy" class="alignnone size-large wp-image-18836" src="https://phppot.com/wp-content/uploads/2022/08/javascript-autocomplete-550x303.jpg" alt="javascript autocomplete" width="550" height="303" srcset="https://phppot.com/wp-content/uploads/2022/08/javascript-autocomplete-550x303.jpg 550w, https://phppot.com/wp-content/uploads/2022/08/javascript-autocomplete-300x165.jpg 300w, https://phppot.com/wp-content/uploads/2022/08/javascript-autocomplete-768x422.jpg 768w, https://phppot.com/wp-content/uploads/2022/08/javascript-autocomplete.jpg 800w" sizes="(max-width: 550px) 100vw, 550px"><br />Example 2: Load autocomplete with ID</h2>
<p>The AutoComplete function sends an additional parameter with the default <em>term</em> argument. That is to limit the number of results shown in the autocomplete textbox.</p>
<p>It returns the database <a href="https://phppot.com/php/advanced-search-using-php/">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>
<p>It is helpful when the result id is required while selecting a particular item from the autosuggest list.</p>
<p>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>
<p><img loading="lazy" class="alignnone size-large wp-image-18838" src="https://phppot.com/wp-content/uploads/2022/08/autocomplete-result-with-id-550x296.jpg" alt="autocomplete result with id" width="550" height="296" srcset="https://phppot.com/wp-content/uploads/2022/08/autocomplete-result-with-id-550x296.jpg 550w, https://phppot.com/wp-content/uploads/2022/08/autocomplete-result-with-id-300x162.jpg 300w, https://phppot.com/wp-content/uploads/2022/08/autocomplete-result-with-id.jpg 600w" sizes="(max-width: 550px) 100vw, 550px"></p>
<p>The below JavaScript code has two textboxes. One textbox is enabled with the autocomplete feature.</p>
<p>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>
<p>This JSON data contains an association of dynamic results with their corresponding id. On selecting the autocomplete result item, the <em>select</em> callback function access the <em>UI.item</em> object.</p>
<p>Using this object, it gets the id and post title from the <a href="https://phppot.com/php/json-handling-with-php-how-to-encode-write-parse-decode-and-convert/">JSON data bundle</a>. Then this JavaScript callback function targets the UI textboxes to populate the title and id of the selected item.</p>
<pre class="prettyprint"><code class="language-html"><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>
</code></pre>
<p>This PHP script receives the post parameters sent via the autocomplete function.</p>
<p>The search keyword and the result limit are sent from the source callback of the autocomplete initiation.</p>
<p>This PHP script substitutes those parameters into the database query execution process.</p>
<p>Once found the results, it bundles the array into a JSON format to print as an auto-suggestion list.</p>
<p class="code-heading">get-result-by-additional-param.php</p>
<pre class="prettyprint"><code class="language-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);
?>
</code></pre>
<h2>Example 3: AutoComplete with recent search</h2>
<p>This example shows the autocomplete box with text and image data. The database for this example contains additional details like <em>description</em> and <em>featured_image</em> for the posts.</p>
<p>If you want a sleek and straightforward autocomplete solution with text, then use the above two examples.</p>
<p>This example uses BootStrap and plain <strong>JavaScript without jQuery</strong>. It displays <a href="https://phppot.com/php/bootstrap-ecommerce-recently-viewed-products-list-carousel/">recent searches</a> on focusing the autocomplete textbox.</p>
<h3>Create AutoComplete UI with Bootstrap and JavaScript Includes</h3>
<p>See this HTML loads the autocomplete textbox and required JavaScript and CSS assets for the UI. The <em>autocomplete.js</em> handles the autosuggest request raised from the UI.</p>
<p>The autocomplete textbox has the <em>onKeyPress</em> and <em>onFocus</em> attributes. The <em>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>
<p class="code-heading">autocomplete-with-search-history/index.php</p>
<pre class="prettyprint"><code class="language-html"><link href="https://cdn.jsdelivr.net/npm/[email protected]/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>
</code></pre>
<h3>Get the autosuggest list from the tbl_post database table</h3>
<p>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>
<p>In this script, it receives the HTML response from the endpoint. This HTML is with an unordered list of autosuggest items.</p>
<pre class="prettyprint"><code class="language-javascript">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 = ''; }
}
</code></pre>
<p class="code-heading">ajax-endpoint/get-auto-suggestion.php</p>
<pre class="prettyprint"><code class="language-php-template"><?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 }
}
?>
</code></pre>
<p><img loading="lazy" class="alignnone size-large wp-image-18846" src="https://phppot.com/wp-content/uploads/2022/08/javascript-autocomplete-without-jquery-550x278.jpg" alt="javascript autocomplete without jquery" width="550" height="278" srcset="https://phppot.com/wp-content/uploads/2022/08/javascript-autocomplete-without-jquery-550x278.jpg 550w, https://phppot.com/wp-content/uploads/2022/08/javascript-autocomplete-without-jquery-300x152.jpg 300w, https://phppot.com/wp-content/uploads/2022/08/javascript-autocomplete-without-jquery.jpg 600w" sizes="(max-width: 550px) 100vw, 550px"></p>
<h3>Add to search history</h3>
<p>When selecting the suggested list item, it triggers this JavaScript function on click.</p>
<p>This function reads the post id and title added to the <a href="https://phppot.com/jquery/read-html5-data-attribute-via-jquery/">HTML5 data attribute</a>. Then passes these details to the server-side PHP script.</p>
<pre class="prettyprint"><code class="language-javascript">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; });
}
</code></pre>
<p>This PHP endpoint checks if the selected item is already added to the database history table.</p>
<p>In the database, the <em>tbl_search_history</em> stores the search history.</p>
<p>If data is not found in the database, then the search instance will be added to this table.</p>
<p class="code-heading">ajax-endpoint/add-to-history.php</p>
<pre class="prettyprint"><code class="language-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 )); }
}
?>
</code></pre>
<h3>Show search history by focusing on the autocomplete textbox</h3>
<p>This function calls the PHP endpoint to fetch the stored search history. It also receives the HTML response from the server side.</p>
<p>The response HMTL is loaded into the autosuggest textbox in the UI.</p>
<pre class="prettyprint"><code class="language-javascript">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; } }); }
}
</code></pre>
<p>In this PHP file, it joins the <em>tbl_post</em> and the <em>tbl_search_history</em> database tables. It is to filter the already searched keyword list.</p>
<p class="code-heading">ajax-endpoint/show-search-history.php</p>
<pre class="prettyprint"><code class="language-php-template"><?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>
</code></pre>
<h3>Remove history from the autosuggest textbox</h3>
<p>When focusing on the autocomplete textbox, the UI will display the recently searched post titles.</p>
<p>If the user wants to remove the recent searches, it is possible by this code.</p>
<p>The autosuggest entries have the <em>remove</em> link in the UI. On clicking the link, the corresponding record will be deleted.</p>
<pre class="prettyprint"><code class="language-javascript">function removeFromHistory(obj, postId) { fetch("ajax-endpoint/remove-history.php", { method: "POST", body: JSON.stringify({ postId: postId }) }).then(function() { obj.parentNode.remove(); });
}
</code></pre>
<p>This PHP code removes the search instances stored in the <em>tbl_search_history</em> database. The delete request posts the record id to fire the <a href="https://phppot.com/php/how-to-create-comment-system-with-delete-using-php-jquery/">delete action</a>.</p>
<p class="code-heading">ajax-endpoint/remove-history.php</p>
<pre class="prettyprint"><code class="language-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
));
?>
</code></pre>
<p><img loading="lazy" class="alignnone size-large wp-image-18847" src="https://phppot.com/wp-content/uploads/2022/08/autocomplete-with-search-history-550x202.jpg" alt="autocomplete with search history" width="550" height="202" srcset="https://phppot.com/wp-content/uploads/2022/08/autocomplete-with-search-history-550x202.jpg 550w, https://phppot.com/wp-content/uploads/2022/08/autocomplete-with-search-history-300x110.jpg 300w, https://phppot.com/wp-content/uploads/2022/08/autocomplete-with-search-history.jpg 600w" sizes="(max-width: 550px) 100vw, 550px"><br /><a class="demo" href="https://phppot.com/demo/javascript-autocomplete-textbox-database/">View Demo</a><a class="download" href="https://phppot.com/downloads/javascript-autocomplete-textbox-database.zip">Download</a></p>
<p> <!-- #comments --> </p>
<div class="related-articles">
<h2>Popular Articles</h2>
</p></div>
<p> <a href="https://phppot.com/javascript/javascript-autocomplete-textbox-database/#top" class="top">↑ Back to Top</a> </p>
</div>
https://www.sickgaming.net/blog/2022/08/09/javascript-autocomplete-textbox-autosuggest-from-database/