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.

Next The MySQL soundex function; Selecting names »

Previous « Barkers Creek Reservoir Harcourt full drone images

Tags: How toMYSQLPHPWeb Dev

5 years ago

FTP getting array of file details such as size using PHP
Using FTP and PHP to get an array of file details such as size and…
Creating Laravel form requests
Creating and using Laravel form requests to create cleaner code, separation and reusability for your…
Improving the default Laravel login and register views
Improving the default Laravel login and register views in such a simple manner but making…