The error of updating grid record after it was filtered

[africanfarmerMember]

by using the update code with php in demo, found errors as below,

1/ if the grid is not filtered, it is correct to update one row of grid.

2/ when it is filtered, two errors shown,

a/ it generates a new row with the updated content in grid(same in database). the one before updates still exists.

b/ the filter cannot be cleared anymore even if click on clear button. the filter tool on the colume filtered is only shown the filtered name i/o all names (check items), even if i choose ‘select all’.

no pic upload tool in this forms otherwise i can describle the problems more clearly.

[Peter StoevKeymaster]

Hi africanfarmer,

if you experience an issue, then please provide an example which illustrates it(JS + HTML page which we will be able to run locally) and provide information about the jQWidgets version that you use.

Best Regards,
Peter Stoev

jQWidgets Team
http://www.jqwidgets.com/

[africanfarmerMember]

Hi Big Boss,

tks and pls refer to below,

file link.

if you can not download them, i will paste the codes here, but all html tags disappered.

[africanfarmerMember]

i used the up-to-date version of jQWidgets. jQWidgets v2.9.3 Release, July-11-2013

[africanfarmerMember]

Hi Peter,
seems you will hard to download file in chinese net hard drive. pls refer to below code
—js—

$(document).ready(function () {
    var theme = "custom";
    var source =
    {
        datatype: "json",
        datafields: [
            { name: 'id', type: 'number'},
            { name: 'date', type: 'date'},
            { name: 'agentname', type: 'string'},
            { name: 'secondagent', type: 'string'},
            { name: 'blno', type: 'string'},
            { name: 'vlm', type: 'number'},
            { name: 'cntrtype', type: 'string'},
            { name: 'sellingrate', type: 'number'},
            { name: 'cost', type: 'number'},
            { name: 'averageprofit', type: 'number'},
            { name: 'totalprofit', type: 'number'}
        ],
        cache: false,
        id: 'id',
        url: 'data.php',
/*        filter: function () {
            // update the grid and send a request to the server.
            $("#jqxgrid").jqxGrid('updatebounddata', 'filter');
        },*/
        updaterow: function (rowid, rowdata, commit) {
            // synchronize with the server - send update command
            var data = "update=true&date=" + rowdata.date + "&agentname=" + rowdata.agentname + "&secondagent="
                + rowdata.secondagent + "&blno=" + rowdata.blno + "&vlm=" + rowdata.vlm + "&cntrtype=" + rowdata.cntrtype
                + "&sellingrate=" + rowdata.sellingrate + "&cost=" + rowdata.cost + "&averageprofit=" + rowdata.averageprofit
                + "&totalprofit=" + rowdata.totalprofit;
            data = data + "& id=" + rowid;
            $.ajax({
                dataType: 'json',
                url: 'data.php',
                type: 'POST',
                data: data,
                success: function (data, status, xhr) {
                    // update command is executed.
                    commit(true);
                    $("#jqxgrid").jqxGrid('updatebounddata', 'cells');
                }
            });
        },
        //delete record
        deleterow: function (rowid, commit) {
            var data = "delete=true";
            data = data + "&id=" + rowid;
            $.ajax({
                dataType: 'json',
                url: 'data.php',
                type: 'POST',
                data: data,
                success: function (data, status, xhr) {
                    commit(true);
                }
            });
        },
        // insert row
        addrow: function (rowid, rowdata, position, commit) {
            var data = "insert=true&date=" + rowdata.date + "&agentname=" + rowdata.agentname + "&secondagent="
                + rowdata.secondagent + "&blno=" + rowdata.blno + "&vlm=" + rowdata.vlm + "&cntrtype=" + rowdata.cntrtype
                + "&sellingrate=" + rowdata.sellingrate + "&cost=" + rowdata.cost + "&averageprofit=" + rowdata.averageprofit
                + "&totalprofit=" + rowdata.totalprofit;
            $.ajax({
                dataType: 'json',
                url: 'data.php',
                type: 'POST',
                data: data,
                success: function (data, status, xhr) {
                    commit(true);
                    $("#jqxgrid").jqxGrid('updatebounddata', 'rows');
                }
            });
        }
};
    //var dataAdapter = new $.jqx.dataAdapter(source);
    // initialize the input fields.
    var cntrtypesource =[
        "20GP",
        "40GP",
        "40HQ",
        "45HQ",
        "40RH",
        "20RH",
        "20OT",
        "40OT",
        "20FR",
        "40FR"
    ];
    var agentnamesource = {
        datatype: "json",
        datafields:[
            {name:'id', type:'number'},
            {name: 'agentname', type:'string'},
        ],
        id: 'id',
        url: 'agentnamelist.php',
        async: false,
        cache:false,
        addrow: function (rowid, rowdata, position, commit) {
            var data = "insert=true& agentname=" + rowdata.agentname;
            $.ajax({
                dataType: 'json',
                url: 'agentnamelist.php',
                type: 'POST',
                data: data,
                success: function (data, status, xhr) {
                    // update command is executed.
                    commit(true);
                    $("#agentjqxgrid").jqxGrid('updatebounddata', 'cells');
                }
            });
        },
        updaterow: function (rowid, rowdata, commit) {
        // synchronize with the server - send update command
        var data = "update=true& agentname=" + rowdata.agentname;
        data = data + "& id=" + rowid;
        $.ajax({
            dataType: 'json',
            url: 'agentnamelist.php',
            type: 'POST',
            data: data,
            success: function (data, status, xhr) {
                // update command is executed.
                commit(true);
                $("#agentjqxgrid").jqxGrid('updatebounddata', 'cells');
            }
        });
    },
    //delete record
         deleterow: function (rowid, commit) {
        var data = "delete=true";
        data = data + "& id=" + rowid;
        $.ajax({
            dataType: 'json',
            url: 'agentnamelist.php',
            type: 'POST',
            data: data,
            success: function (data, status, xhr) {
                commit(true);
            }
        });
    }
    };
    var an_dataAdapter = new $.jqx.dataAdapter(agentnamesource);
    // Create a jqxDateTimeInput
    $("#date, #cdate, #startdate, #enddate").jqxDateTimeInput({width: '150px', height: '23px', theme: theme,formatString: 'yyyy-MM-dd'  });
    //$("#date").jqxInput({width: 150, height: 23});
    //$("#agentname").jqxInput({width: 150, height: 23});
    $("#agentname, #cagentname, #sagentname").jqxDropDownList({
        source: an_dataAdapter,
        theme:theme,
        width:150,
        height:23,
        selectedIndex: 0,
        displayMember: 'agentname',
        valueMember: 'agentname'
    });
    $("#secondagent, #csecondagent").jqxInput({width: 150, height: 23,theme: theme});
    $("#blno, #cblno").jqxInput({width: 150, height: 23,theme: theme});
    $("#vlm, #cvlm").jqxNumberInput({width: 150, height: 23, decimalDigits: 0, spinButtons: true,theme: theme});
    $("#cntrtype, #ccntrtype").jqxDropDownList({ source: cntrtypesource, selectedIndex: 1, width: '150', height: '23', theme: theme });
    //$("#cntrtype").jqxInput({width: 150, height: 23});
    $("#sellingrate, #csellingrate").jqxNumberInput({symbol: '$', width: 150, height: 23, spinButtons: true,theme: theme});
    $("#cost, #ccost").jqxNumberInput({symbol: '$', width: 150, height: 23,  spinButtons: true,theme: theme});
    $("#averageprofit, #caverageprofit").jqxNumberInput({symbol: '$', width: 150, height: 23, spinButtons: true,theme: theme});
    $("#totalprofit, #ctotalprofit").jqxNumberInput({symbol: '$', width: 150, height: 23, spinButtons: true,theme: theme});
    var dataAdapter = new $.jqx.dataAdapter(source);
    var editrow = -1;
    // initialize jqxGrid
    $("#jqxgrid").jqxGrid(
        {
            width: 1150,
            source: dataAdapter,
            theme: theme,
            showfilterrow: true,
            filterable: true,
            autoheight: true,
            pageable: true,
            showstatusbar: true,
            statusbarheight: 30,
            //altrows: true,
            showaggregates: true,
            columnsresize: true,
            columns: [
                { text: 'ID', editable: false, datafield: 'id', width: 50 },
                { text: '日期', datafield: 'date', cellsformat: 'yyyy-MM-dd', width: 100,filtertype: 'date' },
                { text: '代理名称', datafield: 'agentname', width: 100,filtertype: 'checkedlist'},
                { text: '二代名称', datafield: 'secondagent', width: 100,filtertype: 'textbox' },
                { text: '提单号码', datafield: 'blno', width: 120, filtertype: 'textbox', filtercondition: 'starts_with',
                    aggregates: [
                        { '<b>票数</b>':
                            function (aggregatedValue, currentValue) {
                                if (currentValue ) {
                                    return aggregatedValue + 1;
                                }
                                return aggregatedValue;
                            }
                        }
                    ]},
                { text: '箱量', datafield: 'vlm', width: 50, cellsformat:'n2',cellsalign: 'right',filtertype: 'number'},
                { text: '柜型', datafield: 'cntrtype', width: 70 ,filtertype: 'checkedlist'},
                { text: '卖价', datafield: 'sellingrate', width: 100, cellsformat: 'c2',cellsalign: 'right',filtertype: 'number'},
                { text: '底价', datafield: 'cost', width: 100,  cellsalign: 'right',cellsformat: 'c2' ,filtertype: 'number'},
                { text: '平均利润', datafield: 'averageprofit', width: 100, cellsalign: 'right', cellsformat: 'c2',filtertype: 'number'},
                { text: '总利润', datafield: 'totalprofit', width: 150, cellsformat: 'c2',cellsalign: 'right',filtertype: 'number',aggregates: [{ '<b>总计</b>':
                    function (aggregatedValue, currentValue, column, record) {
                        var total = currentValue;
                        return aggregatedValue + total;
                    }
                }]
                },
  /*              { text: '修改', datafield: 'Edit', columntype: 'button', cellsrenderer: function () {
                    return "修改";
                }, buttonclick: function (row) {
                    // open the popup window when the user clicks a button.
                    editrow = row;
                    var offset = $("#jqxgrid").offset();
                    $("#popupWindow").jqxWindow({ position: { x: parseInt(offset.left) + 460, y: parseInt(offset.top) + 60 } });
                    // get the clicked row's data and initialize the input fields.
                    var dataRecord = $("#jqxgrid").jqxGrid('getrowdata', editrow);
                    $("#date").val(dataRecord.date);
                    $("#agentname").val(dataRecord.agentname);
                    $("#secondagent").val(dataRecord.secondagent);
                    $("#blno").val(dataRecord.blno);
                    $("#vlm").jqxNumberInput({decimal: dataRecord.vlm});
                    $("#cntrtype").val(dataRecord.cntrtype);
                    $("#sellingrate").jqxNumberInput({decimal: dataRecord.sellingrate});
                    $("#cost").jqxNumberInput({decimal: dataRecord.cost});
                    $("#averageprofit").jqxNumberInput({decimal: dataRecord.averageprofit});
                    $("#totalprofit").jqxNumberInput({decimal: dataRecord.totalprofit});
                    // show the popup window.
                    $("#popupWindow").jqxWindow('open');
                }
                },*/
                { text: '删除', datafield: 'Delete', columntype: 'button', cellsrenderer: function(){
                    return '删除';
                }, buttonclick: function (row){
                    delrow = row;
                    var offset = $("#jqxgrid").offset();
                    $("#cfmWindow").jqxWindow({ position: { x: parseInt(offset.left) + 460, y: parseInt(offset.top) + 60 } });
                    $("#cfmWindow").jqxWindow('open');
                }}
            ]
        });
    $("#agentjqxgrid").jqxGrid(
        {
            width: 400,
            source: an_dataAdapter,
            theme: theme,
            autoheight: true,
            pageable: true,
            showstatusbar: true,
            statusbarheight: 30,
            showaggregates: true,
            editable: true,
            columns: [
                { text: 'ID', editable: false, datafield: 'id', width: 80 },
                { text: '代理名称', datafield: 'agentname', width: 320 ,
                    aggregates: [
                        { '<b>代理数</b>':
                            function (aggregatedValue, currentValue) {
                                if (currentValue ) {
                                    return aggregatedValue + 1;
                                }
                                return aggregatedValue;
                            }
                        }
                    ]}
            ]
        }
    );
    // initialize the popup window and buttons.
    $("#popupWindow").jqxWindow({
        width: 280, resizable: false, isModal: true, autoOpen: false, cancelButton: $("#Cancel"), isModal:true, modalOpacity: 0.3,theme: theme
    });
    $("#popupWindow").on('open', function () {
        $("#date").jqxInput('selectAll');
    });
    $("#cfmWindow").jqxWindow({
        width: 280, height: 120, resizable: false, isModal: true, autoOpen: false, cancelButton: $("#Cancel2"), isModal:true,modalOpacity: 0.3,theme: theme
    });
    $("#createWindow").jqxWindow({
        width: 280, resizable: false, isModal: true, autoOpen: false, cancelButton: $("#cCancel"), isModal:true,modalOpacity: 0.3,theme: theme
    });
    $("#agentWindow").jqxWindow({
        width: 450, resizable: false, isModal: true, autoOpen: false, cancelButton: $("#aCancel"), isModal:true,modalOpacity: 0.3,theme: theme
    });
    $("#Cancel").jqxButton({theme: theme});
    $("#Save").jqxButton({theme: theme});
    $("#ok").jqxButton({theme: theme});
    $("#Cancel2").jqxButton({theme: theme});
    $("#cSave").jqxButton({theme: theme});
    $("#cCancel").jqxButton({theme: theme});
    $("#create, #modify, #export, #editagent, #aSave, #aCancel").jqxButton({theme: theme});
    $("#ag_toolbar&gt;button").jqxButton({theme: theme});
    $('#clearfilteringbutton').jqxButton({theme: theme});
    $('#logoutbtn').jqxButton({theme: theme});
    $('#ad_name').jqxInput({width: 150, height: 23, placeHolder: '请输入一个名称', theme:theme});
    $('#clearfilteringbutton').click(function () {
   $("#jqxgrid").jqxGrid('clearfilters');
    });
    //create record
    $("#create").click(function(){
        var offset = $("#jqxgrid").offset();
        $("#createWindow").jqxWindow({ position: { x: parseInt(offset.left) + 460, y: parseInt(offset.top) + 60 } });
        $("#cdate").val("");
        $("#cagentname").val("");
        $("#csecondagent").val("");
        $("#cblno").val("");
        $("#cvlm").jqxNumberInput({decimal: ""});
        $("#ccntrtype").val("");
        $("#csellingrate").jqxNumberInput({decimal: ""});
        $("#ccost").jqxNumberInput({decimal: ""});
        $("#caverageprofit").jqxNumberInput({decimal: ""});
        $("#ctotalprofit").jqxNumberInput({decimal: ""});
        $("#createWindow").jqxWindow('open');
    });
    $("#modify").click(function() {
       // $("#jqxgrid").jqxGrid('clearfilters');
        var selectedrowindex = $("#jqxgrid").jqxGrid('getselectedrowindex');
        var editrow = selectedrowindex;
       // var rowscount = $("#jqxgrid").jqxGrid('getdatainformation').rowscount;
            var dataRecord = $("#jqxgrid").jqxGrid('getrowdata', editrow);
            var offset = $("#jqxgrid").offset();
           $("#popupWindow").jqxWindow({ position: { x: parseInt(offset.left) + 460, y: parseInt(offset.top) + 60 } });
            // get the clicked row's data and initialize the input fields.
            $("#date").val(dataRecord.date);
            $("#agentname").val(dataRecord.agentname);
            $("#secondagent").val(dataRecord.secondagent);
            $("#blno").val(dataRecord.blno);
            $("#vlm").jqxNumberInput({decimal: dataRecord.vlm});
            $("#cntrtype").val(dataRecord.cntrtype);
            $("#sellingrate").jqxNumberInput({decimal: dataRecord.sellingrate});
            $("#cost").jqxNumberInput({decimal: dataRecord.cost});
            $("#averageprofit").jqxNumberInput({decimal: dataRecord.averageprofit});
            $("#totalprofit").jqxNumberInput({decimal: dataRecord.totalprofit});
            // show the popup window.
            $("#popupWindow").jqxWindow('open');
        });
    $("#export").click(function () {
        $('#jqxgrid').jqxGrid('exportdata', 'xls', 'jqxGrid', false);
    });
    $("#logoutbtn").click(function(){
        location.href = 'logout.php';
    })
    //当用户点击ok，执行删除程序
    $("#ok").click(function(){
        var rowID = $('#jqxgrid').jqxGrid('getrowid', delrow);
        $('#jqxgrid').jqxGrid('deleterow', rowID);
        $("#cfmWindow").jqxWindow('close');
    });
    // update the edited row when the user clicks the 'Save' button.
    $("#Save").click(function () {
/*
        if (editrow &gt;= 0) {
            var row = { date: $("#date").val(), agentname: $("#agentname").val(), secondagent: $("#secondagent").val(), blno: $("#blno").val(),
                vlm: $("#vlm").val(), cntrtype: $("#cntrtype").val(), sellingrate: $("#sellingrate").val(), cost: $("#cost").val(),
                averageprofit: averageprofit($("#sellingrate").val(),$("#cost").val()), totalprofit: totalprofit($("#vlm").val(),$("#sellingrate").val(),$("#cost").val())};
            var rowID = $("#jqxgrid").jqxGrid('getselectedrowindex');
            $('#jqxgrid').jqxGrid('updaterow', rowID, row);
            $("#popupWindow").jqxWindow('close');
        }*/
        var selectedrowindex = $("#jqxgrid").jqxGrid('getselectedrowindex');
        var rowscount = $("#jqxgrid").jqxGrid('getdatainformation').rowscount;
        var datarow = { date: $("#date").val(), agentname: $("#agentname").val(), secondagent: $("#secondagent").val(), blno: $("#blno").val(),
            vlm: $("#vlm").val(), cntrtype: $("#cntrtype").val(), sellingrate: $("#sellingrate").val(), cost: $("#cost").val(),
            averageprofit: averageprofit($("#sellingrate").val(),$("#cost").val()), totalprofit: totalprofit($("#vlm").val(),$("#sellingrate").val(),$("#cost").val())};
        if (selectedrowindex &gt;= 0 &amp;&amp; selectedrowindex = 0 &amp;&amp; selectedrowindex = 0 &amp;&amp; selectedrowindex &lt; rowscount) {
     var id = $(&quot;#agentjqxgrid&quot;).jqxGrid('getrowid', selectedrowindex);
     var commit = $(&quot;#agentjqxgrid&quot;).jqxGrid('deleterow', id);
     }
     });
});
//计算利润
function averageprofit($fsellingrate, $fcost) {
    $faverageprofit = ($fsellingrate-$fcost);
    return $faverageprofit;
}
function totalprofit($fvlm,$fsellingrate, $fcost){
    averageprofit($fsellingrate, $fcost);
    $ftotalprofit = $fvlm * $faverageprofit;
    return $ftotalprofit;
}

[africanfarmerMember]

--html--

<body class='default'>
<div id='jqxWidget'>
    <div style="width:1150px">
        <div> <input  style="margin: 5px;margin-top:20px;" type="button" id="create" value="新建"/>&nbsp;<input  style="margin: 5px;margin-top:20px;" type="button" id="modify" value="修改"/>&nbsp;<input  style="margin: 5px;margin-top:20px;" type="button" id="export" value="导出到Excel"/>&nbsp;<input  style="margin: 5px;margin-top:20px;" type="button" id="editagent" value="管理代理"/>&nbsp; <input style="margin: 5px;margin-top:20px;" value="移除筛选" id="clearfilteringbutton" type="button" />
</body> <input  style="float:right;position:relative;margin: 5px;margin-top:20px;" type="button" id="logoutbtn" value="退出"/></div>
</div>
    <div id="jqxgrid"></div>
    <div style="margin-top: 30px;">
        <div id="cellbegineditevent"></div>
        <div style="margin-top: 10px;" id="cellendeditevent"></div>
    </div>
    <div id="popupWindow">
        <div>编辑</div>
        <div style="overflow: hidden;">
            <table>
                <tr>
                    <td align="right">日期:</td>
                    <td align="left"><div id="date"></div></td>
                </tr>
                <tr>
                    <td align="right">代理名称:</td>
                    <td align="left"><div style="float: left;" name="agentname" id="agentname"></div></td>
                </tr>
                <tr>
                    <td align="right">二代名称:</td>
                    <td align="left"><input id="secondagent"/></td>
                </tr>
                <tr>
                    <td align="right">提单号:</td>
                    <td align="left"><input id="blno"/></td>
                </tr>
                <tr>
                    <td align="right">箱量:</td>
                    <td align="left"><div id="vlm"></div></td>
                </tr>
                <tr>
                    <td align="right">柜型:</td>
                    <td align="left">
                        <div id="cntrtype"></div>
                    </td>
                </tr>
                <tr>
                    <td align="right">卖价:</td>
                    <td align="left"><div id="sellingrate"></div></td>
                </tr>
                <tr>
                    <td align="right">底价:</td>
                    <td align="left"><div id="cost"></div></td>
                </tr>
                <tr>
                    <td align="right">平均利润:</td>
                    <td align="left"><div id="averageprofit"></div></td>
                </tr>
                <tr>
                    <td align="right">总利润:</td>
                    <td align="left"><div id="totalprofit" ></div></td>
                </tr>
                <tr>
                    <td align="right"></td>
                    <td style="padding-top: 10px;" align="right"><input style="margin-right: 5px;" type="button" id="Save" value="保存" /><input id="Cancel" type="button" value="取消" /></td>
                </tr>
            </table>
        </div>
    </div>
    <div id="cfmWindow">
        <div>
            提醒~</div>
        <div>
            <div style="margin-left: 20px;margin-top: 20px">
                确认删除？
            </div>
            <div>
                <div style="float: right; margin-top: 15px;">
                    <input type="button" id="ok" value="OK" style="margin-right: 10px" />
                    <input type="button" id="Cancel2" value="取消" />
                </div>
            </div>
        </div>
    </div>
    <div id="createWindow">
        <div>新建</div>
        <div style="overflow: hidden;">
            <table>
                <tr>
                    <td align="right">日期:</td>
                    <td align="left"><div id="cdate"></div></td>
                </tr>
                <tr>
                    <td align="right">代理名称:</td>
                    <td align="left"><div style="float: left;" name="cagentname" id="cagentname"></div></td>
                </tr>
                <tr>
                    <td align="right">二代名称:</td>
                    <td align="left"><input id="csecondagent"/></td>
                </tr>
                <tr>
                    <td align="right">提单号:</td>
                    <td align="left"><input id="cblno"/></td>
                </tr>
                <tr>
                    <td align="right">箱量:</td>
                    <td align="left"><div id="cvlm"></div></td>
                </tr>
                <tr>
                    <td align="right">柜型:</td>
                    <td align="left">
                        <div id="ccntrtype"></div>
                    </td>
                </tr>
                <tr>
                    <td align="right">卖价:</td>
                    <td align="left"><div id="csellingrate"></div></td>
                </tr>
                <tr>
                    <td align="right">底价:</td>
                    <td align="left"><div id="ccost"></div></td>
                </tr>
                <tr>
                    <td align="right">平均利润:</td>
                    <td align="left"><div id="caverageprofit"></div></td>
                </tr>
                <tr>
                    <td align="right">总利润:</td>
                    <td align="left"><div id="ctotalprofit"></div></td>
                </tr>
                <tr>
                    <td align="right"></td>
                    <td style="padding-top: 10px;" align="right"><input style="margin-right: 5px;" type="button" id="cSave" value="保存" /><input id="cCancel" type="button" value="取消" /></td>
                </tr>
            </table>
        </div>
    </div>
</div>
<div id="agentWindow">
    <div>代理管理</div>
    <div style="overflow: hidden;">
        <div id="ag_toolbar" style="margin:5px;">
            <input id="ad_name" type="text"/>
            <button style="padding:4px 16px;" id="ag_new">
                新建</button>
            <button style="padding:4px 16px;" id="ag_delete">删除</button>
            <button style="padding:4px 16px;" id="ag_modify">
                保存</button>
        </div>
       <div  id="agentjqxgrid"></div>
    </div>
</div>
</body>

[africanfarmerMember]

–data.php–

<?php
#Include the connect.php file
include('connect.php');
#Connect to the database
//connection String
$connect = mysql_connect($hostname, $username, $password)
or die('Could not connect: ' . mysql_error());
//Select The database
$bool = mysql_select_db($database, $connect);
if ($bool === False){
   print "can't find $database";
}
// get data and store in a json array
$query = "SELECT * FROM st_shippinglist";
mysql_query ( "set names 'utf8'" );
if (isset($_POST['update']))
{
	// UPDATE COMMAND 
	$update_query = "UPDATE `st_shippinglist` SET `date`='".mysql_real_escape_string($_POST['date'])."',
	`agentname`='".mysql_real_escape_string($_POST['agentname'])."',
	`secondagent`='".mysql_real_escape_string($_POST['secondagent'])."',
	`blno`='".mysql_real_escape_string($_POST['blno'])."',
	`vlm`=".mysql_real_escape_string($_POST['vlm']).",
	`cntrtype`='".mysql_real_escape_string($_POST['cntrtype'])."',
	`sellingrate`=".mysql_real_escape_string($_POST['sellingrate']).",
	`cost`=".mysql_real_escape_string($_POST['cost']).",
	`averageprofit`=".mysql_real_escape_string($_POST['averageprofit']).",
	`totalprofit`=".mysql_real_escape_string($_POST['totalprofit'])." WHERE `id`=".mysql_real_escape_string($_POST['id'])."";
	 $result = mysql_query($update_query) or die("SQL Error 1: " . mysql_error());
     echo $result;
}
elseif (isset($_POST['insert']))
{
    $date=mysql_real_escape_string($_POST['date']);
	$agentname=mysql_real_escape_string($_POST['agentname']);
	$secondagent=mysql_real_escape_string($_POST['secondagent']);
	$blno=mysql_real_escape_string($_POST['blno']);
	$vlm=mysql_real_escape_string($_POST['vlm']);
	$cntrtype=mysql_real_escape_string($_POST['cntrtype']);
	$sellingrate=mysql_real_escape_string($_POST['sellingrate']);
	$cost=mysql_real_escape_string($_POST['cost']);
	$averageprofit=mysql_real_escape_string($_POST['averageprofit']);
	$totalprofit=mysql_real_escape_string($_POST['totalprofit']);
   // $insert_query = "insert into `st_shippinglist` (`date`,`agentname`,`secondagent`,`blno`,`vlm`,`cntrtype`,`sellingrate`,`cost`,`averagerate`, `totalprofit`) value "
    $insert_query = "INSERT INTO `st_shippinglist`(
`date`,
`agentname`,
 `secondagent`,
`blno`,
`vlm`,
`cntrtype`,
`sellingrate`,
`cost`,
`averageprofit`,
`totalprofit`)
 VALUES
('".
$date."', '".
$agentname ."', '".
$secondagent ."', '".
$blno ."', ".
$vlm .", '".
$cntrtype ."', ".
$sellingrate .", ".
$cost .", ".
$averageprofit .", ".
$totalprofit .")";
    $result = mysql_query($insert_query) or die("SQL Error 1: " . mysql_error());
    echo $result;
}
elseif (isset($_POST['delete']))
{
    $delete_query="DELETE FROM `st_shippinglist` WHERE `id`=".mysql_real_escape_string($_POST['id']);
    $result = mysql_query($delete_query) or die("SQL Error 1: " . mysql_error());
    echo $result;
}
else
{
    if (isset($_GET['filterscount']))
    {
        $filterscount = $_GET['filterscount'];
        if ($filterscount > 0)
        {
            $where = " WHERE (";
            $tmpdatafield = "";
            $tmpfilteroperator = "";
            for ($i=0; $i < $filterscount; $i++)
            {
                // get the filter's value.
                $filtervalue = $_GET["filtervalue" . $i];
                // get the filter's condition.
                $filtercondition = $_GET["filtercondition" . $i];
                // get the filter's column.
                $filterdatafield = $_GET["filterdatafield" . $i];
                // get the filter's operator.
                $filteroperator = $_GET["filteroperator" . $i];
                if ($tmpdatafield == "")
                {
                    $tmpdatafield = $filterdatafield;
                }
                else if ($tmpdatafield <> $filterdatafield)
                {
                    $where .= ")AND(";
                }
                else if ($tmpdatafield == $filterdatafield)
                {
                    if ($tmpfilteroperator == 0)
                    {
                        $where .= " AND ";
                    }
                    else $where .= " OR ";
                }
                // build the "WHERE" clause depending on the filter's condition, value and datafield.
                switch($filtercondition)
                {
                    case "CONTAINS":
                        $where .= " " . $filterdatafield . " LIKE '%" . $filtervalue ."%'";
                        break;
                    case "DOES_NOT_CONTAIN":
                        $where .= " " . $filterdatafield . " NOT LIKE '%" . $filtervalue ."%'";
                        break;
                    case "EQUAL":
                        $where .= " " . $filterdatafield . " = '" . $filtervalue ."'";
                        break;
                    case "NOT_EQUAL":
                        $where .= " " . $filterdatafield . " <> '" . $filtervalue ."'";
                        break;
                    case "GREATER_THAN":
                        $where .= " " . $filterdatafield . " > '" . $filtervalue ."'";
                        break;
                    case "LESS_THAN":
                        $where .= " " . $filterdatafield . " < '" . $filtervalue ."'";
                        break;
                    case "GREATER_THAN_OR_EQUAL":
                        $where .= " " . $filterdatafield . " >= '" . $filtervalue ."'";
                        break;
                    case "LESS_THAN_OR_EQUAL":
                        $where .= " " . $filterdatafield . " <= '" . $filtervalue ."'";
                        break;
                    case "STARTS_WITH":
                        $where .= " " . $filterdatafield . " LIKE '" . $filtervalue ."%'";
                        break;
                    case "ENDS_WITH":
                        $where .= " " . $filterdatafield . " LIKE '%" . $filtervalue ."'";
                        break;
                }
                if ($i == $filterscount - 1)
                {
                    $where .= ")";
                }
                $tmpfilteroperator = $filteroperator;
                $tmpdatafield = $filterdatafield;
            }
            // build the query.
            $query = "SELECT * FROM st_shippinglist" . $where;
        }
    }
    // SELECT COMMAND
    $query = $query . " ORDER BY date DESC" ;
	$result = mysql_query($query) or die("SQL Error 1: " . mysql_error());
	while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
		$shippinglist[] = array(
			'id' => $row['id'],
			'date' => $row['date'],
			'agentname' => $row['agentname'],
			'secondagent' => $row['secondagent'],
            'blno' => $row['blno'],
            'vlm' => $row['vlm'],
            'cntrtype' => $row['cntrtype'],
            'sellingrate' => $row['sellingrate'],
            'cost' => $row['cost'],
            'averageprofit' => $row['averageprofit'],
            'totalprofit' => $row['totalprofit']
		  );
	}
	echo json_encode($shippinglist);
}
?>

[africanfarmerMember]

—-agentnamelist.php—

<?php
#Include the connect.php file
include('connect.php');
#Connect to the database
//connection String
$connect = mysql_connect($hostname, $username, $password)
or die('Could not connect: ' . mysql_error());
//Select The database
$bool = mysql_select_db($database, $connect);
if ($bool === False){
    print "can't find $database";
}
// get data and store in a json array
$query = "SELECT * FROM agent";
mysql_query ( "set names 'utf8'" );
if (isset($_POST['update']))
{
    $update_query = "UPDATE `agent` SET `agentname`='".mysql_real_escape_string($_POST['agentname'])."'
     WHERE `id`=".mysql_real_escape_string($_POST['id'])."";
    $result = mysql_query($update_query) or die("SQL Error 1: " . mysql_error());
    echo $result;
}
elseif (isset($_POST['delete']))
{
    $delete_query="DELETE FROM `agent` WHERE `id`=".mysql_real_escape_string($_POST['id']);
    $result = mysql_query($delete_query) or die("SQL Error 1: " . mysql_error());
    echo $result;
}
elseif (isset($_POST['insert']))
{
    $ad_name=mysql_real_escape_string($_POST['agentname']);
    $insert_query = "INSERT INTO `agent`(
`agentname`)
 VALUES
('".
        $ad_name."')";
    $result = mysql_query($insert_query) or die("SQL Error 1: " . mysql_error());
    echo $result;
}
else{
    $result = mysql_query($query) or die("SQL Error 1: " . mysql_error());
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    $agentnamelist[] = array(
        'id' => $row['id'],
        'agentname' => $row['agentname']
    );
}
echo json_encode($agentnamelist);
}

[africanfarmerMember]

—-statement.sql—

-- phpMyAdmin SQL Dump
-- version 4.0.4.1
-- http://www.phpmyadmin.net
--
-- 主机: localhost
-- 生成日期: 2013 年 08 月 10 日 08:00
-- 服务器版本: 5.6.12
-- PHP 版本: 5.5.0
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- 数据库: `statement`
--
CREATE DATABASE IF NOT EXISTS `statement` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `statement`;
-- --------------------------------------------------------
--
-- 表的结构 `agent`
--
CREATE TABLE IF NOT EXISTS `agent` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `agentname` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=12 ;
--
-- 转存表中的数据 `agent`
--
INSERT INTO `agent` (`id`, `agentname`) VALUES
(2, 'AA'),
(4, 'BB'),
(8, 'CC'),
(9, 'DD'),
(10, 'EE'),
(11, 'FF');
-- --------------------------------------------------------
--
-- 表的结构 `st_shippinglist`
--
CREATE TABLE IF NOT EXISTS `st_shippinglist` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `agentname` varchar(20) NOT NULL,
  `secondagent` varchar(20) NOT NULL,
  `blno` varchar(15) NOT NULL,
  `vlm` int(11) NOT NULL,
  `cntrtype` varchar(10) NOT NULL,
  `sellingrate` decimal(10,2) NOT NULL,
  `cost` decimal(10,2) NOT NULL,
  `averageprofit` decimal(10,2) NOT NULL,
  `totalprofit` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='utf8_general_ci' AUTO_INCREMENT=46 ;
--
-- 转存表中的数据 `st_shippinglist`
--
INSERT INTO `st_shippinglist` (`id`, `date`, `agentname`, `secondagent`, `blno`, `vlm`, `cntrtype`, `sellingrate`, `cost`, `averageprofit`, `totalprofit`) VALUES
(2, '2013-07-31', 'BB', 'dasd', 'sdf123', 1, '20GP', '123.00', '320.00', '-197.00', '-197.00'),
(3, '2013-07-24', 'AA', '232', '23', 232, '20GP', '2323.00', '23.00', '23.00', '23.00'),
(4, '2013-07-23', 'CC', '232', '23', 232, '20GP', '2323.00', '23.00', '2300.00', '533600.00'),
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

[Peter StoevKeymaster]

Hi africanfarmer,

For updating rows you try to use row index, not row ID. To get the row’s id, you should use the “getrowid” method.

Best Regards,
Peter Stoev

jQWidgets Team
http://www.jqwidgets.com/

[africanfarmerMember]

Hi peter,

but seems I used row index->getslectedrowindex. Can you advise how to amend below code? as i think, if there is no error before filtering, it means no error in code for getting the row id or index. maybe i though in wrong, can you help on it?

   // update the edited row when the user clicks the 'Save' button.
    $("#Save").click(function () {
        var selectedrowindex = $("#jqxgrid").jqxGrid('getselectedrowindex');
        var rowscount = $("#jqxgrid").jqxGrid('getdatainformation').rowscount;
        var datarow = { date: $("#date").val(), agentname: $("#agentname").val(), secondagent: $("#secondagent").val(), blno: $("#blno").val(),
            vlm: $("#vlm").val(), cntrtype: $("#cntrtype").val(), sellingrate: $("#sellingrate").val(), cost: $("#cost").val(),
            averageprofit: averageprofit($("#sellingrate").val(),$("#cost").val()), totalprofit: totalprofit($("#vlm").val(),$("#sellingrate").val(),$("#cost").val())};
        if (selectedrowindex >= 0 && selectedrowindex < rowscount) {
            var id = $("#jqxgrid").jqxGrid('getrowid', selectedrowindex);
            var commit = $("#jqxgrid").jqxGrid('updaterow', id, datarow);
            $("#jqxgrid").jqxGrid('ensurerowvisible', selectedrowindex);
            $("#popupWindow").jqxWindow('close');
        }
    });

[africanfarmerMember]

Hi Peter,

sorry for asking you again and again! I feel headache on catching the row selected. still no solution against your comments. i replace rowid i/o getslectedbyindex, appear wrong. changed back, running well, but still have the mentioned issue.

[Author]

Posts