jqgrid pager not showing data

Question

I have a jqGrid that does not use the loadonce: true option. The server handles the sorting dynamically as each query is sent. Only thing is that each page shows the same data. When I click on a column header, the data is sorted appropriately, but I cannot get the different pages to show.

Here's the function that handles the sorting. It's basically the same as http://www.trirand.com/jqgridwiki/doku.php?id=wiki:first_grid&s[]=sidx except with postgres instead of mysql

function popGrid($conn){
    $page = $_POST['page'];
    $limit = $_POST['rows'];
    $sidx = $_POST['sidx'];
    $sord = $_POST['sord'];
    $sqlCount = $_POST['sqlCount'];
    $sqlSelect = $_POST['sqlSelect'];
    $sqlSelect2 = $_POST['sqlSelect2'];
    $sqlSelect3 = $_POST['sqlSelect3'];
    $label1 = $_POST['label1'];
    $label2 = $_POST['label2'];
    $lbl1 = $_POST['lbl1'];
    $lbl2 = $_POST['lbl2'];
    $lbl3 = $_POST['lbl3'];

    if(!$sidx){ $sidx = 1; }

    $query = pg_query($conn,$sqlCount);
    $row = pg_fetch_row($query);
    $count = $row[0];

    if($count > 0 && $limit > 0){ $total_pages = ceil($count/$limit); }
    else{ $total_pages = 0; }

    if($page > $total_pages){ $pages = $total_pages; }

    $start = $limit*page - $limit;

    if($start < 0){ $start = 0; }

    header("Content-type: text/xml;charset=utf-8");

    $s = "<?xml version='1.0' encoding='utf-8'?>
        <rows>
        <page>".$page."</page>
        <total>".$total_pages."</total>
        <records>".$count."</records>";

    $sqlSelect .= " ORDER BY $sidx $sord LIMIT $limit OFFSET $start ";
    $query = pg_query($conn,$sqlSelect);
    while($row = pg_fetch_row($query)){
        $s .= "<row id='".$row[0]."'>";
        if($lbl1){  $s .= "<cell>$lbl1</cell>"; }
        $s .=   "<cell>".$row[0]."</cell>
                <cell>".$row[1]."</cell>";
        if($label1){ $s.= "<cell>$label1</cell>"; } 
        $s .= "<cell>".$row[2]."</cell>
                <cell>".$row[3]."</cell>
                <cell>".$row[4]."</cell>
                <cell>".$row[5]."</cell>
                <cell>".$row[6]."</cell>
                <cell>".$row[7]."</cell>
                <cell>".$row[8]."</cell>
                <cell>".$row[9]."</cell>
                <cell>".$row[10]."</cell>
                <cell>".$row[11]."</cell>
                <cell>".$row[12]."</cell>
                <cell>".$row[13]."</cell>
                <cell>".$row[14]."</cell>
                <cell>".$row[15]."</cell>
                </row>";
    }
    if($sqlSelect2){
        $sqlSelect2 .= " ORDER BY $sidx $sord LIMIT $limit OFFSET $start ";
        $query = pg_query($conn,$sqlSelect2);
        while($row = pg_fetch_row($query)){
            $s .= "<row id='".$row[0]."'>";
            if($lbl2){ $s .= "<cell>$lbl2</cell>"; }
            $s .= " <cell>".$row[0]."</cell>
                    <cell>".$row[1]."</cell>";
            if($label2){ $s .= "<cell>$label2</cell>"; }
            $s .=   "<cell>".$row[2]."</cell>
                    <cell>".$row[3]."</cell>
                    <cell>".$row[4]."</cell>
                    <cell>".$row[5]."</cell>
                    <cell>".$row[6]."</cell>
                    <cell>".$row[7]."</cell>
                    <cell>".$row[8]."</cell>
                    <cell>".$row[9]."</cell>
                    </row>";
        }
    }
    if($sqlSelect3){
        $sqlSelect3 .= " ORDER BY $sidx $sord LIMIT $limit OFFSET $start ";
        $query = pg_query($conn,$sqlSelect3);
        while($row = pg_fetch_row($query)){
            $s .= "<row id='".$row[0]."'>";
            if($lbl3){ $s .= "<cell>$lbl3</cell>"; }
            $s .= " <cell>".$row[0]."</cell>
                    <cell>".$row[1]."</cell>";
            if($label2){ $s .= "<cell>$label2</cell>"; }
            $s .=   "<cell>".$row[2]."</cell>
                    <cell>".$row[3]."</cell>
                    <cell>".$row[4]."</cell>
                    <cell>".$row[5]."</cell>
                    <cell>".$row[6]."</cell>
                    <cell>".$row[7]."</cell>
                    </row>";            
        }
    }   
    $s .= "</rows>";
    echo $s;

}

And here's the grid, in case that's the problem:

$('#lst_users').jqGrid({
    url: 'hours_func.php',
    dataType: 'xml',
    mtype: 'POST',
    postData: {
        action: 'popGrid',
        sqlCount: sqlCount,
        sqlSelect: sqlSelect
    },
    colNames:['ID','LOGIN ID','PASSWORD','FIRST NAME','LAST NAME','EMAIL','ADMIN'],
    colModel:[
        {   name: 'id',
            required: false,
            editable: false,
        },
        {   name: 'login_id',
            editoptions: {
                maxlength: 4
            }
        },
        {   name: 'password',
            editoptions: {
                maxlength: 30
            }
        },
        {   name: 'fname',
            editoptions: {  
                maxlength: 20
            }
        },
        {   name: 'lname',
            editoptions: {
                maxlength: 30
            }
        },
        {   name: 'email',
            required: false,
            editoptions:{
                maxlength: 30
            }
        },
        {   name: 'admin',
            editoptions: {
                dataInit: function(elem){
                    $(elem).mask("~");
                }
            }
        },      
    ],
    cmTemplate:{ 
        align: 'center',
        colwidth: 80,
        editable: true,
        required: true,
    },
    caption: 'Users',
    pager:'#pgr_users',
    editurl: 'hours_func.php',
    loadonce: false,
}).navGrid('#pgr_users',
    //include all buttons
    {   search: false,
    },  
    //edit options
    {   bSubmit: "Edit",
        width: 350,
        recreateForm: true,
        recreateFilter: true,
        closeOnEscape: true,
        editData: { 
            action: 'grdUsers',
            id: function(){
                var id = $('#lst_users').jqGrid('getGridParam','selrow');
                var val = $('#lst_users').jqGrid('getCell',id,'id');
                return val;
            }
        },
    },  
    //add options
    {   bSubmit: "Add",
        width: 350,
        recreateForm: true,
        recreateFilter: false,
        closeOnEscape: true,
        editData: { action: 'grdUsers' }
    },  
    //delete options
    {   recreateForm: false,
        recreateFilter: false,
        closeOnEscape: true,
        delData: { 
            action: 'grdUsers',
            id: function() {
                var sel_id = $('#lst_users').jqGrid('getGridParam','selrow');
                var value = $('#lst_users').jqGrid('getCell', sel_id, 'id');
                return value;
            }
        }
    },
    //search options
    {},
    //view options
    {}
);

Answer 1

The way you are setup the paging will be handled on the server side. If you use Firebug or somthing similar you can see that the jqGrid will send out information to your controller allowing you to filter down to the correct page. Your controller is already handling the sorting of the data, so now you just need to utilize the paging information to grab the correct page of data.

The code below is for C# but it will serve as an example:

Your Controller will receive the following information from the post of the jqGrid on the page request

GetGridData(string sidx, string sord, int page, int rows, bool _search, string filters)
{
 ....

You can then use this information to filter which page of data that the jqGrid is requesting as I do in this example.

...
var pagedQuery = DataSet.OrderBy(sidx + " " + sord).Skip((page - 1) * rows).Take(rows).ToList();
...