HTML form input auto complete with PHP and MySQL

How to Autofill or auto-complete an HTML input form using JQuery with PHP and MySQL.

This method uses LIKE in a MySQL query for the typed in characters and returns the matches in a drop-down below the input for selection to fill into the input.

To do this you will need jquery along with jquery UI JS and CSS. These can be easily obtained with cdnjs.

HTML & JS

form.html :

<html lang="en">
<head>
    <title>Form input auto complete</title>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.12.1/jquery-ui.css"/>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.js"></script>
    <script>
        $(function () {
            $("#item_input").autocomplete({
                source: "input_search.php",
            });
        });
    </script>
</head>
<body>
<label for="item_input">Item: </label>
<input type="text" id="item_input"/>
</body>
</html>

form.html includes the Javascript and CSS files to auto-complete the input form.

PHP & MySQL

input_search.php :

<?php
$db = new PDO('mysql:host=127.0.0.1;dbname=DATABASE;charset=utf8mb4', 'USERNAME', 'PASSWORD');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

if (isset($_GET['term'])) {
    $search_for = $_GET['term'];

    $select = $db->prepare("SELECT `item` FROM `products` WHERE `item` LIKE ? LIMIT 50;");
    $select->execute(array("%$search_for%"));
    $data = $select->fetchAll();

    $items = array();
    foreach ($data as $an_item) {
        $items[] = $an_item['item'];
    }
    echo json_encode($items);
}

input_search.php returns an array from the MySQL query SELECT `item` FROM `products` WHERE `item` LIKE '%INPUT%' LIMIT 50; the limit of 50 is just to make the query run that little bit faster. Depending on how many items you have in your table you could make this limit higher (table with fewer items) or lower.