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!

ⓘ I have included a zip file with all the source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

 

 

TABLE OF CONTENTS

Download & Notes Autocomplete Useful Bits & Links
The End

 

DOWNLOAD & NOTES

Firstly, here is the download link to the example code as promised.

 

QUICK NOTES

  • Create a dummy database and import 1-users.sql.
  • Change the database settings in 2b-search.php and 3b-search.php to your own.
  • Access 2a-single.html or 3a-multiple.html in your browser – Use http:// and not file://.
If you spot a bug, feel free to comment below. I try to answer short questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

EXAMPLE CODE DOWNLOAD

Click here to download the source code in a zip file – I have released it under the MIT License, so feel free to build on top of it if you want to.

 

 

PHP MYSQL AUTOCOMPLETE

All right, let us now get into the autocomplete example with PHP and MySQL.

 

PART 1) DUMMY DATABASE

1-users.sql
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;

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

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

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

THE HTML & JAVASCRIPT

2a-single.html
<!-- (A) AUTOCOMPLETE JS + CSS -->
<script src="autocomplete.js"></script>
<link rel="stylesheet" href="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. Doh. 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.

 

PHP SEARCH

2b-search.php
<?php
// (A) CONNECT TO DATABASE - CHANGE TO YOUR OWN!
$dbhost = "localhost";
$dbname = "test";
$dbchar = "utf8";
$dbuser = "root";
$dbpass = "";
try {
  $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
  ]);
} catch (Exception $ex) { exit($ex->getMessage()); }
 
// (B) DO SEARCH
$data = [];
$stmt = $pdo->prepare("SELECT `name` FROM `users` WHERE `name` LIKE ?");
$stmt->execute(["%" . $_POST["search"] . "%"]);
while ($row = $stmt->fetch()) { $data[] = $row["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

THE HTML & JAVASCRIPT

3a-multiple.html
<!-- (A) AUTOCOMPLETE JS + CSS -->
<script src="autocomplete.js"></script>
<link rel="stylesheet" href="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.

 

PHP SEARCH

3b-search.php
<?php
// (A) CONNECT TO DATABASE - CHANGE TO YOUR OWN!
$dbhost = "localhost";
$dbname = "test";
$dbchar = "utf8";
$dbuser = "root";
$dbpass = "";
try {
  $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
  ]);
} catch (Exception $ex) { exit($ex->getMessage()); }
 
// (B) DO SEARCH
$data = [];
$stmt = $pdo->prepare("SELECT * FROM `users` WHERE `name` LIKE ?");
$stmt->execute(["%" . $_POST["search"] . "%"]);
while ($row = $stmt->fetch()) { $data[] = [
  "D" => $row["name"], "dEmail" => $row["email"], "dTel" => $row["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.

 

 

USEFUL BITS & LINKS

That’s all for this guide, and here is a small section on some extras and links that may be useful to you.

 

SEARCH PERFORMANCE – INDEX THE SEARCHABLE FIELDS

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

Notice how all the fields in the dummy table above are set as keys? This is just one small suggestion for you guys who already have an existing project – Index the fields that you want to search, set them as keys. Just why do we want to index the fields? Imagine a library without a catalog system… Searching for a book will mean having to look through every book in the library.

It is the same in a database. If we don’t index the field, 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, but it will also greatly speed up the searches and consume fewer system resources in the long run.

 

SEARCH SECURITY

2b-search.php
<?php
session_start();
if (!isset($_SESION["user"]) { exit(); }

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

 

 

HOW TO SEARCH MULTIPLE FIELDS? MULTIPLE 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.

 

LINKS & REFERENCES

 

THE END

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!

4 thoughts on “Simple Autocomplete With PHP MySQL (Free Download)”

Leave a Comment

Your email address will not be published. Required fields are marked *