Autocomplete

Steps shows how to integrate database driven type ahead and autocomplete by lookup query.

Basic Typeahead (single field)

Step1: Set formatter to autocomplete on desired column

$col["formatter"] = "autocomplete"; // autocomplete

Step2: Set format options query. The field in sql aliased ‘v’ will be shown in list

// Fill the name field with query values typeahead
$col["formatoptions"] = array(  "sql"=>"SELECT name as v FROM clients",
                "update_field"=>"name");

Autofill other field.

The field ‘company’ will be filled w.r.t. selected name.

$col["formatoptions"] = array(  "sql"=>"SELECT company as k, name as v FROM clients",
                "update_field"=>"company");

The field aliased ‘k’ will be set in the ‘updated_field’ column (e.g. company)

Callback function

Connect a callback function that will auto-fill multiple fields & search in both name + id,

$col["formatoptions"] = array(  "sql"=>"SELECT *, name as v FROM clients",
                "search_on"=>"concat(name,'-',client_id)",
                "callback"=>"fill_form");

and in html part, define callback function.

<script>
function fill_form(data)
{
  jQuery("input[name=gender].FormElement").val(data.gender);
  jQuery("textarea[name=company].FormElement").val(data.company);

  jQuery("input[name=gender].editable").val(data.gender);
  jQuery("textarea[name=company].editable").val(data.company);
}
</script>

Minimum Length for Autocomplete

You can set minimum characters length to trigger autocomplete by:

$col["formatoptions"]["min_length"] = 1; // defaults to 1

Force Selection

You can also force to select from one of the options, set force_select => true

// callback function
$col["formatoptions"] = array(  "sql"=>"SELECT *, name as v FROM clients where gender='male' ORDER BY name desc",
                "search_on"=>"concat(name,'-',client_id)",
                "force_select"=>true);

By default, autocomplete uses ‘contains’ search. To switch to ‘begins with’ set:

$col["formatoptions"]["op"] = "bw";

Resources

See Live Demo
You can check this demo in archive demos/integrations/autocomplete.php