Cjuiautocomplete

Yii 1.1 General Discussion
1

Hi Yii Forum contributors,

I am working on a CJuiAutoComplete search box. I want to search the user’s full name and username at the same time.

I am getting an exception for my current code:




CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'username, fullname' in 'where clause'. The SQL statement executed was: SELECT `user_id`, `username`, `fullname`, `photofile` FROM `user` `u` JOIN `user_info` `p` ON u.user_id=p.user_info_id WHERE `username, fullname` LIKE '%Jo%'

I renderPartial my search in my view:




<?php 

    Yii::app()->clientScript->registerCoreScript('jquery.ui');


    //will render html correctly in search results

    Yii::app()->clientScript->registerScriptFile('/js/jquery.ui.autocomplete.html.js');




      $form=$this->beginWidget('CActiveForm', array(

        'action'=>Yii::app()->createUrl('user/person'),

        'method'=>'post',

        'id'=>'searchform',  

));

?>


 <?php      

       $this->widget('zii.widgets.jui.CJuiAutoComplete', array(

            'id'=>'searchBox',

	    'name'=>'searches',

	    'source'=>$this->createUrl('user/complete'),

	    // additional javascript options for the autocomplete plugin

            //in the controller

            'options'=>array(

               'showAnim'=>'fold', 

               'delay'=>300, // wait 300ms after user stops typing

               'minLength'=>2, // length of entered characters before completing

               'select'=>'js:function(event, ui) { 

                     $("#searchBox").val(ui.item.id); // ui.item.whatYouReturnInController

                     alert(ui.item.id+" "+ui.item.label + " "+ui.item.value);

                     $(this).parents("form").submit();

                     return false;

                }',

                'close'=>'js:function(){        

                }',

                

            ),

            'htmlOptions'=>array('class'=>'','style'=>'height:20px;width:200px;','size'=>40, 'placeholder'=>'Search Profiles...'

            ),

	));

?>

        <div class="row buttons">

                <?php 

                $srhmag = Yii::app()->request->baseUrl.'/images/magnifying_glass.png';

                CHtml::imageButton($srhmag, array('alt'=>'Search','width'=>17, 'height'=>17, 'title'=>'Search HOUSEsports Profiles', 'id'=>'imgSrh')); 

                ?>

        </div>

<?php $this->endWidget(); ?>

This is my action in my controller:




        public function actionComplete(){


              $term = Yii::app()->getRequest()->getParam('term', false);

                            

              $user = Yii::app()->db->createCommand()

                    ->select('user_id, username, fullname, photofile')

                    ->from('user u')

                    ->join('user_info p', 'u.user_id=p.user_info_id')

                    ->where(array('or like', 'username, fullname', '%'.$term.'%'))

                    ->queryColumn();              

              

              $res=array();

              foreach($user AS $model)

              {

                      $res[]=array(

                              'label'=>CHtml::image(Yii::app()->request->baseUrl."/images/").$model->photofile,

                              'value'=>CHtml::encode($model->fullname).CHtml::encode($model->username),

                              'id'=>(int)$model->user_id,

                      );

              }

                           

              echo CJSON::encode($res);

              Yii::app()->end();

        }

Can someone please help me to structure my query correctly? I have researched and searched and can’t find the way to do this. Thanks in advance.

2

What about this:


 ->where('username like :term or fullname like :term'))

->params(array(':term'=>'%'.$term.'%'))
3

It looks like your where array is not correct

http://www.yiiframework.com/doc/api/1.1/CDbCommand#where()-detail

4

or use findAll or find

5

in controller

use like this




 public function actionAutocomplete() 

	 {

	  if (isset($_GET['term'])) {

		$criteria=new CDbCriteria;

		$criteria->alias = "first_name";

		$criteria->condition = "first_name   like '" . $_GET['term'] . "%'"." or last_name   like '" . $_GET['term'] . "%'";

		$criteria->order = 'first_name ASC';

		$Students = Students::model()->findAll($criteria);

	

		$return_array = array();

		foreach($Students as $Student) {

		  $return_array[] = array(

						'label'=>ucfirst($Student->first_name).' '.ucfirst($Student->middle_name).' '.ucfirst($Student->last_name) ,

						'id'=>$Student->id,

						);

		}

		echo CJSON::encode($return_array);

	  }

	}
6

Thanks Rajith R, bennouna, and zaccaria. This is what I ended up with working in my controller:




        public function actionComplete(){

            

            $term = Yii::app()->getRequest()->getParam('term', false);

       

            $users = Yii::app()->db->createCommand()

                    ->select('user_id, username, fullname, photofile')

                    ->from('user u')

                    ->join('user_info p', 'u.user_id=p.user_info_id')

                    ->where('username like :term or fullname like :term',array(':term'=>'%'.$term.'%'))

                    ->queryAll();              

              

              $res=array();

              foreach($users AS $user)

              {

                      $res[]=array(

                              'label'=>$user['photofile']==="" ? CHtml::image(Yii::app()->request->baseUrl."/images/man.gif").$user['fullname']."<br/>".$user['username'] : CHtml::image(Yii::app()->request->baseUrl.'/images/'.$user['photofile']).$user['fullname']."-".$user['username'],

                              'name'=>$user['username'],

                              'id'=>(int)$user['user_id'],

                      );

              }


              echo CJSON::encode($res);

              Yii::app()->end();

        }