Create an account


Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
[-]
Welcome
You have to register before you can post on our site.

Username:


Password:





[-]
Discord Widget

[-]
Twitter: SickGaming

[-]
Latest Threads
[Tut] Python Find Shortest List in List
Last Post: xSicKxBot
Today 02:42 AM
» Replies: 0
» Views: 0
News - Sly Cooper Celebrates 20 Years Wi...
Last Post: xSicKxBot
Today 02:41 AM
» Replies: 0
» Views: 0
PC - Isonzo
Last Post: xSicKxBot
Today 02:41 AM
» Replies: 0
» Views: 0
[Tut] How to Delete a Line from a File i...
Last Post: xSicKxBot
Yesterday 09:31 AM
» Replies: 0
» Views: 0
[Tut] PHP Excel Export Code (Data to Fil...
Last Post: xSicKxBot
Yesterday 09:31 AM
» Replies: 0
» Views: 0
News - Pierce Brosnan Doesn't Care Who T...
Last Post: xSicKxBot
Yesterday 09:30 AM
» Replies: 0
» Views: 0
PC - Broken Pieces
Last Post: xSicKxBot
Yesterday 09:30 AM
» Replies: 0
» Views: 0
[Tut] Python Find Longest List in List
Last Post: xSicKxBot
09-23-2022 01:19 PM
» Replies: 0
» Views: 0
[Tut] How to Capture Screenshot of Page ...
Last Post: xSicKxBot
09-23-2022 01:19 PM
» Replies: 0
» Views: 0
(Indie Deal) Fantasy Idols Bundle, HITMA...
Last Post: xSicKxBot
09-23-2022 01:19 PM
» Replies: 0
» Views: 0

[Tut] JavaScript Autocomplete TextBox (autosuggest) from Database

#1
JavaScript Autocomplete TextBox (autosuggest) from Database

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 dynamically, the source possibility 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 easily.

The jQuery autocomplete() uses the PHP endpoint autocomplete.php script. Then, load the remote data into the textbox on the UI.

View Demo

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 next example also needs this database for displaying the autosuggest values.

Run the below database queries for getting 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.);

javascript autocomplete
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.

autocomplete result with id

The below JavaScript code has two textboxes. One textbox is enabled with the autocomplete feature.

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.

This JSON data contains an association of dynamic results with their corresponding id. On selecting the autocomplete result item, the select callback function access 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.

If you want a sleek and straightforward autocomplete solution with text, then use the above two examples.

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" on‌keyup="showSuggestionList(this.value)" on‌focus="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.

In 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 &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 = ''; }
}

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"]; ?>" on‌Click="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 }
}
?>

javascript autocomplete without jquery

Add to search history


When selecting the suggested list item, it 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; } }); }
}

In this PHP file, it 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" on‌Click="removeFromHistory(this, <?php echo $row["id"]; ?>)">[remove]</span></li>
<?php } ?>
</ul>

Remove history from the autosuggest textbox


When focusing on the autocomplete textbox, the UI will display the recently searched post titles.

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
));
?>

autocomplete with search history
View DemoDownload

↑ Back to Top



https://www.sickgaming.net/blog/2022/08/...-database/
Reply





Users browsing this thread:
1 Guest(s)

𝚂̷𝚒̷𝚌̷𝚔̷𝙶̷𝚊̷𝚖̷𝚒̷𝚗̷𝚐̷ 𝙷̷𝚘̷𝚖̷𝚎̷

Copyright © SickGaming.net 2022