jqGrid - Server side paging sorting implementation with PHP, SQL Server

Recently, I faced to load a result set with around 5 thousand records, and found that the overall performance could not be accepted.

Therefore, server side paging is the way to go, and I found that there are only a few examples on the net to demonstrate how to do it.

So, I hope my simple example here can help you if you're also looking for it.

javascript :

var mygrid01 = $('#mygrid1');
    mygrid01.jqGrid({
        url:'php/loaddata.php',
        datatype: "json",        
        mtype: 'GET',
        colNames:[ 
            'customer_id','name','address'
        ],
        colModel:[    
            {name:'customer_id',index:'customer_id'},
            {name:'name',index:'name'},
            {name:'address',index:'address'}
        ],
        rowNum:50,
        rowList: [50,100,500],
        pager: '#pager1',    
        rownumbers: true,
        viewrecords: true,
        loadComplete: function() {
        }
    });

loaddata.php:

$limit=$_GET['rows']; 
$page=$_GET['page'];
$sidx=$_GET['sidx'];
$sord=$_GET['sord'];
...//your script of connection to db

$sidx = ($sidx=='')? 'customer_id':$sidx; //set default order if empty
$sord = ($sord=='')? 'asc':$sord;

$tsql = "select count(*) as count from customer";
$stmt = sqlsrv_query($conn, $tsql);
$row = sqlsrv_fetch_array($stmt);
$count = $row['count'];

$total_pages=ceil($count/$limit);
if ($page > $total_pages) { $page=$total_pages; }

$start = $limit*$page - $limit;
$end = $page * $limit;

$tsql = "WITH PAGED_CUSTOMERS  AS
    ( SELECT  customer_id, name, address,
     ROW_NUMBER() OVER (ORDER BY ".$sidx." ".$sord.") AS RowNumber FROM customer )

    SELECT customer_id, name, address  FROM PAGED_CUSTOMERS
   WHERE RowNumber BETWEEN ".$start." AND ".$end;

$stmt = sqlsrv_query($conn, $tsql);

$response = new stdClass();
$response->page = $page;
$response->total = $total_pages;
$response->records = $count;

$l_rowcount=0;
while ($row = sqlsrv_fetch_array($stmt)){ 
$response->rows[$l_rowcount]['id']=$l_rowcount+1; //set id starting from 1 instead of 0, and it's up to your design
    $response->rows[$l_rowcount]['cell']=array(
        $row['customer_id'],
        $row['name'],
        $row['address']
    );
    $l_rowcount++;               
}

echo json_encode($response);