Create an account


Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
[Tut] JavaScript Autocomplete TextBox (autosuggest) from Database

#1
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">&lt;link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.13.2/themes/base/jquery-ui.min.css" /&gt;
&lt;script src="https://code.jquery.com/jquery-3.6.0.min.js"&gt;&lt;/script&gt;
&lt;script src="https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.13.2/jquery-ui.min.js"&gt;&lt;/script&gt;
&lt;script&gt;
$(document).ready(function(){ $( "#textbox" ).autocomplete({ source: "autocomplete.php", minLength: 2 });
});
&lt;/script&gt;
&lt;input id="textbox" class="full-width" /&gt;
</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">&lt;?php
$name = $_GET['term'];
$name = "%$name%";
$conn = mysqli_connect('localhost', 'root', '', 'phppot_autocomplete');
$sql = "SELECT * FROM tbl_post WHERE title LIKE ?";
$statement = $conn-&gt;prepare($sql);
$statement-&gt;bind_param('s', $name);
$statement-&gt;execute();
$result = $statement-&gt;get_result();
$autocompleteResult = array();
if (! empty($result)) { while ($row = $result-&gt;fetch_assoc()) { $autocompleteResult[] = $row["title"]; }
}
print json_encode($autocompleteResult);
?&gt;
</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/20...00x165.jpg 300w, https://phppot.com/wp-content/uploads/20...68x422.jpg 768w, https://phppot.com/wp-content/uploads/20...mplete.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/20...00x162.jpg 300w, https://phppot.com/wp-content/uploads/20...ith-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&nbsp;<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">&lt;script&gt;
$(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); } });
});
&lt;/script&gt;
&lt;div class="row"&gt; &lt;label&gt;Type for suggestion&lt;/label&gt; &lt;input id="textbox" class="full-width" /&gt;
&lt;/div&gt;
&lt;div class="row"&gt; &lt;label&gt;Item id&lt;/label&gt; &lt;input id="itemId" class="full-width" /&gt;
&lt;/div&gt;
</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">&lt;?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-&gt;prepare($sql);
$statement-&gt;bind_param('s', $name);
$statement-&gt;execute();
$result = $statement-&gt;get_result();
$autocompleteResult = array();
if (! empty($result)) { $i = 0; while ($row = $result-&gt;fetch_assoc()) { $autocompleteResult[$i]["postId"] = $row["id"]; $autocompleteResult[$i]["title"] = $row["title"]; $i ++; }
}
print json_encode($autocompleteResult);
?&gt;
</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&nbsp;<em>onKeyPress</em> and&nbsp;<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">&lt;link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-gH2yIJqKdNHPEq0n4Mqa/HGKIhSkIHeL5AyhkYV8i59U5AR6csBvApHHNl/vI1Bx" crossorigin="anonymous"&gt;
&lt;script src="./assets/autocomplete.js"&gt;&lt;/script&gt;
&lt;style&gt;
.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;
}
&lt;/style&gt;
&lt;input id="textbox" class="form-control" onkeyup="showSuggestionList(this.value)" onfocus="showRecentSearch()" autocomplete="off" /&gt;
&lt;span id="auto-suggestion-box"&gt;&lt;/span&gt;
</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 &amp;gt; 1) { var xhttp = new XMLHttpRequest(); xhttp.open('POST', 'ajax-endpoint/get-auto-suggestion.php', true); xhttp.setRequestHeader(&amp;quot;Content-type&amp;quot;, &amp;quot;application/x-www-form-urlencoded&amp;quotWink; xhttp.send(&amp;quot;formData=&amp;quot; + searchInput); xhttp.onreadystatechange = function() { if (xhttp.readyState == 4 &amp;amp;&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">&lt;?php
require_once __DIR__ . '/../lib/DataSource.php';
$dataSource = new DataSource(); if (isset($_POST["formData"])) { $searchInput = filter_var($_POST["formData"], FILTER_SANITIZE_STRING); $highlight = '&lt;b&gt;' . $searchInput . '&lt;/b&gt;'; $query = "SELECT * FROM tbl_post WHERE title LIKE ? OR description LIKE ? ORDER BY id DESC LIMIT 15"; $result = $dataSource-&gt;select($query, 'ss', array( "%" . $searchInput . "%", "%" . $searchInput . "%" )); if (! empty($result)) { ?&gt;
&lt;ul class="list-group"&gt;
&lt;?php foreach ($result as $row) { ?&gt; &lt;li class="list-group-item text-muted" data-post-id="&lt;?php echo $row["id"]; ?&gt;" onClick="addToHistory(this)" role="button"&gt;&lt;img class="post-icon" src="&lt;?php echo $row["featured_image"]; ?&gt;" /&gt;&lt;span&gt; &lt;?php echo str_ireplace($searchInput, $highlight, $row["title"]); ?&gt; &lt;/span&gt;&lt;/li&gt;
&lt;?php } ?&gt;
&lt;/ul&gt;
&lt;?php }
}
?&gt;
</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/20...00x152.jpg 300w, https://phppot.com/wp-content/uploads/20...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">&lt;?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-&gt;select($query, 'i', array( $selectedResult )); if (empty($result)) { $query = " INSERT INTO tbl_search_history (post_id) VALUES (?)"; $result = $dataSource-&gt;insert($query, 'i', array( $selectedResult )); }
}
?&gt;
</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&nbsp;<em>tbl_post</em> and the&nbsp;<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">&lt;?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-&gt;select($query); ?&gt;
&lt;ul class="list-group"&gt;
&lt;?php foreach ($result as $row) { ?&gt; &lt;li class="list-group-item text-muted" role="button"&gt;&lt;img class="post-icon" src="&lt;?php echo $row["featured_image"]; ?&gt;" /&gt;&lt;span&gt;&lt;?php echo $row["title"]; ?&gt;&lt;/span&gt; &lt;span title="Remove from history" class="remove-link" onClick="removeFromHistory(this, &lt;?php echo $row["id"]; ?&gtWink"&gt;[remove]&lt;/span&gt;&lt;/li&gt;
&lt;?php } ?&gt;
&lt;/ul&gt;
</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">&lt;?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-&gt;insert($query, 'i', array( $postId
));
?&gt;
</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/20...00x110.jpg 300w, https://phppot.com/wp-content/uploads/20...istory.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/...-database/
Reply



Forum Jump:


Users browsing this thread:
1 Guest(s)

Forum software by © MyBB Theme © iAndrew 2016