Select2 Pagination节目正在加载更多结果......

Question

When i search for usernamed "a" it fetches only first 5 username and displays "Loading more results…" but its not loading, when i debugged it, the page parameter is not incremented it just stays 1 where as the term parameter is working correctly. Below is my code:

function getCommonAutoComplete(urlStr) {
  $(".item-dropdown").select2({
    placeholder: "[Select Item]",
    allowClear: true,
    ajax: {
      url: urlStr,
      dataType: "json",
      delay: 250,
      casesensitive: false,
      data: function(params) {
        return {
          q: params.term, // search term
          page: params.page || 1
        };
      },
      processResults: function(data, params) {
        var resData = [];
        data.forEach(function(value) {
          resData.push(value);
        });
        var page = params.page || 1;
        return {
          results: $.map(resData, function(item) {
            return {
              text: "[" + item.item_code + "] " + item.ItemName,
              id: item.id
            };
          }),
          pagination: {
            more: page * 5 <= data[0].total_count
          }
        };
      },
      cache: true
    },
    minimumInputLength: 1
  });
}

Below is my server side code where this is controller

public function actionSearchItem(){
  $string=$_GET['q'];
  $page= $_GET['page'];
  $data=$this->fin->getItemAjax($string,$page);
  echo CJSON::encode($data);exit;
}

This is my model

public function getItemAjax($string,$page){
    $resultCount = 5;
    $end = ($page - 1) * $resultCount;       
    $start = $end + $resultCount;
    $sql="SELECT item_name as ItemName,item_code,id from wp_item where item_name like '%$string%' LIMIT {$end},{$start}";
    $result = Yii::app()->db->createCommand($sql)->queryAll();
    foreach ($result as $itemKey => $ajaxValue) {
        $data[] = ['id'=>$ajaxValue['id'], 'ItemName'=>$ajaxValue['ItemName'],'item_code'=>$ajaxValue['item_code'], 'total_count'=>count($result)];
    }
    return $data;
}

And here is my Json data

[
  { id: "2", name: "Tracy Moen DVM", total_count: 5 },
  { id: "3", name: "Miss Zena Swift Jr.", total_count: 5 },
  { id: "4", name: "Gail Kunde", total_count: 5 },
  { id: "5", name: "Edna Langworth", total_count: 5 },
  { id: "7", name: "Meta Weimann", total_count: 5 }
];

Screeshot of the issue is below:

Answer 1

You're handling pagination in a wrong way - this is not "start" and "end", but "limit" and "offset". Also total_count is calculated in a wrong way and you're query is vulnerable to SQL Injection. You should try something like this:

public function getItemAjax($string, $page) {
    $resultCount = 5;
    $offset = ($page - 1) * $resultCount;       
    $limit = $resultCount;

    $sql = "SELECT item_name as ItemName, item_code, id from wp_item where item_name like '%:string%' LIMIT {$offset}, {$limit}";
    $result = Yii::app()->db->createCommand($sql)->queryAll(true, ['string' => $string]);

    $countSql = "SELECT count(*) from wp_item where item_name like '%:string%'"
    $totalCount = Yii::app()->db->createCommand($countSql)->queryScalar(['string' => $string]);

    foreach ($result as $itemKey => $ajaxValue) {
        $data[] = [
            'id' => $ajaxValue['id'], 
            'ItemName' => $ajaxValue['ItemName'],
            'item_code' => $ajaxValue['item_code'], 
            'total_count' => $totalCount,
        ];
    }
    return $data;
}