Saturday, 12 February 2011

CJuiAutoComplete Example

I've started using Yii and am very impressed with it from an ease-of-use and installation as well as certain areas that have been thought about properly and implemented well. I wanted an auto complete box on a view and had a quick look at the documentation on the Yii site but it had no worked examples and I was new to Yii and didn't understand how it connected up HTML, jQuery and the php code. I have now got something working which I would like to share for others use. Firstly, here is how I declare my Yii php code for the control:
<?php $this->widget('zii.widgets.jui.CJuiAutoComplete', array(
     'name'=>'churchac',
     'source'=>CController::createUrl('user/churchsearch'),
     'options'=>array(
         'minLength'=>'2',
         'focus'=>'js:function( event, ui ) {
          $( "#churchac" ).val( ui.item.label );
              return false;
         }',
         'select'=>'js:function( event, ui ) {$("#churchac").val( ui.item.label );$("#User_church").val( ui.item.value ); return false; }',
     ),
     'htmlOptions'=>array(
         'style'=>'height:20px;'
     ),
)); ?>

A few points to note here. Firstly I have added function handlers for select and focus to override the default behaviour. This is because I want to display a church name to the user but store the database id in a hidden field which is linked to the model for when this view is saved. In my case I have simply updated the autocomplete box (#churchac) and the hidden field (#User_church). Also note the "js:" at the start of the strings for these handlers which stops Yii from escaping the quotes around the jQuery selector names on the HTML.
Next up is the controller function which looks like this:
public function actionchurchsearch($term)
{
    $sql = 'SELECT columns FROM tables LEFT JOIN etc..';
    $keywords = explode(',',$term);
    $sql = $sql.' WHERE name_1 LIKE \'%'.trim($keywords[0]).'%\'';    // Must be at least 1
    if ( count($keywords) >= 2 )
    {
        $sql = $sql.' AND city LIKE \'%'.trim($keywords[1]).'%\'';
    }
    if ( count($keywords) >= 3 )
    {
        $sql = $sql.' AND name_2 LIKE \'%'.trim($keywords[2]).'%\'';
    }
    $sql = $sql.' LIMIT 10';
    $schema=Yii::app()->db->schema;
    $builder=$schema->commandBuilder;
    $command = $builder->createSqlCommand($sql);
    echo json_encode( $command->queryAll());
}

A few more notes here. The function needs the word "action" added to the front as with other actions. You must also allow access to it in the accessRules() function of your controller. It takes a single parameter which is the search text that the user has typed in. In my case, I allow a multi-part search so I split the string up into separate elements and build a standard SQL statement to find the results. I have used the more basic CDbCommandBuilder class since the CDbCriteria is very much geared around retrieving data to populate models with but in my case, I just want simple search data to use in the autocomplete. Also note, you should provide an alias for the columns that you want to display (called label) and to select (called value). You can add other fields but you would need to explicitly use these if required. Note that I don't return the data but echo it to the response since this is an Ajax function. I use a built-in php helper function called json_encode and this ensures that the data is in a format that is usable by the auto-complete control (obviously you can't just echo a php array and expect javascript to handle it).
As a side note, remember this function can be called many times over a very short period so you might need to ensure that it works efficiently and caches search terms if appropriate to avoid frequent database queries. Also, see my newer post on customising the return data:
Post a Comment