Paginate result using select 2 ajax remote data

Question

Trying to paginate ajax data using Select2 (v 4.0.6.rc1) so that the user can find more results if not present in the first limit ,using the following but not retrieving data.I 'll appreciate if someone helps me out,there is not much of examples about pagination.

i was trying to paginate data using the help of this question Select2 v4 how to paginate results using AJAX its not seems to be working,getting the array but not correct format.

JS:

  $('#compose_username').select2({
        dropdownParent: $("#modal-compose") ,
        placeholder: "Search Username...",
        minimumInputLength: 1,
        ajax: {
            url: "username.php",
            dataType: 'json',
            delay: 250,
            cache: false,
            data: function (params) {
                return {
                    term: params.term,
                    page: params.page || 1                      
                    //a: params.term, // search term
                    //psf: params.page
                };
            },
            processResults: function(data) {
                console.log(data);
                var result = $.map(data, function (item) { return { id: item.id, text: item.username }});
                return { results: result };
            }
        }
  });

PHP

    try{
         $page= $_GET['page'];
        // $resultCount = 10;
        // $offset = ($page - 1) * $resultCount;
  $pageLength = 20;
  $pageStart = ($page - 1) * $pageLength;
  $pageEnd = $pageStart + $pageLength;  

        $stmt = $db_con->query("SELECT id,first_name FROM datatables_demo WHERE first_name LIKE '%".$_GET['term']."%' LIMIT {$pageStart},{$pageEnd}");
        $count = $db_con->query("SELECT count(first_name) FROM datatables_demo WHERE first_name LIKE '%".$_GET['term']."%' LIMIT {$pageStart},{$pageEnd}")->fetchColumn();;
        $stmt->execute();
            $json = [];
            while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
                 $json[] = ['id'=>$row['id'], 'username'=>$row['first_name']];
            }
    $endCount = $pageStart + $pageLength;
    $morePages = $endCount > $count;            
$results = array(
  "results" => $json,
  "pagination" => array(
    "more" => $morePages
  )
);          
            echo json_encode($results);
    }
    catch(PDOException $e){
        echo $e->getMessage();
    }

what is `page: params.page || 2` supposed to do? – Bobby Axe Aug 31 '18 at 00:25 — Bobby Axe, Aug 31 '18 at 00:25
i think its the page number – MR_AMDEV Aug 31 '18 at 05:48 — MR_AMDEV, Aug 31 '18 at 05:48

score 6 · Accepted Answer · edited Sep 27 '21 at 15:17

Found not much examples about select2 paginate, had to figure it out on my own and here is the full working of how to paginate data(infinite scroll) using Select2: Hope this helps someone else.

JS:

$('#compose_username').select2({
    dropdownParent: $("#modal-compose") ,
    placeholder: "Search Username...",
    minimumInputLength: 1,
    allowClear: true,
    ajax: {
        url: "username.php",
        dataType: 'json',
        delay: 250,
        cache: false,
        data: function (params) {
            return {
                term: params.term,
                page: params.page || 1,
            };
        },
        processResults: function(data, params) {
            //console.log(data);
            //  NO NEED TO PARSE DATA `processResults` automatically parse it
            //var c = JSON.parse(data);
            console.log(data);
            var page = params.page || 1;
            return {
                results: $.map(data, function (item) { return {id: item.col, text: item.col}}),
                pagination: {
                // THE `10` SHOULD BE SAME AS `$resultCount FROM PHP, it is the number of records to fetch from table` 
                    more: (page * 10) <= data[0].total_count
                }
            };
        },              
    }
});

PHP(USING PDO):

try{
    $page= $_GET['page'];
    $resultCount = 10;
    $end = ($page - 1) * $resultCount;       
    $start = $end + $resultCount;

    $stmt = $db_con->query("SELECT col,col FROM table WHERE col LIKE '".$_GET['term']."%' LIMIT {$end},{$start}");
    $stmt->execute();
    $count = $stmt->rowCount();
    $data = [];
    while($row = $stmt->fetch(PDO::FETCH_ASSOC))
        $data[] = ['id'=>$row['id'], 'col'=>$row['col'],'total_count'=>$count];
        
    // IF SEARCH TERM IS NOT FOUND DATA WILL BE EMPTY SO
    if (empty($data)){
        $empty[] = ['id'=>'', 'col'=>'', 'total_count'=>'']; 
        echo json_encode($empty);
    }

    else 
        echo json_encode($data);
        
}
catch(PDOException $e){
    echo $e->getMessage();
}

Paginate result using select 2 ajax remote data

1 Answers1