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
DOWNLOAD & NOTES
Here is the download link to the example code, so you don’t have to copy-paste everything.
EXAMPLE CODE DOWNLOAD
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.
SORRY FOR THE ADS...
But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.
Buy Me A Coffee Code Boxx eBooks
PHP MYSQL AUTOCOMPLETE
All right, let us now get into the autocomplete example with PHP and MySQL.
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="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>
- Load the autocomplete Javascript and CSS. If you want the details, check out my other tutorial on the HTML Javascript autocomplete.
- The usual HTML input field.
- Lastly, call
ac.attach()
on page load to attach the autocomplete – Just specify thetarget
input field and yourdata
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.
- Connect to the database. Just make sure that the PDO extension is enabled in your
php.ini
and change the settings to your own. - The Javascript will send
$_POST["search"]
over. All we need is to search the database and output the results in a flat array –SELECT * FROM `table` WHERE `field` LIKE "%SEARCH%"
.
PART 3) AUTOCOMPLETE MULTIPLE FIELDS
3A) HTML & JAVASCRIPT
<!-- (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.
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 “display 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, theemail
column will populate thedEmail
field, and thephone
column will fill updTel
.
PART 4) OVERRIDE SELECTED VALUE
4A) HTML & JAVASCRIPT
<!-- (A) AUTOCOMPLETE JS + CSS -->
<script src="autocomplete.js"></script>
<link rel="stylesheet" href="autocomplete.css">
<!-- (B) INPUT FIELD -->
<input type="text" id="dUser">
<script>
// (C) ATTACH AUTOCOMPLETE TO INPUT FIELD
ac.attach({
target: document.getElementById("dUser"),
data: "4b-search.php"
});
</script>
Let’s say we have 2 users with the same name. To distinguish between them, we have to add the email to the suggestion list – But when the option is selected, we populate the field with the email only. There’s no difference in the HTML/Javascript once again, we only have to change the PHP output.
4B) 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"]} ({$r["email"]})",
"V" => $r["email"]
]; }
echo count($data)==0 ? "null" : json_encode($data) ;
As you can see:
D
The “display value” is now “NAME (EMAIL)”.V
But when the user selects the option, we populate the target field with the email only.
P.S. If you want to fill up multiple fields, you can output more FIELD_ID => VALUE
as with the previous example.
EXTRAS
That’s all for this guide, and here is a small section on some extras and links that may be useful to you.
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 – These are only simple examples. 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
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!
Hi again, still don’t work with Chinese and cyrillic.
https://codepen.io/code-boxx/pen/MWEQZyo – Works on Chrome, FF, Edge, Opera.
Flush your browser cache, do a force reload.
Thanks once more for the answer, I will try one more time (nevertheless i tried already dozens of times :)).
Yes, “Pure HTML Javascript Autocomplete” works perfect, but this is another example.
When you have to load from database Chinese or cirillyc names – not working (“Simple Autocomplete With PHP MySQL”). With English it works.
I can send you somewhere private link of my tryouts for proof, if you want.
Best regards! And sorry for bothering! 🙂
Redownload the updated version and double-check – There are Chinese entries in the dummy database, and it works. I cannot provide further consultation otherwise – Good luck!
I’ve already remaid it. Still problem.
When I print the $data array extracted from mysql – it says:
[{“D”:”Jazz Doe”,”dEmail”:”jazz@doe.com”,”dTel”:”56973317″},{“D”:”Jane Doe”,”dEmail”:”jane@doe.com”,”dTel”:”40378541″},{“D”:”Rusty Terry”,”dEmail”:”rusty@terry.com”,”dTel”:”34614899″},{“D”:”Peers Sera”,”dEmail”:”peers@sera.com”,”dTel”:”13014383″},{“D”:”Jaslyn Keely”,”dEmail”:”jaslyn@keely.com”,”dTel”:”52154191″},{“D”:”Richard Breann”,”dEmail”:”richard@breann.com”,”dTel”:”58765281″},{“D”:”????”,”dEmail”:”zhuge@thad.com”,”dTel”:”11753471″},{“D”:”Tillie Sharalyn”,”dEmail”:”tillie@sharalyn.com”,”dTel”:”33989432″},{“D”:”???”,”dEmail”:”akow@adelaide.com”,”dTel”:”56539890″}…
So Chinese is in ??? that’s why it’s doesn’t work. Same with other non-english.
When I find solution I will write…
Great tutorial!
Doesn’t work with names with cyrillic characters inserted into table users.
Any idea why?
Try
$dbchar = "utf8mb4"
and add<meta charset="utf-8">
into<head>
.Tried utf8mb4 and utf8 and meta… But still don’t work…
I have a database with URL … when I search a name how do I open the URL?
I am guessing you want to open a new page when choosing from the autocomplete.
See
select : (val, multi)
in example 3 of https://code-boxx.com/simple-autocomplete-html-javascript/A demo please. Could not validate the jquery script.
https://code-boxx.com/faq/#nodemo
I dont understand how to change so how multiple Fields are filled in one search, can you please explain ?
Examples updated – Not that easy to do, but possible.