Implementing Province => City dependency

Hi everyone!

Since I notice that there are quite some posts on this topic, I thought I should share with you how I implemented a form where one field depends on another. I will use the classic example where the user selects a province and then is limited in his choice of choosing a city only to those cities that actually exist in that province.

The final result can be seen here: http://www.colussigroup.it/index.php/Chiedi-Informazioni (the fields are provincia and comune).

Step1: Create the DB Tables

For the provinces:

CREATE TABLE province (
  id mediumint(9) NOT NULL,
  `name` char(2) default NULL,
  PRIMARY KEY  (id)
);

For the cities:

CREATE TABLE comuni (
  id mediumint(9) NOT NULL auto_increment,
  comune varchar(100) default NULL,
  provincia char(2) default NULL,
  pid mediumint(9) default NULL,
  PRIMARY KEY  (id)
);

Step 2: Create a custom data type for the province combo box

I used the integrate extension developed by Bruce Morrison here:

http://pubsvn.ez.no/community/trunk/extension/integrate/

Of course you can develop your own data type, but since Bruce did a really nice job here I recommend you use it.

Check the README file for installation instructions.

Step 3: Create the form

For those who are not familiar with the creation of eZ Publish forms, here is the tutorial: http://share.ez.no/learn/ez-publish/how-to-create-ez-publish-forms/(language)/eng-GB.

The two fields of the form that interest us were created in this way:

Provincia [External Option] - Table: Province - Index Column: id - Name Column: name

Comune [Text line] - Default Value: Empty

In the chiedi_informazioni.tpl, the code for the two fields is:

<select class="formelement" id="prov" name="ContentObjectAttribute_data_int_{$node.data_map.provincia.id}" style="width: 155px;">
             <option value="">Seleziona la provincia</option>
             {section loop=$node.data_map.provincia.content.options}
                <option value="{$item.val|wash(xhtml)}"{if eq($value, $item.val)} selected="selected"{/if}>{$item.label|wash(xhtml)}</option>
             {/section}
         </select>

and:

<input type="text" value="" name="ContentObjectAttribute_ezstring_data_text_{$node.data_map.comune.id}" id="city" class="formelement" style="width: 150px;" />

At this point we have a fully functioning form, but we are missing the last bit (the purpose of this post): The dependency. We accomplish this in two steps: Creating an ezjscore extension and adding a little bit of jQuery to our .tpl.

Step 4: Create the extension

There is the ezjscore tutorial here: http://share.ez.no/learn/ez-publish/ezjscore-ez-publish-javascript-and-ajax-framework. I spent a few days before I could connect all the pieces. Probably I was lacking some jQuery skills and for this particular case you would find that information here: http://docs.jquery.com/Plugins/autocomplete.

If you don't have ezjscore already, as part of ezflow or ezwebin you should install it first.

This is the code that you should put in your /extension/yourextension/classes/extensionfunction.php (in my case it is /extension/citycomplete/classes/completecityfuncion.php).

<?php

class completeCityFunction extends ezjscServerFunctions
{
    public static function searchCities($args)
{
        $query = '';
        $db =& eZDB::instance();

        $http = eZHTTPTool::instance();
        $query="select distinct(comune) from comuni where comune like '".trim($http->getVariable( 'q' ))."%' and pid ='".$http->getVariable( 'province' )."'";

        $result = $db->arrayQuery($query);

        return $result;
        var_dump($result);


    }
}
?>

I made this extension available through the admin interface and I put it in the site.ini.append.php in the /settings/override directory (you could choose any other of a number of options depending on your needs):

[ExtensionSettings]
ActiveExtensions[]=citycomplete

For further information on HOWTO with extensions, read: http://share.ez.no/learn/ez-publish/an-introduction-to-developing-ez-publish-extensions.

Step 5: Write the Javascript

Simply, here is the code. If you stray from it, you will find trouble.

$(document).ready(function(){

    $("#city").autocomplete('/index.php/ezjscore/call/citycomplete::searchCities', {
        dataType: "json",
        mustMatch: true,
        minChars: 1,
        parse: function(data) {
            return $.map(data.content, function(row) {
                return {
                    data: row.comune,
                    value: row.comune,
                    result: row.comune
                }
            });
        },
        formatItem: function(item) {
            return item;
        },
        extraParams: {
            province: function() { return $("#prov").val();}
        }
    });
    }

Note: If you prefer, you could substitute the combo-box with a text box. In that case you would not need Bruce's datatype anymore, you would have to add a new extension, similar to the citycomplete extension, and you would need to modify you SQL a little.

Ciao!