Reorder table rows, update priority in database

I have a table with rows that is sorted based on the INT Priority in my database.

Every row has a <input type="hidden" /> with an ID reference to the database. It also have some up and down arrows (class .up and .down) with the following JavaScript to move the row.

$(document).ready(function(){
    $(".up,.down").click(function(){
        var row = $(this).parents("tr:first");
        if ($(this).is(".up")) {
            row.insertBefore(row.prev());
        } else {
            row.insertAfter(row.next());
        }
    });
});

Now my question is, how to I update the priority in 开发者_JAVA技巧the database? I need somehow to get the order of the ID's and update the priority column - is there a neat solution for this?

Use jQuery to get a list of the IDs after you have moved a row. Something like:

var inputs = $("#myTable").find("tr").find("input");

// store each rows id in an array - this will be in the correct order
var ids = [];
inputs.each(function(){
    ids.push($(this).val());
});

Then pass this list to a PageMethod or WebService and loop through the list, setting the priority of each row in the database. Obviously you will also need to take into account paging or any filtering you have applied to the items too.

Update:

You might want to also look at jQueryUI Sortable to enable drag/drop sorting. You would update the db in the same way.

Update 2: Here is a simple delay function.

var delay = (function () {
    var timer = 0;
    return function (callback, ms) {
        clearTimeout(timer);
        timer = setTimeout(callback, ms);
    };
})();

Then use it as follows:

delay(function () {
  MySortFunction();
}, 300);

This will delay the function for x milliseconds and cancel any previous calls if you call it again within the specified time.

well, if you also update the prioirty number (index?) when you reorder two rows (), right in the handler for 'up' / 'down' next to the row.insertBefore(row.prev()); then you could simply loot through the rows on the server and generate a simple "update x set priority = @priority where id = @id"

This may be overkill but I wanted to randomly move a row more than one row up or down. Still have to submit back to the server and this snippet doesn't have the <form> tag, but it should be pretty easy to process the inputs into a sort order based on looping through the input name form.hdrSort* values.

<cfoutput>
<script type="text/javascript">
var maxHdr=#qX.recordCount#;
var curHdr=0;
$(document).ready(function(){
    bindReorder();//set with function because when the table gets regenerated, the ordering functionality gets stripped and has to be rebound to the content
});
function bindReorder(ok2do){
    if(ok2do==null) ok2do=true;
    $("input[id^='hdr']").each(function(){
        $(this).mask("?999").focus(function(){
            curHdr=parseInt($(this).val());//original value held in global js var
        }).blur(function(){
        var tVal=parseInt($(this).val());//entered value
        if(curHdr!=tVal){
            var tId =parseInt($(this).attr("id").substr(3));//id of changed value - this is the new value we don't change'
            if(tVal>#qX.recordCount# || tVal<1){//validate entered is in scope
                alert("please enter a positive number less than or equal to #qX.recordCount#");
                $(this).val(curHdr);
            }else if(ok2do){
                var lo=Math.min(tVal,curHdr);//lower of original and entered values
                var hi=Math.max(tVal,curHdr);//higher of original and entered values
                var upDn=1;//default that entered value is less than original value
                var aryHdrTbls=new Array(#qX.recordCount#+1);//zero based
                if(lo==curHdr) upDn=-1;
                $("input[id^='hdr']").each(function(i){
                    var checkVal=parseInt($(this).val());
                    var thisId=parseInt($(this).attr("id").substr(3));
                    if(checkVal<=hi && checkVal>=lo){
                        if(thisId!==tId) $(this).attr("value",checkVal+upDn);
                        else $(this).attr("value",checkVal);
                        aryHdrTbls[$(this).val()]=$("##tbl"+thisId).parent().html();
                    }
                });
                for(var i=lo; i<=hi; i++){
                    $("##td"+i).html(aryHdrTbls[i]);
                }
                    bindReorder(false);
                }
            }
        });
    });
}
</script>
<table width="80%">
<cfloop query="qX">
<tr>
<td id="td#qX.currentRow#">
    <table style="width:100%;border:1px solid gray;" id="tbl#qX.currentRow#">
        <tr>
            <td colspan="2" style="background-color:##dddddd;">
                <div style="float:right;"><input name="hdrSort#qX.currentRow#" id="hdr#qX.currentRow#" size="1" value="#qX.currentRow#">     <input type="button" width="3" value="go"></div></td>
        </tr>
        <tr>
            <td>#qX.currentRow# #qX.nada#</td>
            <td>#qX.nada# more your content original header #qX.currentRow#</td>
        </tr>
    </table>
</td>
</tr>
</cfloop>
</cfoutput>
</table>