Autocomplete Textbox with Javascript, PHP and MySQL

Autocomplete Textbox with Javascript, PHP and MySQL

In this guide, we will walk through how to add autocomplete to a textbox, with the help of jQuery and also an alternative that works with just vanilla Javascript.

Welcome to a step-by-step tutorial on how to create an autocomplete textbox with PHP, Javascript, and MySQL. In this guide, we will walk through how to add autocomplete to a textbox, with the help of jQuery and also an alternative that works with just vanilla Javascript. Read on to find out!

Quick notes

  • Download and unzip the file into a folder.
  • Create a dummy database and import 1-users.sql.
  • Change the database settings in 2-search.php to your own.
  • Access 3a-jquery.html in your browser for the jQuery version.
  • Access 3b-vanilla.html in your browser for the vanilla Javascript version.

1. Step 1: The Database

For the purpose of demonstration in this tutorial, I have included a simple user table and some sample data. You can skip this part if you already have an established database and want to work on that instead.

The User table and Sample data

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(24) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `users` (`id`, `name`, `email`, `phone`) VALUES
(1, 'Jazz Doe', '[email protected]', '56973317'),
(2, 'Jane Doe', '[email protected]', '40378541'),
(3, 'Rusty Terry', '[email protected]', '34614899'),
(4, 'Peers Sera', '[email protected]', '13014383'),
(5, 'Jaslyn Keely', '[email protected]', '52154191'),
(6, 'Richard Breann', '[email protected]', '58765281'),
(7, 'Imogene Thad', '[email protected]', '11753471'),
(8, 'Tillie Sharalyn', '[email protected]', '33989432'),
(9, 'Drake Adelaide', '[email protected]', '56539890'),
(10, 'Coby Kelleigh', '[email protected]', '83788228'),
(11, 'Frank Doe', '[email protected]', '87074336'),
(12, 'John Doe', '[email protected]', '55163490'),
(13, 'Jan Doe', '[email protected]', '45310572'),
(14, 'Joe Doe', '[email protected]', '48938630'),
(15, 'Joanne Doe', '[email protected]', '44101677'),
(16, 'Johan Doe', '[email protected]', '28278966'),
(17, 'Charles Doe', '[email protected]', '45800899'),
(18, 'Charlie Doe', '[email protected]', '32644564'),
(19, 'Apple Doe', '[email protected]', '76438908'),
(20, 'Orange Doe', '[email protected]', '47648224');

This is nothing but a very simple users table with 4 fields – id is the primary key, name, email, and phone.

Index your Searchable Fields

ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `email` (`email`),
  ADD KEY `name` (`name`);

ALTER TABLE `users`
MODIFY `id` int(21) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=21;

One small suggestion for you guys who already have an existing project – Index the fields that you want to search. Just why do we need to index the fields? Imagine a library that does not have a catalog system… Searching for a book that you want will mean having to look through each and every book in the library until you find it.

It is the same here in a database. If you don’t index the field, the database will have to look through each and every entry to find the ones that you want. Yes, while indexing does take up a little more disk space, but it will also speed up your searches and consume fewer system resources in the long run. Especially if you have a massive database with thousands of entries.

2. Step 2: Server - side script

Now that we have established the dummy database, it is time to work on a server-side script that will deal with searching the database and output the results.

<?php
// YOU MIGHT WANT TO ADD SOME SECURITY ON THIS PAGE
// E.G. CHECK IF VALID USER LOGIN
// if (isset($_SESSION['admin'])) { ... DO SEARCH AS BELOW ... }

// (1) CONNECT TO DATABASE
$host = '127.0.0.1';
$dbname = 'test';
$user = 'root';
$password = '';
$charset = 'utf8';
$pdo = new PDO(
    "mysql:host=$host;dbname=$dbname;charset=$charset", $user, $password, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
    ]
);

// (2) SEARCHING FOR?
$data = [];
switch ($_POST['type']) {
  // (2A) INVALID SEARCH TYPE
  default :
    break;

  // (2B) SEARCH FOR USER
  case "user":
    // You might want to limit number of results on massive databases
    // SELECT * FROM XYZ WHERE `FIELD` LIKE ? LIMIT 20
    $stmt = $pdo->prepare("SELECT * FROM `users` WHERE `name` LIKE ?");
    $stmt->execute(["%" . $_POST['term'] . "%"]);
    while ($row = $stmt->fetch(PDO::FETCH_NAMED)) {
      $data[] = $row['name'];
    }
    break;

  // (2C) SEARCH FOR EMAIL
  case "email":
    $stmt = $pdo->prepare("SELECT * FROM `users` WHERE `email` LIKE ?");
    $stmt->execute(["%" . $_POST['term'] . "%"]);
    while ($row = $stmt->fetch(PDO::FETCH_NAMED)) {
      $data[] = $row['email'];
    }
    break;
}

// (3) RETURN RESULT
$pdo = null;
echo json_encode($data);
?>
  • There are 2 things to POST to this script.
    • First, which type of data you want – user or email?
    • A search term should also be sent to this script.
  • Very straightforward. The script first opens up a database connection – Remember to change the database settings to your own.
  • Then runs a search query on the user table, depending on what you want to look for.
  • Finally, outputs the search results (if any) using JSON encoding.

The jQuery autocomplete plugin uses JSON, but if you want to work with XML (or some other format) in your own project, please feel free to modify the code to fit your own needs.

3. Step 3: Jquery Autocomplete

The final piece of the puzzle is the client-side HTML and Javascript, which I will give 2 alternatives for. This first alternative includes the use of jQuery UI, and it is as simple as attaching the autocomplete to an element.

The Script:

<!DOCTYPE html>
<html>
  <head>
    <title>
      jQuery Autocomplete Demo
    </title>
    <!-- (1) YOU NEED TO LOAD BOTH JQUERY AND JQUERY UI -->
    <link rel="stylesheet" href="https://code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
    <script src="https://code.jquery.com/jquery-3.4.1.min.js"></script>
    <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.min.js"></script>ry-ui.min.js"></script>

    <!-- (2) ATTACH AUTOCOMPLETE ON PAGE LOAD -->
    <script>
      $(function () {
        // NAME AUTO-COMPLETE
        $('#user-name').autocomplete({
          source: function (request, response) {
            $.ajax({
              type: "POST",
              url: "2-search.php",
              data: {
                term: request.term,
                type: "user"
              },
              success: response,
              dataType: 'json',
              minLength: 2,
              delay: 100
            });
          }
        });

        // EMAIL AUTO-COMPLETE
        $('#user-email').autocomplete({
          source: function (request, response) {
            $.ajax({
              type: "POST",
              url: "2-search.php",
              data: {
                term: request.term,
                type: "email"
              },
              success: response,
              dataType: 'json',
              minLength: 2,
              delay: 100
            });
          }
        });
      });
    </script>
  </head>
  <body>
    <!-- (3) DUMMY FORM -->
    Name: <input type="text" id="user-name"/>
    <br/><br/>
    Email: <input type="email" id="user-email"/>
  </body>
</html>

The Explannation

Not much rocket science is required when it comes to jQuery:

  • You need to load both the main jQuery library and jQuery UI.
  • Just attach autocomplete to the input fields.

Please do also check out the jQuery autocomplete documentation on the settings that you can use.

4. Step 4: Vanila JavaScript

For you guys who do not wish to use jQuery, or think that it adds too much to the loading bloat, here is the other alternative written in pure Javascript.

The HTML:

<!DOCTYPE html>
<html>
  <head>
    <title>
      Vanilla Javascript Autocomplete Demo
    </title>
    <!-- (1) AUTOCOMPLETE SCRIPTS -->
    <link href="3b-autocomplete.css" rel="stylesheet">
    <script src="3b-autocomplete.js"></script>

    <!-- (2) ATTACH AUTOCOMPLETE ON PAGE LOAD -->
    <script>
    window.addEventListener("load", function(){
      // NAME AUTO-COMPLETE
      suggest.attach({
        // Minimum required
        target : "user-name",
        url : "2-search.php",
        data : { type: "user" }
      });

      // EMAIL AUTO-COMPLETE
      suggest.attach({
        // Minimum required
        target : "user-email",
        url : "2-search.php",
        data : { type: "email" },
        // Optional
        delay : 200,
        min : 3
      });
    });
    </script>
  </head>
  <body>
    <!-- (3) DUMMY FORM -->
    Name: <input type="text" id="user-name"/>
    <br/><br/>
    Email: <input type="email" id="user-email"/>
  </body>
</html>
  • Text fields in HTML as usual.
  • We wait for the page to be fully loaded window.addEventListener("load") before attaching the autocomplete to the fields.
  • Use suggest.attach(OPTIONS) to attach the auto-suggest to the required fields.

The CSS

div.acWrap {
  position: relative;
}
div.acBox {
  position: absolute;
  top: 25px;
  left: 0;
  padding: 5px;
  z-index: 999;
  background: #fff;
  border: 1px solid #000;
  display: none;
}
div.acBox div:hover {
  cursor: pointer;
  background: #fea;
}

Now, the autocomplete script will “pad” the input field with some HTML `` wrappers, and it will look like this when fully initiated:

<div id="acWrapXXX" class="acWrap">
  <input type="text" id="FOO-BAR"/>
  <div id="acBoxXXX" class="acBox">
    <!-- SUGGESTIONS ARE PUT HERE DYNAMICALLY VIA AJAX -->
    <div>Option A</div>
    <div>Option B</div>
    <div>Option C</div>
  </div>
</div>

So feel free to change the CSS styles to suit your own project.

The JavaScript:

var suggest = {
  instance : {}, // attached instances
  focus : null, // current text field in focus

  attach : function (opt) {
  // suggest.attach () : attach autosuggest to input field
  // opt : options
  //  - target : ID of target input field, required
  //  - url : Target URL to fetch suggestions, required
  //  - delay : Delay before autocomplete fires up, optional, default 500ms
  //  - min : Minimum characters to fire up autocomplete, default 2
  //  - data : Additional data to post to the server
  //  - fetch : Manual fetch draw override
  //  - select : Manual select draw override

    // Create autocomplete wrapper and box
    var id = Object.keys(suggest.instance).length,
        input = document.getElementById(opt.target);
    input.outerHTML = "<div id='acWrap" + id + "' class='acWrap'>" + input.outerHTML + "<div id='acBox" + id + "' class='acBox'></div></div>";

    // Set the HTML references and options
    suggest.instance[opt.target] = {
      input : document.getElementById(opt.target),
      wrap : document.getElementById("acWrap" + id),
      box : document.getElementById("acBox" + id),
      delay : opt.delay ? opt.delay : 500,
      url : opt.url,
      min : opt.min ? opt.min : 2,
      data : opt.data ? opt.data : null,
      fetch : opt.fetch ? opt.fetch : null,
      select : opt.select ? opt.select : null,
      timer : null
    };

    // Attach key listener
    suggest.instance[opt.target].input.addEventListener("keyup", function (evt) {
      // Clear old timer
      if (suggest.instance[opt.target].timer != null) {
        window.clearTimeout(suggest.instance[opt.target].timer);
      }

      // Hide and clear old suggestion box
      suggest.instance[opt.target].box.innerHTML = "";
      suggest.instance[opt.target].box.style.display = "none";

      // Create new timer, only if minimum characters
      if (evt.target.value.length >= suggest.instance[opt.target].min) {
        suggest.instance[opt.target].timer = setTimeout(
          function () { suggest.fetch(evt.target.id); },
          suggest.instance[opt.target].delay
        );
      }
    });

    // This is used to hide the suggestion box if the user navigates away
    suggest.instance[opt.target].input.addEventListener("focus", function (evt) {
      if (suggest.focus != null) { suggest.close(null, true); }
      suggest.focus = opt.target;
    });
  },

  fetch : function (id) {
  // suggest.fetch() : AJAX get suggestions and draw
  // id : ID of target input field, automatically passed in by keyup event

    // Init AJAX
    var req = new XMLHttpRequest();
    req.addEventListener("load", function () {
      var data = JSON.parse(this.response);
      if (Object.keys(data).length > 0) {
        if (suggest.instance[id]['fetch']) {
          suggest.instance[id]['fetch'](id, data);
        } else {
          data.forEach(function (el) {
            suggest.instance[id].box.insertAdjacentHTML("beforeend", "<div onclick=\"suggest.select('" + id + "', this);\">" + el + "</div>");
          });
        }
        suggest.instance[id].box.style.display = "block";
        document.addEventListener("click", suggest.close);
      }
    });

    // Data
    var data = new FormData();
    data.append('term', suggest.instance[id].input.value);
    if (suggest.instance[id].data) {
      for (let key in suggest.instance[id].data) {
        data.append(key, suggest.instance[id].data[key]);
      }
    }

    // Send
    req.open("POST", suggest.instance[id].url);
    req.send(data);
  },

  select : function (id, el, more) {
  // suggest.select() : user selects a value from autocomplete
  // id : ID of text box
  // el : Selected element
  // more : Optional, more parameters

    if (suggest.instance[id]['select']) {
      suggest.instance[id]['select'](id, el, more);
    } else {
      suggest.instance[id].input.value = el.innerHTML;
      suggest.instance[id].box.innerHTML = "";
      suggest.instance[id].box.style.display = "none";
      document.removeEventListener("click", suggest.close);
    }
  },

  close : function (evt, force) {
  // suggest.close() : close the autocomplete box if the user clicks away from the input field
  // evt : click event
  // force : force close

    if (force || event.target.closest(".acWrap") == null) {
      suggest.instance[suggest.focus].box.innerHTML = "";
      suggest.instance[suggest.focus].box.style.display = "none";
      document.removeEventListener("click", suggest.close);
    }
  }
};

The Explannation:

This may look like a handful at first, but it is a stepped-down version of what jQuery has. Rather than repeating line-by-line (trace through the code on your own if you want)

5. Step 5: Jquery Complete Multiple

The autocomplete is done, and here is an extra section for you guys who want to populate many fields with one selection. For example, select the user name, and automatically fill up the email plus phone number.

Sever-Side Addition

Firstly, we are going to add an extra handler in the server-side search script:

<?php
switch ($_POST['type']) {
  // (X) SEARCH FOR USER WITH ALL INFORMATION
  // JQUERY VERSION
  case "user-all":
    // Data yoga
    $data = [
      "display" => [],
      "details" => []
    ];

    $stmt = $pdo->prepare("SELECT * FROM `users` WHERE `name` LIKE ?");
    $stmt->execute(["%" . $_POST['term'] . "%"]);
    while ($row = $stmt->fetch(PDO::FETCH_NAMED)) {
      $data["display"][] = [
        "label" => $row['name'],
        "value" => $row['id']
      ];
      $data["details"][$row['id']] = [
        "email" => $row['email'],
        "phone" => $row['phone']
      ];
    }
    break;
}

Now, this is pretty much the same “search the database by user” as above, but we are returning a lot more data this time around. Take note of how the $data response is formed:

  • $data["display"] will hold a list of the user id and name.
  • $data["details"] will hold a list of the user id, email and phone.
  • Yep, I would have just packed everything flat inside $data, but jQuery works otherwise.
  • In jQuery autocomplete, it takes an array of label and value to generate the autocomplete list – This is why we have $data["display"], with the user name as the label, and id as the value.
  • Upon selection, as you can guess, we will then use the selected value (id) to tie to $data["details"] – Use it to populate the rest of the fields. 

The HTML

<!DOCTYPE html>
<html>
  <head>
    <title>
      jQuery Autocomplete Demo
    </title>

    <!-- (1) YOU NEED TO LOAD BOTH JQUERY AND JQUERY UI -->
    <link rel="stylesheet" href="https://code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
    <script src="https://code.jquery.com/jquery-3.4.1.min.js"></script>
    <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.min.js"></script>

    <!-- (2) ATTACH AUTOCOMPLETE ON PAGE LOAD -->
    <script>
      // GLOBAL TEMP VAR TO HOLD AUTOCOMPLETE DATA
      var actemp = null;

      // AUTO-COMPLETE SETUP
      $(function () {
        $('#user-name').autocomplete({
          source: function (request, response) {
            $.ajax({
              type: "POST",
              url: "2-search.php",
              data: {
                term: request.term,
                type: "user-all"
              },
              success: function(data) {
                // Details to global temp
                actemp = data.details;

                // Return display
                response(data.display);
              },
              dataType: 'json',
              minLength: 2,
              delay: 100
            });
          },
          select : function(event, ui){
            // Fill up other fields
            $('#user-name').val(ui.item.label);
            $('#user-email').val(actemp[ui.item.value]['email']);
            $('#user-tel').val(actemp[ui.item.value]['phone']);
            return false;
          }
        });
      });
    </script>
  </head>
  <body>
    <!-- (3) DUMMY FORM -->
    Name: <input type="text" id="user-name"/>
    <br/><br/>
    Email: <input type="email" id="user-email"/>
    <br/><br/>
    Tel: <input type="email" id="user-tel"/>
  </body>
</html>

A little bit confusing in this one, but it is as described above. We use data["display"] to generate the selection list, use the selected value to tie back to data["details"], then it is possible to auto-complete the rest of the other fields.

6. Step 6: Vanilla Complete Multiple

Finally, the “auto-complete many” vanilla Javascript version.

Sever-Side Addition

Just like the jQuery version, we are going to add another request to the server-side script. But this time, it will be a lot more straightforward for the vanilla version.

<?php
switch ($_POST['type']) {
  // (X) SEARCH FOR USER WITH ALL INFORMATION
  // VANILLA VERSION
  case "user-a":
    $stmt = $pdo->prepare("SELECT * FROM `users` WHERE `name` LIKE ?");
    $stmt->execute(["%" . $_POST['term'] . "%"]);
    while ($row = $stmt->fetch(PDO::FETCH_NAMED)) {
      $data[$row["id"]] = $row;
    }
    break;
}

Override Draw

Finally, all we have to do is to override the draw and select to pick up the additional data.

<!DOCTYPE html>
<html>
  <head>
    <title>
      Vanilla Javascript Autocomplete Demo
    </title>

    <!-- (1) AUTOCOMPLETE SCRIPTS -->
    <link href="3b-autocomplete.css" rel="stylesheet">
    <script src="3b-autocomplete.js"></script>

    <!-- (2) ATTACH AUTOCOMPLETE ON PAGE LOAD -->
    <script>
    // GLOBAL TEMP VAR TO HOLD AUTOCOMPLETE DATA
    var actemp = null;
    window.addEventListener("load", function(){

      // NAME AUTO-COMPLETE
      suggest.attach({
        target : "user-name",
        url : "2-search.php",
        data : { type: "user-a" },

        // MANUAL OVERRIDE FETCH
        fetch : function (id, data) {
          // User data to global temp
          actemp = data;

          // Suggestion draw override
          for (let uid in data) {
            suggest.instance[id].box.insertAdjacentHTML("beforeend", "<div onclick=\"suggest.select('" + id + "', this, " + uid + " );\">" + data[uid]['name'] + "</div>");
          }
        },

        // Selection draw override
        select : function (id, el, more) {
          // Name
          suggest.instance[id].input.value = el.innerHTML;
          // Email
          document.getElementById("user-email").value = actemp[more]['email'];
          // Phone
          document.getElementById("user-tel").value = actemp[more]['phone'];
          // Close suggestions
          suggest.instance[id].box.innerHTML = "";
          suggest.instance[id].box.style.display = "none";
          document.removeEventListener("click", suggest.close);
        }
      });
    });
    </script>
  </head>
  <body>
    <!-- (3) DUMMY FORM -->
    Name: <input type="text" id="user-name"/>
    <br/><br/>
    Email: <input type="email" id="user-email"/>
    <br/><br/>
    Tel: <input type="email" id="user-tel"/>
  </body>
</html>

That’s all folks. The vanilla version is simpler than jQuery for once.

What next?

Thank you for reading, and we have come to the end of this short tutorial. I hope it has given you a kick start to your own project, and if you decide to use my vanilla auto-complete, please do remember to do your own CSS styles with it… It is really nothing but a skeleton.

If there is anything that you like to add to this guide, please feel free to comment below. Good luck and happy coding!

Javascript PHP MySQL jQuery HTML

What's new in Bootstrap 5 and when Bootstrap 5 release date?

How to Build Progressive Web Apps (PWA) using Angular 9

What is new features in Javascript ES2020 ECMAScript 2020

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Random Password Generator Online

HTML Color Picker online | HEX Color Picker | RGB Color Picker

How to Create Login and Registeration Form with PHP, HTML, CSS and MySQL

In this video we will create a Login and a Registeration form using PHP, HTML, CSS and MySQL. (User Login & Register Form in PHP and MySQL)

Build a Register & Login Form using PHP, HTML, CSS, Bootstrap and MySQL

In this tutorial you will learn and build a Responsive PHP Registration & Login Form using PHP, HTML, CSS, Bootstrap and MySQL

Electron & MySQL CRUD | Aplicación de escritorio con HTML, CSS y Javascript

En este ejemplo práctico de Electronjs, aprenderemos a crear una aplicación de escritorio conectada a MYSQL como base de datos SQL. Crearemos un CRUD (CREATE, READ, UPDATE. DELETE) usando conceptos de Nodejs, y process de Electron, ademas integraremos bibiliotecas de CSS como Bootstrap y animate para poder desarrollar nuestra interfaz.

HTML JavaScript - Add Javascript File to HTML

Learn HTML and javascript, their uses & importance, html javascript function, javascript tags list, why add javascript file to html, HTML Events with JavaScript etc