PHP Live search from MySQL with AJAX

Creating a live search input form that matches the current input against a MySQL database using AJAX and PHP combined.

I needed this to suite a “product select” project whereby there was simply thousands of products and putting them in a drop-down or in categories on multiple pages wasn’t ideal.

Live search/suggest input

The solution was a live search that saves the user from fully typing out the product name.

The main part was the Javascript AJAX code:

function showHint(str) {
    if (str.length == 0) {
        document.getElementById("hint").innerHTML = "";
        return;
    } else {
        var xmlhttp = new XMLHttpRequest();
        xmlhttp.onreadystatechange = function () {
            if (this.readyState == 4 && this.status == 200) {
                document.getElementById("hint").innerHTML = this.responseText;
            }
        };
        xmlhttp.open("GET", "getlike.php?term=" + str, true);
        xmlhttp.send();
    }
}

If the string length of the search input is 0 nothing is done, however when there is letters/words in the input a request is made to getlike.php with the term parameter which is the current input.

This script is in the base search index.html which included bootstrap for basic styling.

<!DOCTYPE html>
<html lang="en">
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <title>Add product</title>
    <meta name="description"
          content="">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.4.1/css/bootstrap.min.css"/>
    <script>
        function showHint(str) {
            if (str.length == 0) {
                document.getElementById("hint").innerHTML = "";
                return;
            } else {
                var xmlhttp = new XMLHttpRequest();
                xmlhttp.onreadystatechange = function () {
                    if (this.readyState == 4 && this.status == 200) {
                        document.getElementById("hint").innerHTML = this.responseText;
                    }
                };
                xmlhttp.open("GET", "getlike.php?term=" + str, true);
                xmlhttp.send();
            }
        }
    </script>
    <style>
        p {
            margin-bottom: 0.2rem;
        }
    </style>
</head>
<body>
<div class="container">
    <div class="row">
        <div class="col-12">
            <form>
                <fieldset>
                    <p>Start typing a product name into the input below</p>
                    <div class="form-group">
                        <label for="product">Product</label>
                        <input type="text" class="form-control" id="product" aria-describedby="product" onkeyup="showHint(this.value)">
                    </div>
                    <div id="hint"></div>
                </fieldset>
            </form>
        </div>
    </div>
</div>
</body>
</html>

In the text input you will see onkeyup="showHint(this.value) This executes our AJAX script from above when a key is released.

getlike.php:

<?php
require_once('db.php');
$hint = '';//Empty
$term = $_REQUEST["term"];
$db = db_connect();
$select = $db->prepare("SELECT `id`, `name`, `size` FROM `product_basic` WHERE `name` LIKE ? LIMIT 50;");//lower limit means slightly faster
$select->execute(array(''.$term.'%'));//Beginning with term
while ($row = $select->fetch(PDO::FETCH_ASSOC)) {
    $hint = '<p><a href="addproduct.php?id='.$row['id'].'">'.$row['name'].' '.$row['size'].'</a></p>';
    echo $hint;
}

The SELECT query is limited to 50 this is dependent on the amount of rows in your database. With a massive amount of rows as soon as the first character is typed the code will fetch all results that suit the LIKE this could lag or crash out the browser.

For example if I type “a” every product that begins with a will be fetched. That’s why a limit is important.

Using LIKE 'val%' means everything beginning with “val” is selected otherwise using LIKE '%val%' will return everything that has “val” at any position. This is handy for a product search but likely risky with a big database.

Example usage: