title: Javascript autocomplete using large set of possible names
body: <p>I want to provide autocomplete functionality for a form input element from a database of about 1 million possible plant names.</p>
  <p>I have limited knowledge of javascript and after various attempts I copied and pasted some code from w3Schools (<a href="https://www.w3schools.com/howto/howto_js_autocomplete.asp" rel="nofollow noreferrer">https://www.w3schools.com/howto/howto_js_autocomplete.asp</a>) which I only partially understand.</p>
  <p>The w3Schools example uses a hard coded array of about 200 country names called <code>countries</code>. I changed the assignment to this array to a PHP query to an SQLite database called <code>wcvp</code> (World Checklist of Vascular Plants. See: <a href="https://wcvp.science.kew.org/" rel="nofollow noreferrer">https://wcvp.science.kew.org/</a> and <a href="http://sftp.kew.org/pub/data-repositories/WCVP/" rel="nofollow noreferrer">http://sftp.kew.org/pub/data-repositories/WCVP/</a>).</p>
  <p>When I restricted the query to about 39000 distinct genus names the autocomplete functionality seemed to work very well. When I changed the query to include all the distinct species and sub-species (about 1.1 million) in the database, I just get a blank page in the browser. I assume the difference in behaviour is down to the extra number of items involved.</p>
  <p>I would be very interested to know more about why the autocomplete functionality fails with the bigger data set rather than just taking longer to work. Also I would be interested to know of any approaches I might take to make it cope with the bigger data set.</p>
  <p>I have posted the full code at the end of this question. The key bits I have added are related to querying the <code>wcvp</code> database and assigning the results of the query to the <code>countries</code> array:</p>
  <p>This gives about 39000 results and autocomplete works well: <code>$sql = &quot;SELECT DISTINCT genus FROM wcvp&quot;;</code></p>
  <p>This gives about 1.1 million results and results in the page failing to load into my Firefox browser: <code>$sql = &quot;SELECT DISTINCT taxon_name FROM wcvp&quot;;</code></p>
  <p>The following code is how I populate the JavaScript array with the results from the SQL queries above:</p>
  <pre><code>  countries = [
  &lt;?php
        foreach($plantNames as $plantName){
          echo &quot;'{$plantName[0]}',&quot;;
        }
  ?&gt;
        ];
  </code></pre>
  <p>Apart from the additions I  made, referred to above, I also removed the style element from the w3Schools example which does not alter the functionality significantly but shortens the code. The full code (which includes the parts shown above and without the CSS) is:</p>
  <pre><code>&lt;?php
    require './sqlite/dsn.php';
    $sql = &quot;SELECT DISTINCT genus FROM wcvp&quot;;
    //$sql = &quot;SELECT DISTINCT taxon_name FROM wcvp&quot;;
    $stmt = $db-&gt;prepare($sql);
    $stmt-&gt;execute();
    $plantNames = $stmt-&gt;fetchAll();
  ?&gt;
  &lt;!DOCTYPE html&gt;
  &lt;html&gt;
  &lt;head&gt;
  &lt;meta name=&quot;viewport&quot; content=&quot;width=device-width, initial-scale=1.0&quot;&gt;
  &lt;/head&gt;     
  &lt;body&gt;
  
  &lt;h2&gt;Autocomplete&lt;/h2&gt;
  
  &lt;p&gt;Start typing:&lt;/p&gt;
  
  &lt;!--Make sure the form has the autocomplete function switched off:--&gt;
  &lt;form autocomplete=&quot;off&quot; action=&quot;/action_page.php&quot;&gt;
    &lt;div class=&quot;autocomplete&quot; style=&quot;width:300px;&quot;&gt;
      &lt;input id=&quot;myInput&quot; type=&quot;text&quot; name=&quot;myCountry&quot; placeholder=&quot;Country&quot;&gt;
    &lt;/div&gt;
    &lt;input type=&quot;submit&quot;&gt;
  &lt;/form&gt;
  &lt;script&gt;
  function autocomplete(inp, arr) {
    /*the autocomplete function takes two arguments,
    the text field element and an array of possible autocompleted values:*/
    var currentFocus;
    /*execute a function when someone writes in the text field:*/
    inp.addEventListener(&quot;input&quot;, function(e) {
        var a, b, i, val = this.value;
        /*close any already open lists of autocompleted values*/
        closeAllLists();
        if (!val) { return false;}
        currentFocus = -1;
        /*create a DIV element that will contain the items (values):*/
        a = document.createElement(&quot;DIV&quot;);
        a.setAttribute(&quot;id&quot;, this.id + &quot;autocomplete-list&quot;);
        a.setAttribute(&quot;class&quot;, &quot;autocomplete-items&quot;);
        /*append the DIV element as a child of the autocomplete container:*/
        this.parentNode.appendChild(a);
        /*for each item in the array...*/
        for (i = 0; i &lt; arr.length; i++) {
          /*check if the item starts with the same letters as the text field value:*/
          if (arr[i].substr(0, val.length).toUpperCase() == val.toUpperCase()) {
            /*create a DIV element for each matching element:*/
            b = document.createElement(&quot;DIV&quot;);
            /*make the matching letters bold:*/
            b.innerHTML = &quot;&lt;strong&gt;&quot; + arr[i].substr(0, val.length) + &quot;&lt;/strong&gt;&quot;;
            b.innerHTML += arr[i].substr(val.length);
            /*insert a input field that will hold the current array item's value:*/
            b.innerHTML += &quot;&lt;input type='hidden' value='&quot; + arr[i] + &quot;'&gt;&quot;;
            /*execute a function when someone clicks on the item value (DIV element):*/
            b.addEventListener(&quot;click&quot;, function(e) {
                /*insert the value for the autocomplete text field:*/
                inp.value = this.getElementsByTagName(&quot;input&quot;)[0].value;
                /*close the list of autocompleted values,
                (or any other open lists of autocompleted values:*/
                closeAllLists();
            });
            a.appendChild(b);
          }
        }
    });
    /*execute a function presses a key on the keyboard:*/
    inp.addEventListener(&quot;keydown&quot;, function(e) {
        var x = document.getElementById(this.id + &quot;autocomplete-list&quot;);
        if (x) x = x.getElementsByTagName(&quot;div&quot;);
        if (e.keyCode == 40) {
          /*If the arrow DOWN key is pressed,
          increase the currentFocus variable:*/
          currentFocus++;
          /*and and make the current item more visible:*/
          addActive(x);
        } else if (e.keyCode == 38) { //up
          /*If the arrow UP key is pressed,
          decrease the currentFocus variabe:*/
          currentFocus--;
          /*and and make the current item more visible:*/
          addActive(x);
        } else if (e.keyCode == 13) {
          /*If the ENTER key is pressed, prevent the form from being submitted,*/
          e.preventDefault();
          if (currentFocus &gt; -1) {
            /*and simulate a click on the &quot;active&quot; item:*/
            if (x) x[currentFocus].click();
          }
        }
    });
    function addActive(x) {
      /*a function to classify an item as &quot;active&quot;:*/
      if (!x) return false;
      /*start by removing the &quot;active&quot; class on all items:*/
      removeActive(x);
      if (currentFocus &gt;= x.length) currentFocus = 0;
      if (currentFocus &lt; 0) currentFocus = (x.length - 1);
      /*add class &quot;autocomplete-active&quot;:*/
      x[currentFocus].classList.add(&quot;autocomplete-active&quot;);
    }
    function removeActive(x) {
      /*a function to remove the &quot;active&quot; class from all autocomplete items:*/
      for (var i = 0; i &lt; x.length; i++) {
        x[i].classList.remove(&quot;autocomplete-active&quot;);
      }
    }
    function closeAllLists(elmnt) {
      /*close all autocomplete lists in the document,
      except the one passed as an argument:*/
      var x = document.getElementsByClassName(&quot;autocomplete-items&quot;);
      for (var i = 0; i &lt; x.length; i++) {
        if (elmnt != x[i] &amp;&amp; elmnt != inp) {
          x[i].parentNode.removeChild(x[i]);
        }
      }
    }
    /*execute a function when someone clicks in the document:*/
    document.addEventListener(&quot;click&quot;, function (e) {
        closeAllLists(e.target);
    });
  }
  
        countries = [
  &lt;?php
        foreach($plantNames as $plantName){
          echo &quot;'{$plantName[0]}',&quot;;
        }
  ?&gt;
        ];
  
  /*An array containing all the country names in the world:*/
  //var countries = [&quot;Afghanistan&quot;,&quot;Albania&quot;,&quot;Alg.......emen&quot;,&quot;Zambia&quot;,&quot;Zimbabwe&quot;];
  
  /*initiate the autocomplete function on the &quot;myInput&quot; element, and pass along the countries array as possible autocomplete values:*/
  autocomplete(document.getElementById(&quot;myInput&quot;), countries);
  &lt;/script&gt;
  
  &lt;/body&gt;
  &lt;/html&gt;
  </code></pre>
  <p><strong>Edit:</strong> If I limit the query to 100,000 results (<code>$sql = &quot;SELECT DISTINCT taxon_name FROM wcvp LIMIT 100000&quot;;</code>) the autocomplete functionality works but it is a bit slow and would probably not be a great user experience.</p>