Table of Contents
The symbol returns the reader to the top of the Table of Contents.
1. Introduction
I recently visited a website that contained a country <select> element that itself contained 239 <option> elements, all nicely alphabetized, but still 239 items. If I wanted to select Zimbabwe I would have been required to scroll down through all 239 entries. Even if had only wanted to choose my home country (the US), I would have had to scroll down to the 226th entry.
The website page contained a <select> element that appears as:
<select name="adr_country"
id="adr_country"
tabindex="19"
class="DataFormDropDownList"
placeholder="Please select"
onchange="resetValidationState();
clearTimeout(timerid);
timerid=setTimeout('__doPostBack(\'adr_country\',\'\')',
msecondsshort); "
onfocusin="expandDropDownList(this);"
onblur="collapseDropDownList(this);"
style="LEFT: 112px;
POSITION: absolute;
TOP: 259px;
HEIGHT: 19px;
WIDTH: 138px;">
<option value="">Please select</option>
<option value=""></option>
<option value="AFGHANISTAN">AFGHANISTAN</option>
<option value="Alaska">Alaska</option>
<option value="ALBANIA">ALBANIA</option>
<option value="ALGERIA">ALGERIA</option>
⋮ 231 additional <option>s
<option value="YUGOSLAVIA">YUGOSLAVIA</option>
<option value="ZAIRE">ZAIRE</option>
<option value="ZAMBIA">ZAMBIA</option>
<option value="ZIMBABWE">ZIMBABWE</option>
</select>
Ignore the absolute positioning and the invalid capitalization. The point is that there are a significant number of items from which to choose. Also, updating the list of countries is difficult in this form.
2. Requirements
There are a number of requirements that should be levied against any new method of selecting a country.
- No third party software is to be used. This effectively eliminates all forms of software that promises out-of-the-box solutions (including Microsoft, jQuery, Node.js, etc.).
- The visitor is to be presented with an easily manipulated and limited number of options from which to choose.
- The list of options must be easily revised.
- Implementation should be limited to:
A simple technology, called autocomplete, appears to meet these requirements.
3. Overview
Autocomplete, or word completion, is a feature in which an application predicts the rest of a word a user is typing....Autocomplete speeds up human-computer interactions when it correctly predicts the word a user intends to enter after only a few characters have been typed into a text input field. It works best in domains with a limited number of possible words....
From Wikipedia, the free encyclopedia
4. Architectural Decisions
There appear to be two approaches to create autocomplete functionality:
There appear to be a number of methods by which the <option> elements subordinate to the <select> or <datalist> elements can be populated:
- A static list of <option>s that appears as child elements beneath a <select> element.
- A static list of <option>s that appears as child elements beneath a <datalist> element.
- A static list of text that appears as elements in an array or as members of an object. A method must be defined that causes the text to become <option>s beneath the <select> or <datalist> element.
- A dynamic list of <option>s that is maintained in a database table. A method must be defined to retrieve and format the data into <option>s beneath the <select> or <datalist> element.
Unfortunately, options 1 and 2 do not provide autocomplete functionality and must be dismissed from further consideration.
The difficulty with option 3 is simply that its results are the same as the original (it produces a large amount of data). Also, methods must be created that mimic autocompletion. See How TO - Autocomplete [^] for such a method.
Option 4 relies on the pattern matching ability of MySQL. Although retrieving and formatting methods must be developed, major computations are performed by MySQL. These computations are highly optimized.
So the architecture for this implementation of autocompletion is option 4.
5. Implementation
With the introduction of the HTML Data List element [^], developing an implementation of autocompletion is relatively straight-forward. Such an implementation could take the form:
<input id="country"
type="text"
list="country_list"
autocomplete="off"
placeholder="Enter Country" />
<datalist id="country_list"></datalist>
Now the question is how do we supply the options to the datalist?
For this project, it was decided to build the <option> elements from the values returned by a MySQL stored procedure. This places all of the "heavy lifting" on MySQL.
5.1. MySQL Database
My database coding guidelines specify that the suffix "_CT" be appended to code table names. Code tables contain two fields: one is a value (usually too long to be placed in a database) and the other contains a code for the first field (usually a one or two character code that is associated with the value). An extreme example is
country code
South Georgia and the South Sandwich Islands GS
5.1.1. Tables
The database for this project contains two tables of interest. There are two because not only was country to be collected but also state was to be collected. The two autocompletion fields on the same HTML page would also be used to test whether two fields on the same HTML page could be serviced by the implementation.
The tables are:
Countries_CT
country
code
States_CT
state
code
For Countries_CT there are a VARCHAR(64) field, containing the full country name, and a VARCHAR(2) field, containing the international two-character country code. For States_CT there are a VARCHAR(32) field, containing the full US state name, and a VARCHAR(2) field, containing the USPS two-character state code.
Sources for these tables (as Comma-Separated Values files) are included in the project download as countries.csv and states.csv.
5.1.2. Stored Procedures
MySQL provides a form of pattern matching in the LIKE [^] clause of the SELECT statement (the other form is RLIKE [^]). In the LIKE form, the character '%' matches an arbitrary number of characters (including zero characters). In MySQL, SQL patterns are case-insensitive by default.
There are two stored procedures of interest:
get_partial_countries
PROCEDURE `get_partial_countries`(IN `partial` VARCHAR(64))
SELECT country
FROM `Countries_CT`
WHERE country LIKE CONCAT(partial,'%')
ORDER BY country ASC
and
get_partial_states
PROCEDURE `get_partial_states`(IN `partial` VARCHAR(32))
SELECT state
FROM `States_CT`
WHERE state LIKE CONCAT(partial,'%');
ORDER BY state ASC
Both are very similar with the exception of the table accessed and the field retrieved.
Sources for these stored procedures are included in the project download as get_partial_countries.sql and get_partial_states.sql.
5.1.3. Examples
These examples deal with the extraction of one or more country names from the Countries_CT code table. If the user types in "m", the stored procedure
SELECT country
FROM `Countries_CT`
WHERE country LIKE CONCAT("m",'%')
ORDER BY country ASC;
would return all entries that began with 'm' followed by any number of characters (recall patterns are case-insensitive):
Macao
Macedonia, the former Yugoslav Republic of
Madagascar
Malawi
Malaysia
Maldives
Mali
Malta
Marshall Islands
Martinique
Mauritania
Mauritius
Mayotte
Mexico
Micronesia, Federated States of
Moldova, Republic of
Monaco
Mongolia
Montenegro
Montserrat
Morocco
Mozambique
Myanmar
If the user adds the letter 'o' and types in "mo", the stored procedure
SELECT country
FROM `Countries_CT`
WHERE country LIKE CONCAT("mo",'%')
ORDER BY country ASC;
would return all entries that began with 'mo' followed by any number of characters:
Moldova, Republic of
Monaco
Mongolia
Montenegro
Montserrat
Morocco
Mozambique
And finally, if the user adds the letter 'r' and types in "mor", the stored procedure
SELECT country
FROM `Countries_CT`
WHERE country LIKE CONCAT("mor",'%')
ORDER BY country ASC;
would return all entries that began with 'mor' followed by any number of characters:
Morocco
During this process (m⇒mo⇒mor), at any time that the user saw the desired entry, the user need only highlight the entry and press Enter. So in the first example, seeing Morocco, the user could highlight the entry and press Enter.
5.2. PHP Interfaces
MySQL stored procedures cannot be executed directly from JavaScript. As a result, PHP is recruited to perform the intermediary function of accessing the MySQL stored procedures.
There are two stored procedures, so there are two PHP modules.
partial_countries.php
<?php
ini_set ( "display_errors", 1 );
error_reporting ( E_ALL );
include 'config.php';
$partial = strval ( htmlspecialchars ( $_GET [ 'partial' ] ) );
$link = mysqli_connect ( $servername,
$username,
$password,
$database );
if ( mysqli_connect_errno ( ) )
{
printf ( "Connect failed: %s\r\n", mysqli_connect_error ( ) );
exit(1);
}
$sql = "CALL get_partial_countries ('".$partial."')";
$result = mysqli_query ( $link, $sql );
if ( !$result )
{
mysqli_close ( $link );
printf ( "Query failed: %s\n", mysqli_error ( $link ) );
exit(1);
}
while ( $post = mysqli_fetch_assoc ( $result ) )
{
echo ",".$post [ 'country' ];
}
mysqli_free_result ( $result );
mysqli_close ( $link );
?>
and
partial_states.php
<?php
ini_set ( "display_errors", 1 );
error_reporting ( E_ALL );
include 'config.php';
$partial = strval ( htmlspecialchars ( $_GET [ 'partial' ] ) );
$link = mysqli_connect ( $servername,
$username,
$password,
$database );
if ( mysqli_connect_errno ( ) )
{
printf ( "Connect failed: %s\r\n", mysqli_connect_error ( ) );
exit(1);
}
$sql = "CALL get_partial_states ('".$partial."')";
$result = mysqli_query ( $link, $sql );
if ( !$result )
{
mysqli_close ( $link );
printf ( "Query failed: %s\n", mysqli_error ( $link ) );
exit(1);
}
while ( $post = mysqli_fetch_assoc ( $result ) )
{
echo ",".$post['state'];
}
mysqli_free_result ( $result );
mysqli_close ( $link );
?>
Again, both are very similar with the exception of the stored procedure that is accessed and the field retrieved.
config.php contains the declarations for servername, username, password, and database. Its contents take the form:
<?php
$servername = host name or an IP address;
$username = MySQL user name;
$password = user's password;
$database = default database to be used when performing queries;
?>
These values are used when opening a connection to a MySQL Server.
In each module,
- Error reporting is defined
- Connection variables are retrieved
- User input is retrieved from the query string
- The connection to the database is acquired
- The MySQL statement is fashioned and submitted
- The returned value is converted to an associative array
- The values in the array are concatenated into a string with comma separation
- No longer needed memory is freed
5.3. JavaScript
The software that binds its invoker with the PHP interfaces is a JavaScript module:
var AutoComplete;
if ( !AutoComplete )
{
AutoComplete = { };
}
else if ( typeof AutoComplete !== "object" )
{
throw new Error (
"AutoComplete already exists but is not an object" );
}
The module declaration is followed by an anonymous function that contains three JavaScript methods. The first is the initializer for the second; the second is an event handler. (The $ helper function is included for completeness.)
function $ ( id )
{
return ( document.getElementById ( id ) );
}
function initialize ( input_id,
PHP_filename )
{
var input_element = $ ( input_id );
input_element.addEventListener (
"keyup",
function ( event )
{
keyup_handler ( event, PHP_filename );
}
);
}
For the earlier example:
<input id="country"
type="text"
list="country_list"
autocomplete="off"
placeholder="Enter Country" />
<datalist id="country_list"></datalist>
to initialize an autocomplete keyup handler for the <input> element country using the PHP file partial_countries.php, the following code would be used:
<script src="auto_complete.js"></script>
<script>
AutoComplete.initialize ( "country",
"partial_countries.php" );
</script>
The actual autocompletion work is performed by the keyup event handler. Note every time that a character is entered, the keyup event handler triggers.
function keyup_handler ( event,
PHP_filename )
{
var input = event.target;
if ( !isNaN ( input.value ) ||
( input.value.length < MINIMUM_CHARACTERS ) )
{
return;
}
else if ( input.list === null )
{
return;
}
else
{
var datalist = input.list;
var url = PHP_filename + "?partial=" + input.value;
var xhr = new XMLHttpRequest ( );
xhr.onreadystatechange =
function ( )
{
if ( ( this.readyState == 4 ) && ( this.status == 200 ) )
{
var items = this.responseText.split ( ',' );
datalist.innerHTML = "";
items.forEach (
function ( item )
{
if ( item.toLowerCase ( ).
startsWith ( input.value.toLowerCase ( ) ) )
{
var option = new Option ( item, item, false, false );
option.value = item;
datalist.appendChild ( option );
}
}
);
}
};
xhr.open ( "GET", url, true );
xhr.send ( );
}
}
Some points regarding keyup_handler.
5.4. HTML Page
As soon as the autocomplete implementation was completed, it replaced the original select statement for countries. I was pleasantly surprised by the results. The only modifications required were:
- The actual replacement of the <select> element by the <input> and <datalist> elements.
- The addition of a reference to the auto_complete.js file.
- The addition of the AutoComplete.initialize invocation.
A somewhat simplified version of the resulting HTML page follows.
<!DOCTYPE html>
<html lang="en">
<head>
<title>Test Autocomplete</title>
<meta http-equiv="Content-type"
content="text/html;charset=UTF-8" />
<meta name="viewport"
content="width=device-width, initial-scale=1" />
</head>
<body>
<form id="membership"
action="" >
<table style="empty-cells:show;">
<tbody>
<tr>
<td>
<span >State</span>
</td>
<td >
<input id="state"
type="text"
style="width:125px;"
list="state_list"
autocomplete="off"
placeholder="Enter State" />
<datalist id="state_list"></datalist>
</td>
<td>
<span >Country</span>
</td>
<td colspan="3" >
<input id="country"
type="text"
style="width:405px;"
list="country_list"
autocomplete="off"
placeholder="Enter Country" />
<datalist id="country_list"></datalist>
</td>
</tr>
</tbody>
</table>
</form>
<script src="./auto_complete.js"></script>
<script>
AutoComplete.initialize ( "state",
"partial_states.php" );
AutoComplete.initialize ( "country",
"partial_countries.php" );
</script>
</body>
</html>
Because each browser sets the width of the <input> element to a value of its own choosing, the width attribute is incorporated to assure that the value returned by the autocomplete will fit.
6. References
7. Download
The file download (in the referenced ZIP file) contains the files that demonstrate autocompletion.
auto_complete.js
config.php
countries_CT.csv
get_partial_countries.sql
get_partial_states.sql
partial_countries.php
partial_states.php
states_CT.csv
test.html
It is recommended that:
- The all downloaded files be placed in a single directory on a PHP/MySQL hosting machine.
- The CSV files be imported into the Countries_CT and States_CT tables as their names imply.
- The config.php file contents be revised for use on the PHP/MySQL hosting machine.
- The two SQL files be used to develop stored procedures on the PHP/MySQL hosting machine.
8. Conclusion
This article has provided the code necessary to implement autocomplete on an <input> element without using third-party software.
9. Development Environment
The Autocomplete Project was developed in the following environment:
Microsoft Windows 7 Professional SP 1 |
Microsoft Visual Studio 2008 Professional SP1 |
Microsoft Visual C# 2008 |
Microsoft .NET Framework Version 3.5 SP1 |
10. Supported Browsers
The following depicts the browsers that support autocompletion.
| | | | | | |
Chrome | Edge | Firefox | Firefox
Developer | Internet
Explorer | Opera | Safari |
Neither Internet Explorer nor Safari have revisions for Windows 7.
11. History
03/04/2022 | Original article |