Autocomplete Textbox with Javascript, PHP and MySQL

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', 'jazz@doe.com', '56973317'),
(2, 'Jane Doe', 'jane@doe.com', '40378541'),
(3, 'Rusty Terry', 'rusty@terry.com', '34614899'),
(4, 'Peers Sera', 'peers@sera.com', '13014383'),
(5, 'Jaslyn Keely', 'jaslyn@keely.com', '52154191'),
(6, 'Richard Breann', 'richard@breann.com', '58765281'),
(7, 'Imogene Thad', 'imogene@thad.com', '11753471'),
(8, 'Tillie Sharalyn', 'tillie@sharalyn.com', '33989432'),
(9, 'Drake Adelaide', 'drake@adelaide.com', '56539890'),
(10, 'Coby Kelleigh', 'coby@kelleigh.com', '83788228'),
(11, 'Frank Doe', 'frank@doe.com', '87074336'),
(12, 'John Doe', 'john@doe.com', '55163490'),
(13, 'Jan Doe', 'jan@doe.com', '45310572'),
(14, 'Joe Doe', 'joe@doe.com', '48938630'),
(15, 'Joanne Doe', 'joanne@doe.com', '44101677'),
(16, 'Johan Doe', 'johan@doe.com', '28278966'),
(17, 'Charles Doe', 'charles@doe.com', '45800899'),
(18, 'Charlie Doe', 'charlie@doe.com', '32644564'),
(19, 'Apple Doe', 'apple@doe.com', '76438908'),
(20, 'Orange Doe', 'orange@doe.com', '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.

Server side search

<?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