d

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore.

15 St Margarets, NY 10033
(+381) 11 123 4567
ouroffice@aware.com

 

KMF

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"
    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.

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="https://phppot.com/javascript/javascript-autocomplete-textbox-database/<?php echo $row["id"]; ?>"
        onClick="addToHistory(this)" role="button"><img
        class="post-icon" src="https://phppot.com/javascript/javascript-autocomplete-textbox-database/<?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="https://phppot.com/javascript/javascript-autocomplete-textbox-database/<?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

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

Credit: Source link

Previous Next
Close
Test Caption
Test Description goes like this