Tag Archives: MySQL

jQuery UI Autocomplete, JSON and PHP

In case you are not familiar with the term ‘autocomplete,’ it refers to the kind of functionality you get with Google mail when typing a ‘to’ email address, or in Google Suggest.  As you type characters in the search box, it ‘autocompletes’ by providing you a list of matches. The list gets refined the more you type.  We also have  a WebSmart example you can try out live.  Autocomplete widgets can help make your applications more user-friendly, because they make it much easier for users to find items in large sets of data.

We’ve used Autocomplete plugins in a number of our web applications (both for customers and internal use) for some time now. I just stumbled upon the relatively new jQuery UI Autocomplete plugin. There is some good example code at the jQueryUI site.  Another good tutorial on how to use it is this one.

This post explains how to code it for a typical usage. Note that although I am using PHP here, you can accomplish exactly the same thing with PML and DB2 SQL in WebSmart ILE.

Here’s a sample image from a work in progress (new customer log system using jQuery, AJAX, PHP and MySQL). I’ve blurred the customers, as this is real data from our files.



Let’s look at the code required to implement this. First, the client-code (jQuery and JavaScript).

Client Code

			minLength: 3,
	 	    delay : 400,
			source: function(request, response) { 

				   url: 	 "index.php",
				   data:  {
				   			mode : "ajax",
				   			component : "consearch",
				   			searcharg : "company",
				   			task : "display",
				   			limit : 15,
				   			term : request.term
				   dataType: "json",

				   success: function(data) 	{



		   select:  function(e, ui) {
				var keyvalue = ui.item.value;
				alert("Customer number is " + keyvalue); 


The important pieces here are the source property and the select event. The source property really defines 2 key aspects of the autocomplete widget: the source (input) of the data to populate, and how to put it in the autocomplete box (formatting, etc).

First, it defines the source of the data. This can be a local Javascript array – useful for small, relatively static data sets such as State abbreviations. It can also be a string with a value of a URL. The URL will point to a script (see below for example code) that gets invoked via AJAX and returns an array of items. While this may seem the obvious choice for most cases, it’s really not that useful because you have no control over how you format your response data. The third possibility, as shown in the example code above, is an anonymous function. This is the most useful coding pattern. The function accepts two parameters- request and response. Request is an object that has a property called term, whose value is whatever the user types in to search for. So, if the user types ‘AME’, then request.term == ‘AME’. Response is a callback function name that implicitly accepts one parameter, the data to display in the autocomplete results box. This is poorly explained and documented on the jQuery UI site. I only really understood this by reading Dan Wellman’s article. Look at the client code above and you’ll see this line:


That’s a call to the manufactured callback function. You’ll notice, also, that in order to actually do anything, you have to explicitly code the ajax call in the callback function for the source property. This is quite a different coding pattern than what we used with the ‘old’ jQuery autocomplete plugin, but the whole design of this code is so much more elegant and flexible.

The other key piece in the client-side code is the select event. This event is triggered when the user clicks on one of the values in the list. In my example code this just causes an alert box to popup that shows the value of the customer number. The select callback function accepts 2 parameters- the event object (not sure why you’d want that) and the ‘selected item’ object (which I’ve called, by convention, ‘ui’). Note that ui has an object called ‘item’ as one of its properties, which in turn has ‘value’ as a property. Hence: ui.item.value refers to the value of the selected line. You can have other, custom properties besides the 2 standard ones (‘value’ and ‘label’), as shown in this example.

I could not find formal documentation describing ui.item, only example code.

In the real world, the next step would be to do either a redirect or an AJAX call to a script/program that lets me work with my selected customer. I’d likely include the customer key in the url, for example:

  var surl = "index.php?task=edit&custno=" + ui.item.value;

Next, let’s look at the server code.

Server Code

	$searcharg  = getparm('searcharg');
	$limit = getparm('limit');
	if (!$limit) $limit=10;
	$sercontact = '';
	$sercompny = '';
	$term = getparm('term'); 

	 	$sercompny = strtoupper($term);
		$query = "SELECT  CLCLNU, CLCLNA FROM CS_CLTF WHERE clclna like '%" . trim($sercompny) . "%' order by CLCLNA";  

	$query = $query." LIMIT  $limit";  // Limit results based on the limit parameter from the ajax call. 

	$rows = $db->get_results($query);  // Populate result set
		// While we retrieve a row from the result set:
	if ($rows)
		foreach($rows as $row)
			$companies[$count] = array("value" => $row->CLCLNU, "label" =>  $row->CLCLNA);
		echo  json_encode($companies);

(FYI I’m using the EZSql library here to do my SQL coding. EZSql is a PHP library that makes coding SQL statements simpler, and supports multiple databases. A comparable library is PDO, part of Zend Frameworks. EZSql is Free and Open Source).

Note the use of json_encode. What this does is produce a JSON-formatted  array of  objects. Each object has a property of “value” (the customer number – key to the customer table) and “label” (customer name- what appears in the autocomplete). Even though I assign these values to an associative array, json_encode treats it like an object when it translates it (which makes sense when you think about it, and is documented on php.net as intended behavior).

To be a little more clear, the returned data from the PHP script to the AJAX callback will look something like this:

[{"value":"3215","label":"COMMUNICATION INC."},
{"value":"8243","label":"ACME MANUFACTURING COMPANY"},
{"value":"5713","label":"ABCDEFS COMPANIES, INC."},
{"value":"4869","label":"ABERYSTWYTH AVENUE COMPANY"},
{"value":"223","label":"ALLKINDS COMPRESSOR"},
{"value":"3705","label":"MIGHTY INSURANCE COMP"}]

It seems to me this Autocomplete plugin is way better than the jQuery autocomplete plugin I’ve used before, particularly since it uses the jQueryUI CSS framework to style the autocomplete dropdown box. It’s more flexible, you can define the code in one object call instead of two, and it’s more likely to be supported and maintained by the jQueryUI team.