Simple Autocomplete With PHP MySQL (Free Download)

Welcome to a quick tutorial on how to create an autocomplete textbox with PHP and MySQL. Looking to add autocomplete features to a textbox? But it doesn’t make any sense to load an entire library and inflate the loading times. Well, here is a sharing of my autocomplete using only pure HTML and Javascript – It’s both simple and lightweight. Read on for the examples!

 

 

TABLE OF CONTENTS

 

 

PART 1) DUMMY DATABASE

CREATE TABLE `users` (
  `id` bigint(20) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(24) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

ALTER TABLE `users`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT;

This is the dummy database that we are working with, should be self-explanatory.

• id User ID, primary key.
• name User’s full name.
• email The email.
• phone Telephone number.

 

 

PART 2) AUTOCOMPLETE SINGLE FIELD

2A) HTML & JAVASCRIPT

<!-- (A) AUTOCOMPLETE JS + CSS -->
<script src="4a-autocomplete.js"></script>
<link rel="stylesheet" href="4b-autocomplete.css">
 
<!-- (B) INPUT FIELD -->
<input type="text" id="dName">
 
<script>
// (C) ATTACH AUTOCOMPLETE TO INPUT FIELD
ac.attach({
  target: document.getElementById("dName"),
  data: "2b-search.php"
});
</script>

1. Load the autocomplete Javascript and CSS. If you want the details, check out my other tutorial on the HTML Javascript autocomplete.
2. The usual HTML input field.
3. Lastly, call ac.attach() on page load to attach the autocomplete – Just specify the target input field and your data URL.

 

 

2B) PHP SEARCH

<?php
// (A) CONNECT TO DATABASE - CHANGE TO YOUR OWN!
$dbhost = "localhost";
$dbname = "test";
$dbchar = "utf8mb4";
$dbuser = "root";
$dbpass = "";
$pdo = new PDO(
  "mysql:host=$dbhost;dbname=$dbname;charset=$dbchar",
  $dbuser, $dbpass, [
  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
  PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
]);
 
// (B) DO SEARCH
$data = [];
$stmt = $pdo->prepare("SELECT `name` FROM `users` WHERE `name` LIKE ?");
$stmt->execute(["%".$_POST["search"]."%"]);
while ($r = $stmt->fetch()) { $data[] = $r["name"]; }
echo count($data)==0 ? "null" : json_encode($data) ;

Beginners, keep calm and look carefully. This script is actually super simple.

1. Connect to the database. Just make sure that the PDO extension is enabled in your php.ini and change the settings to your own.
2. The Javascript will post a $_POST["search"] over. All we need is to do a database search –  SELECT * FROM `table` WHERE `field` LIKE "%SEARCH%".

 

 

PART 3) AUTOCOMPLETE MULTIPLE FIELDS

3A) HTML & JAVASCRIPT

<!-- (A) AUTOCOMPLETE JS + CSS -->
<script src="4a-autocomplete.js"></script>
<link rel="stylesheet" href="4b-autocomplete.css">
 
<!-- (B) AUTOCOMPLETE MULTIPLE FIELDS -->
<form id="myForm">
  <label for="dName">Name</label>
  <input type="text" id="dName">
  <label for="dEmail">Email</label>
  <input type="email" id="dEmail">
  <label for="dTel">Tel</label>
  <input type="text" id="dTel">
</form>
 
<script>
// (C) ATTACH AUTOCOMPLETE TO INPUT FIELD
ac.attach({
  target: document.getElementById("dName"),
  data: "3b-search.php"
});
</script>

So how about choosing a single suggestion to fill up multiple fields? It’s pretty much the same with the HTML and Javascript – Just define your HTML fields, and attach the autocomplete on page load.

 

3B) PHP SEARCH

<?php
// (A) CONNECT TO DATABASE - CHANGE TO YOUR OWN!
$dbhost = "localhost";
$dbname = "test";
$dbchar = "utf8mb4";
$dbuser = "root";
$dbpass = "";
$pdo = new PDO(
  "mysql:host=$dbhost;dbname=$dbname;charset=$dbchar",
  $dbuser, $dbpass, [
  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
  PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
]);
 
// (B) DO SEARCH
$data = [];
$stmt = $pdo->prepare("SELECT * FROM `users` WHERE `name` LIKE ?");
$stmt->execute(["%".$_POST["search"]."%"]);
while ($r = $stmt->fetch()) { $data[] = [
  "D" => $r["name"], "dEmail" => $r["email"], "dTel" => $r["phone"]
]; }
echo count($data)==0 ? "null" : json_encode($data) ;

Yep, it’s the same old connect to the database, then do a SELECT search. The only difference here is the response:

• D is the value for the “main autocomplete field”. In this example, it is the user’s name.
• Then, followed by as many FIELD-ID => VALUE as required. In this example, the email column will populate the dEmail field, and the phone column will fill up dTel.

 

 

 

SUPPORT

600+ free tutorials & projects on Code Boxx and still growing. I insist on not turning Code Boxx into a "paid scripts and courses" business, so every little bit of support helps.

Buy Me A Meal Code Boxx eBooks

 

EXAMPLE CODE DOWNLOAD

Click here for the source code on GitHub gist, just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

 

INDEX FOR SEARCH PERFORMANCE

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

Notice how we set the email and name columns in the dummy table as keys? This is one small suggestion for you guys who are new – Index and set the searchable fields as keys. Just what does “indexing” do? Imagine a library without a catalog system… Searching for a book will involve looking through every shelf and book in the library.

It works the same way in a database. If we don’t have indices, the database will have to look through each and every entry to find the ones that we want. While indexing does take up a little more disk space, it will also greatly speed up and improve search performance.

 

SEARCH SECURITY

<?php
session_start();
if (!isset($_SESION["user"]) { exit(); }

Before the toxic troll things start to spit acid – The above example is only a simple example. In production systems, one would have added more security. For example, users need to log in for the autocomplete script to respond.

 

 

SEARCH MULTIPLE FIELDS & TABLES

Searching from multiple fields:

SELECT * 
FROM `users` 
WHERE `name` LIKE '%SEARCH%' 
OR `email` LIKE '%SEARCH'

Searching from multiple tables:

SELECT *
FROM `TABLE-A`
LEFT JOIN `TABLE-B`
ON `TABLE-A`.FIELD = TABLE-B.FIELD
WHERE `TABLE-A`.FIELD LIKE '%SEARCH%'
OR `TABLE-B`.FIELD LIKE '%SEARCH%'

Most importantly, master the use of SELECT and JOIN by yourself – Links below.

 

NON-ENGLISH LANGUAGES

Just a small note and reminder – If you are dealing with non-English languages, remember to add <meta charset="utf-8"> in the HTML <head>.

 

LINKS & REFERENCES

• Pure HTML Javascript Autocomplete – Code Boxx
• MYSQL SELECT – TutorialsPoint
• MYSQL JOIN – TutorialsPoint
• Beginner’s JSON – Code Boxx