Lightweight Implementation of Autocomplete

• AutoComplete Project - 8 KB

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:
  • HTML [^]
  • JavaScript [^
  • PHP [^] - to interface between JavaScript and MySQL
  • SQL [^] - for database access only

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:

• <select>
• <datalist>

There appear to be a number of methods by which the <option> elements subordinate to the <select> or <datalist> elements can be populated:

1. A static list of <option>s that appears as child elements beneath a <select> element.
2. A static list of <option>s that appears as child elements beneath a <datalist> element.
3. 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.
4. 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 // partial_countries.php

    ini_set ( "display_errors", 1 );
    error_reporting ( E_ALL );
                                            // define connection variables
    include 'config.php'; 
                                            // get partial prefix of field
    $partial = strval ( htmlspecialchars ( $_GET [ 'partial' ] ) );
                                            // open DB connection and 
                                            // handle possible DB error
    $link = mysqli_connect ( $servername, 
                             $username, 
                             $password, 
                             $database );
    if ( mysqli_connect_errno ( ) ) 
      {
      printf ( "Connect failed: %s\r\n", mysqli_connect_error ( ) );
      exit(1);
      }
                                            // build and execute the SQL;
                                            // handle possible DB error
    $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);
      } 
                                            // process associative array
    while ( $post = mysqli_fetch_assoc ( $result ) ) 
      {
                                            // output each row
      echo ",".$post [ 'country' ];
      }

    mysqli_free_result ( $result );

    mysqli_close ( $link );

    ?>

and

partial_states.php

    <?php // partial_states.php

    ini_set ( "display_errors", 1 );
    error_reporting ( E_ALL );
                                            // define connection variables
    include 'config.php';
                                            // get partial prefix of field
    $partial = strval ( htmlspecialchars ( $_GET [ 'partial' ] ) );
                                            // open DB connection and 
                                            // handle possible DB error
    $link = mysqli_connect ( $servername, 
                             $username, 
                             $password, 
                             $database );
    if ( mysqli_connect_errno ( ) ) 
      {
      printf ( "Connect failed: %s\r\n", mysqli_connect_error ( ) );
      exit(1);
      }
                                            // build and execute the SQL;
                                            // handle possible DB error
    $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);
      } 
                                            // process associative array
    while ( $post = mysqli_fetch_assoc ( $result ) ) 
      {
                                            // output each row
      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 // config.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,

1. Error reporting is defined
2. Connection variables are retrieved
3. User input is retrieved from the query string
4. The connection to the database is acquired
5. The MySQL statement is fashioned and submitted
6. The returned value is converted to an associative array
7. The values in the array are concatenated into a string with comma separation
8. No longer needed memory is freed

5.3. JavaScript

The software that binds its invoker with the PHP interfaces is a JavaScript module:

    // *************************** AutoComplete **************************

    // AutoComplete - a module of utility functions that support 
    //                autocomplete operations 
    //
    // this module creates a single global symbol "AutoComplete" if it 
    // does not already exist

    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.)

  // *************************************************************** $

  // local entry point

  function $ ( id )
    {

    return ( document.getElementById ( id ) );

    } // $


  // ****************************************************** initialize
  
  // global entry point
  
  /// synopsis:
  ///   AutoComplete.initialize ( input_id,
  ///                             PHP_filename );
  ///
  /// <summary>
  ///   initialize the autocomplete event handler for the input_id 
  ///   element
  ///
  /// <param name="input_id">
  ///   string containing the id of the text <input> element that has 
  ///   a datalist to be filled by autocomplete operations
  /// 
  /// <param name="PHP_filename">
  ///   string containing the name of the PHP file that will retrieve 
  ///   values to be placed in the datalist of the <input> element
  ///
  /// <remarks>
  ///   the result of this method is the attachment of the 
  ///   autocomplete keyup event handler to the specified text 
  ///   <input> element

  function initialize ( input_id,
                        PHP_filename )
    {
    var input_element = $ ( input_id );
                                        // add a keyup event listener 
                                        // to the input element
    input_element.addEventListener ( 
      "keyup", 
      function ( event )
        {
        keyup_handler ( event, PHP_filename );
        }
      );

    } // initialize

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.

  // *************************************************** keyup_handler

  // local entry point

  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 ( ) ) )
                  { 
	                                         // create a new <option>
                  var option = new Option ( item, item, false, false );

                  option.value = item;
                  datalist.appendChild ( option );
                  }
                }
              );
            }
          };

      xhr.open ( "GET", url, true );
      xhr.send ( );
      }

    } // keyup_handler

Some points regarding keyup_handler.

• The keyup_handler is declared as the keyup event handler by the initialize function. Its purpose is to supply <option>s to the <datalist> that is defined in the list attribute of the <input> element.
• The guard

  if ( item.toLowerCase ( ).startsWith ( input.value.toLowerCase ( ) ) )

  performs two functions:

  • Insures that the returned item is a value derived from the user input.
  • Insures that extraneous characters are not prepended to any return value. In particular the Byte Order Mark [^] that may be included as the first element in the returned array.
     
• The XMLHttpRequest invocation is asynchrounous.
• This implementation may be invoked by multiple <input> elements on a single HTML page.

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

• How TO - Autocomplete [^]
• Byte Order Mark [^]
• HTML [^]
• HTML Data List [^]
• JavaScript [^
• LIKE [^]
• PHP [^]
• RLIKE [^]
• SQL [^]

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