Autocomplete input to pull cities from database with javascript, jquery and PHP

Question

I have the following code to perform an autocomplete function within an input. Something like when I type in the letter "M" all those cities starting with "M" shows on my input box. This code works if I have just a few cities but once I have hundreds or thousands of cities is not working, I'm guessing because the variable $list cannot store all the information.

Is there a better way to do or fix this?

Thank you

<?php

 include("config.php");

   $db = pg_connect("$db_host $db_name $db_username $db_password");

    $query = "SELECT * FROM cities";
    $result = pg_query($query);
    if (!$result) {
        echo "Problem with query " . $query . "<br/>";
        echo pg_last_error();
        exit();
    }

    $get_total_rows = pg_numrows($result);
    $i =  $get_total_rows; 

while($myrow = pg_fetch_assoc($result)) {
    $city = $myrow[city];
    $country = $myrow[country];
    if ($i == $get_total_rows){
        $list = "'" . $city . " (" . $country . ")'";
        $i = $i -1;
    }
    else {$list = $list . ", '" . $city . " (" . $country . ")'";} 
     }

?>

<form name="form1" method="post" action="searchresults.php" >
    <input id="hero-demo" autofocus type="search" name="search" placeholder="City" >
    <input type="submit" name="submit" value="Search">
</form>

<script src="jquery.auto-complete.js"></script>
<script>
    $(function(){
        $('#hero-demo').autoComplete({
            minChars: 1,
            source: function(term, suggest){
                term = term.toLowerCase();
                var choices = [<?= $list ?>];
                var suggestions = [];
                for (i=0;i<choices.length;i++)
                    if (~choices[i].toLowerCase().indexOf(term)) suggestions.push(choices[i]);
                suggest(suggestions);
            }
        });

    });

</script>

UPDATE

and this is the code for jquery.auto-complete.js

(function($){
$.fn.autoComplete = function(options){
    var o = $.extend({}, $.fn.autoComplete.defaults, options);

    // public methods
    if (typeof options == 'string') {
        this.each(function(){
            var that = $(this);
            if (options == 'destroy') {
                $(window).off('resize.autocomplete', that.updateSC);
                that.off('blur.autocomplete focus.autocomplete keydown.autocomplete keyup.autocomplete');
                if (that.data('autocomplete'))
                    that.attr('autocomplete', that.data('autocomplete'));
                else
                    that.removeAttr('autocomplete');
                $(that.data('sc')).remove();
                that.removeData('sc').removeData('autocomplete');
            }
        });
        return this;
    }

    return this.each(function(){
        var that = $(this);
        // sc = 'suggestions container'
        that.sc = $('<div class="autocomplete-suggestions '+o.menuClass+'"></div>');
        that.data('sc', that.sc).data('autocomplete', that.attr('autocomplete'));
        that.attr('autocomplete', 'off');
        that.cache = {};
        that.last_val = '';

        that.updateSC = function(resize, next){
            that.sc.css({
                top: that.offset().top + that.outerHeight(),
                left: that.offset().left,
                width: that.outerWidth()
            });
            if (!resize) {
                that.sc.show();
                if (!that.sc.maxHeight) that.sc.maxHeight = parseInt(that.sc.css('max-height'));
                if (!that.sc.suggestionHeight) that.sc.suggestionHeight = $('.autocomplete-suggestion', that.sc).first().outerHeight();
                if (that.sc.suggestionHeight)
                    if (!next) that.sc.scrollTop(0);
                    else {
                        var scrTop = that.sc.scrollTop(), selTop = next.offset().top - that.sc.offset().top;
                        if (selTop + that.sc.suggestionHeight - that.sc.maxHeight > 0)
                            that.sc.scrollTop(selTop + that.sc.suggestionHeight + scrTop - that.sc.maxHeight);
                        else if (selTop < 0)
                            that.sc.scrollTop(selTop + scrTop);
                    }
            }
        }
        $(window).on('resize.autocomplete', that.updateSC);

        that.sc.appendTo('body');

        that.sc.on('mouseleave', '.autocomplete-suggestion', function (){
            $('.autocomplete-suggestion.selected').removeClass('selected');
        });

        that.sc.on('mouseenter', '.autocomplete-suggestion', function (){
            $('.autocomplete-suggestion.selected').removeClass('selected');
            $(this).addClass('selected');
        });

        that.sc.on('mousedown click', '.autocomplete-suggestion', function (e){
            var item = $(this), v = item.data('val');
            if (v || item.hasClass('autocomplete-suggestion')) { // else outside click
                that.val(v);
                o.onSelect(e, v, item);
                that.sc.hide();
            }
            return false;
        });

        that.on('blur.autocomplete', function(){
            try { over_sb = $('.autocomplete-suggestions:hover').length; } catch(e){ over_sb = 0; } // IE7 fix :hover
            if (!over_sb) {
                that.last_val = that.val();
                that.sc.hide();
                setTimeout(function(){ that.sc.hide(); }, 350); // hide suggestions on fast input
            } else if (!that.is(':focus')) setTimeout(function(){ that.focus(); }, 20);
        });

        if (!o.minChars) that.on('focus.autocomplete', function(){ that.last_val = '\n'; that.trigger('keyup.autocomplete'); });

        function suggest(data){
            var val = that.val();
            that.cache[val] = data;
            if (data.length && val.length >= o.minChars) {
                var s = '';
                for (var i=0;i<data.length;i++) s += o.renderItem(data[i], val);
                that.sc.html(s);
                that.updateSC(0);
            }
            else
                that.sc.hide();
        }

        that.on('keydown.autocomplete', function(e){
            // down (40), up (38)
            if ((e.which == 40 || e.which == 38) && that.sc.html()) {
                var next, sel = $('.autocomplete-suggestion.selected', that.sc);
                if (!sel.length) {
                    next = (e.which == 40) ? $('.autocomplete-suggestion', that.sc).first() : $('.autocomplete-suggestion', that.sc).last();
                    that.val(next.addClass('selected').data('val'));
                } else {
                    next = (e.which == 40) ? sel.next('.autocomplete-suggestion') : sel.prev('.autocomplete-suggestion');
                    if (next.length) { sel.removeClass('selected'); that.val(next.addClass('selected').data('val')); }
                    else { sel.removeClass('selected'); that.val(that.last_val); next = 0; }
                }
                that.updateSC(0, next);
                return false;
            }
            // esc
            else if (e.which == 27) that.val(that.last_val).sc.hide();
            // enter or tab
            else if (e.which == 13 || e.which == 9) {
                var sel = $('.autocomplete-suggestion.selected', that.sc);
                if (sel.length && that.sc.is(':visible')) { o.onSelect(e, sel.data('val'), sel); setTimeout(function(){ that.sc.hide(); }, 20); }
            }
        });

        that.on('keyup.autocomplete', function(e){
            if (!~$.inArray(e.which, [13, 27, 35, 36, 37, 38, 39, 40])) {
                var val = that.val();
                if (val.length >= o.minChars) {
                    if (val != that.last_val) {
                        that.last_val = val;
                        clearTimeout(that.timer);
                        if (o.cache) {
                            if (val in that.cache) { suggest(that.cache[val]); return; }
                            // no requests if previous suggestions were empty
                            for (var i=1; i<val.length-o.minChars; i++) {
                                var part = val.slice(0, val.length-i);
                                if (part in that.cache && !that.cache[part].length) { suggest([]); return; }
                            }
                        }
                        that.timer = setTimeout(function(){ o.source(val, suggest) }, o.delay);
                    }
                } else {
                    that.last_val = val;
                    that.sc.hide();
                }
            }
        });
    });
}

$.fn.autoComplete.defaults = {
    source: 0,
    minChars: 3,
    delay: 150,
    cache: 1,
    menuClass: '',
    renderItem: function (item, search){
        // escape special characters
        search = search.replace(/[-\/\\^$*+?.()|[\]{}]/g, '\\$&');
        var re = new RegExp("(" + search.split(' ').join('|') + ")", "gi");
        return '<div class="autocomplete-suggestion" data-val="' + item + '">' + item.replace(re, "<b>$1</b>") + '</div>';
    },
    onSelect: function(e, term, item){}
};
}(jQuery));

Answer 1

So there are a few parts to this answer.

One part is that your files should really be separated out. And two is that you should be using ajax to get your data not inserting it through php.

Since we don't know you're table structure I'm going to take some liberties with the query.

This should get you on the right path. It's got your PHP script setup to where it return a JSON Object which it then passes to your auto complete function.

PHP - data.php

<?php
    // Loads Database Details
    include("config.php");

    // Creates Connection
    $db = pg_connect("$db_host $db_name $db_username $db_password");

    // Grabs Selection
    $userInput = $_GET['typed'];

    // Runs Query On Database
    $query = "SELECT * FROM cities";

    // Runs Query And Places It
    $result = pg_query($query);

    // Kills Script If No Results
    if (!$result) {
        echo "Problem with query " . $query . "<br/>";
        echo pg_last_error();

        return json_encode(array());

        die();
    }

    $list = pg_fetch($result);

    return json_encode($list);
?>

HTML/Script

<form name="form1" method="post" action="searchresults.php" >
    <input id="hero-demo" autofocus type="search" name="search" placeholder="City" >
    <input type="submit" name="submit" value="Search">
</form>

<script src="jquery.auto-complete.js"></script>
<script>
    var DBsource = [];

    $('document').ready(function(){
        $.get("data.php", function(response){
            DBsource = response;
        });
        $('#hero-demo').autoComplete({
                minChars: 2,
                source: DBsource
            });
        });
    });
</script>

Answer 2

Usually you don't take all the data to the client, but you perform an ajax call for each character you insert (with a proper debounce method).

Anyway I would try to separate client code from server code and I would try to get the array as a json by using $.ajax function

Answer 3

increase minChars to at least 2 or even more if you have so many cities. no body wants to see that big auto-complete box.

Answer 4

If you're willing to use a library, consider Select2. It can search through large lists of options, either from a <select> element or from an api, and has a large variety of options