Difference between #3 and #2 of Select2 Infinite Scroll With Remote Data in Yii

unchanged
Title
Select2 Infinite Scroll With Remote Data in Yii
unchanged
Category
How-tos
unchanged
Tags
select2, yii, infinite scroll, remote data, ajax
unchanged
Content
I was able to set up the infinite scroll of select2 in Yii. I am glad to share
it with you. The demo and source code can be found
[here](http://ivaynberg.github.io/select2/#infinite
"http://ivaynberg.github.io/select2/#infinite"). However, select2 for
Yii can be found [here](http://www.yiiframework.com/extension/select2/) which
uses a widget.

The controller action is shown below


~~~
[php]
public function actionSearch() 
	 {
		if(Yii::app()->request->isAjaxRequest)
		{
			if (isset($_GET['term'])&& !empty($_GET['term']))

			{
			 	// http://www.yiiframework.com/doc/guide/database.dao	
				$search = "SELECT SQL_CALC_FOUND_ROWS pm.title as text, pm.poem_id as
id,bio.pix as pix,n.penname as name FROM pty_poem pm
				LEFT JOIN pty_biodata bio ON pm.poet_id = bio.poet_id LEFT JOIN pty_name n
ON pm.poet_id = n.poet_id  WHERE category IN ( Select cat.value from
pty_category cat where cat.category LIKE CONCAT('%', :title,
'%')) Limit 5 ";
				
				$command = Yii::app()->db->createCommand($search);
				$command->bindValue(":title",
'%'.$_GET['term'].'%', PDO::PARAM_STR);
				$result =$command->queryAll();	
				
				$total_rows = Yii::app()->db->createCommand('SELECT FOUND_ROWS()
AS total')->queryRow();				
				$total = $total_rows['total'];

				 echo
CJSON::encode(array('result'=>$result,'total'=>$total));
				 Yii::app()->end();
			}
				
		 } else
				throw new CHttpException(403, "The requested page does not
exist");    	
	}
~~~

The SQL_CALC_FOUND_ROWS is a MySQL function that selects all the rows that can
be found given that there is no limit. But it does not give you the result.
Another query has to be executed with FOUND_ROWS() to retrieve the found rows
executed before it. This is needed in order to know the total number of rows
searchable for any search string. The client code would use this to know if it
should continue sending request for more pages. 

Assuming that you downloaded and installed the Yii select2, you can have this
below


~~~
[php]
<?php		
		echo
CHtml::textField('test','',array('id'=>'test','style'=>'height:30px;width:350px;'));					
			
		$this->widget('ext.select2.ESelect2',
                      array(
                           'selector' => "#test",
                           'options'  => array(                    
         
                               'placeholder'        => 'Search
poems by category',
                               'quietMillis' => 200,
							   'minimumInputLength'=>3,
                               'ajax'               => array(
                                   'url'      =>
$this->createUrl("poem/search"),
                                   'dataType' => 'json',
								   'type'=>'GET',
                                   'data'     => 'js:
function(term,page) {
                                                        return {
                                                            term: term,
															page: page,
                                                          page_limit: 5,
                                                        };
                                                  }',
                                   'results'=>'js:function
(data, page) {
           			 					var more = (page * 5) < data.total;          
            							return {results: data.result, more: more};							
						}',
                      ),
                              
'formatResult'=>'js:function(movie) {
			                   var folder =
"'.Yii::app()->request->baseUrl.'/images/";   
            var markup = "<table
class=movie-result><tr>";
			if (movie.pix !== undefined) {
			markup += "<td class=movie-image><img class=image-size
src="+folder+movie.pix +" /></td>";
		}else
			{
				markup += "<td class=movie-image><img class=image-size
src="+folder+"avater.png" + " /></td>";
			}
			
			markup += "<td class=movie-info><div
class=movie-title>" + movie.text + "</div><div
class=author-name>by "+movie.name+"</div>";
            markup += "</td></tr></table>"
            return markup; 
        						}',
                              
'formatSelection'=>'js:function(data) {                       
           
								top.location =
"'.$this->createUrl("poem/view").'"+"/"+data.id
									
									return data.text;
                                }',
								'dropdownCssClass'=>"bigdrop", // apply css that
makes the dropdown taller
								'escapeMarkup'=>'js:function (m) { return m; }',
								'formatNoMatches'=>'js:function () { return "No
category match"; }',		 
                           ),
                      ));			
?>		
</form>
~~~

I customized the look and feel with a little css to suite my taste. It's
not bad to show you


~~~
[php]
<style type="text/css">
	.image-size{
		height:50px;
		width:50px;
		float:left;
	}
	.movie-title{
		text-align:left;
		float:left;
		font-size:14px;
	}
	.author-name{
		font-style:italic;
		clear:both;
		font-size:12px;
	}
	.movie-info{
		padding-left:0;
	}
	.movie-result{margin-bottom:0;}
	.movie-image{
		width:55px;
	}
</style>
~~~

The page_limit is 5. There should have been a way of dynamically including it in
the search query in the action but I'm trying to avoid sql injection. It
needs some param binding. I welcome any suggestion on  that. Clicking any
highlighted selection would take you to a view of it ( e.g /poem/view/89)