Autocomplete other field with Yii CJuiAutoComplete

Question

I've got the following situation:

Form with a field for postal codes, and a field for cities.

I want an autocomplete on the postalcode field, so when the user types for instance 1000, the autocomplete values will show "1000 - Brussels". When this value is then selected, 1000 gets filled in in the postal code field, and Brussels gets filled in in the City field.

Postal code, city, and concatenated info will come from a mysql database:

I have the autocomplete working with only the postalcodes, but have no clue on how to implement the described effect (= populating a second field).

Current form code:

<div class="row">
        <?php echo $form->labelEx($model,'PostalCode'); ?>
        <?php //echo $form->textField($model,'PostalCode',array('size'=>10,'maxlength'=>50));

                $this->widget('zii.widgets.jui.CJuiAutoComplete', array(
                'name'=>'PostalCode',
                'value'=>$model->PostalCode,
            //'source'=>$people, // <- use this for pre-set array of values
                'source'=>$this->createUrl('BeCity/GetBelgianPostalCodes'),// <- path to controller which returns dynamic data
                // additional javascript options for the autocomplete plugin
                'options'=>array(
                        'minLength'=>'1', // min chars to start search
                        'showAnim'=>'fold'
                ),
                ));

                ?>
        <?php echo $form->error($model,'PostalCode'); ?>
    </div>

Current controller action code:

public function actionGetBelgianPostalCodes()
        {                     
            $res =array();

            if (isset($_GET['term'])) {
                    // http://www.yiiframework.com/doc/guide/database.dao
                    $qtxt ="SELECT
                            DISTINCT
                            bc.PostalCode as PostalCode,
                            bc.NameNL as CityName,
                            CONCAT(bc.PostalCode, ' - ', bc.NameNL) as FullCityName
                            FROM be_city bc
                            WHERE bc.PostalCode LIKE :qterm
                            ORDER BY bc.PostalCode, bc.NameNL ASC";
                    $command =Yii::app()->db->createCommand($qtxt);
                    $command->bindValue(":qterm", $_GET['term'].'%', PDO::PARAM_STR);
                    $res =$command->queryColumn();
            }

            echo CJSON::encode($res);
            Yii::app()->end();
        }

Not sure if the controller action is entirely correct, the $command->queryAll() doesn't seem to work so I use queryColumn() instead but it returns only the first column ?

Any hints ?

Also, additional question, I'd like the link to the controller action to be dynamic. If the in a previous Country dropdown the user selected Belgium then the call should be made to 'BeCity/GetBelgianPostalCodes'. If it was France, it should be to 'FrCity/GetFrenchPostalCodes'. Is this possible, and how ?

thanks

Answer 1

Here the way I found (I adjust for your application so I hope no typo...) I also changed the name of the widget to be myPostCode best would probable to keep the default name given by Yii :

<?php
        $this->widget('zii.widgets.jui.CJuiAutoComplete', array(
        'name'=>'myPostCode',
        'value'=>$model->PostalCode,
        'source'=>$this->createUrl('BeCity/GetBelgianPostalCodes'),
        'options'=>array(
                'minLength'=>'1', // min chars to start search
                'showAnim'=>'fold'
                //focus option will tell what is displayed in field during the selection
                'focus'=> 'js:function( event, ui ) {
                    $( "#myPostCode" ).val( ui.item.postalcode );
                    return false;
                }',
                //select function will tell where go each field
                'select'=>'js:function( event, ui ) {
                    $( "#myPostCode" ).val( ui.item.postalcode );
                    $( "#CityName" ).val(ui.item.cityname);
                    return false;
                }'
        ),
        ));

//Here is the code for the display.
//This code MUST be AFTER the widget
//It should be possible to include it in the widget but I do not know how.
// You may change the line "<a>"+item.postalcode... with what you want to display but must keep the a tag."

    Yii::app()->clientScript->registerScript('input', '
        $("#myPostCode").data("autocomplete")._renderItem = function( ul, item ) {
        return $( "<li></li>" )
    .data( "item.autocomplete", item )
    .append( "<a>"+item.postalcode + " - " + item.cityname+"</a>")
    .appendTo( ul );
    };');

?>

You also need to change your query to return all the data's

$res =$command->queryAll();

Answer 2

Set the Ajax select option as follows:

$this->widget('zii.widgets.jui.CJuiAutoComplete', array(
    'name'=>'PostalCode',
    'source'=>$this->createUrl('/site/getpostalcode'),
    // additional options for the autocomplete plugin
    'options'=>array(
        'minLength'=>'4',
        'select'=>"js:function(event, ui) {
                    $('#PostalCode').val(ui.item.postalcode);
                    $('#CityName').val(ui.item.cityname);
                  }",
     ),
    'htmlOptions'=>array(
        'style'=>'width: 200px;',
        'placeholder' => 'Postal Code'
    ),
));