Sorting table rows with jQuery and ColdFusion

27 Feb 2008

I posted a drag and drop example of sorting and saving a list using the jQuery sortables plugin. I was asked how this could be done with table rows. The jQuery TableDnD plugin comes to the rescue!

It doesn’t have serialize method so you need to get the new order yourself which can be done easily by looping through the rows and building a list in the correct order.


<html>
<head>
<script type="text/javascript" src="js/jquery-1.2.3.min.js"></script>
<script type="text/javascript" src="js/jquery.tablednd.js"></script>
<script type="text/javascript">
<!-- <![CDATA[
$(function(){
  $("#sortable-tbl").tableDnD();

  $('#frm-sort').submit(function(){
    var sRowOrder = "";
    $("#sortable-tbl tr").each(function(i,o){
    if (sRowOrder.length) {
      sRowOrder += "," + o.id;
    } else {
      sRowOrder = o.id;
    }
    });
    $('#sort_order').val(sRowOrder);
  });
});
// ]]> -->
</script>
</head>
<body>

<table id="sortable-tbl">
    <tr id="section_id_1">
    <td>One</td>
    <td>Row A</td>
  </tr>
  <tr id="section_id_2">
    <td>Two</td>
    <td>Row B</td>
  </tr>
  <tr id="section_id_3">
    <td>Three</td>
    <td>Row C</td>
  </tr>
  <tr id="section_id_4">
    <td>Four</td>
    <td>Row D</td>
  </tr>
</table>

<form id="frm-sort">
  <input type="submit" name="save" id="save" value="save" />
  <input type="hidden" name="sort_order" id="sort_order" value="" />
</form>

</body>
</html>

* UPDATE *

Someone asked how to update the database using ColdFusion, so here is the code:


<!--- strip the 'section_id_' from the list, so that we only have a list of ids --->
<cfset form.sort_order = ReplaceNoCase(form.sort_order, "section_id_", "", "all") />

<cfloop from="1" to="#ListLen(form.sort_order)#" index="ndx">
  <cfquery name="qryOrder" datasource="#Request.Datasource#">
    update [Sections] set
      section_order = <cfqueryparam value="#ndx#" cfsqltype="cf_sql_integer" />
    where section_id = <cfqueryparam value="#ListGetAt(form.sort_order, ndx)#" cfsqltype="cf_sql_integer" />
  </cfquery>
</cfloop>

Posted in:
jQuery
ColdFusion

13 comments

Thanks for the code for updating db records with ColdFusion!

Rick

Comment by Rick Faircloth – March 18, 2008
Is there a way to make the form submit after a drop happens (instead of clicking the save button)?

Comment by Ryan – March 24, 2009
Hi Ryan,
There is a "onDrop" method of the tableDnD plugin which you can use:

$("#sortable-tbl").tableDnD({
onDrop: function(table, row) {
// do something here
}
});

There is more info in this post:
www.isocra.com/2008/02/table-drag-and-drop-jquery-plugin/

Comment by John Whish – March 25, 2009
John, thanks for pointing this one out. Was getting frustrated at only being able to do drag/sort properly with lists when many data sets really need tables.

Since you originally posted, the plugin's been improved a bit and there's now a built-in serialize function. To expand a bit on your answer to Ryan, here's how you might use it and then post the result to the server via ajax (avoiding the need for a manual submission form):

$("#sortable-tbl").tableDnD({
onDrop: function(table, row) {
var newOrder = $(table).tableDnDSerialize();
$.ajax({
url:"UrlOfMyFormProcessor.cfm"
,type:"POST"
,data:newOrder
});
}
});

Also, there's no need to parse out the IDs on the server. The serialize function will do this for you as long as you use a hyphen before the ID value. So instead of naming your table row IDs:

section_id_1

etc, name them:

section_id-1

and the serialise function will return just "1" without "section_id-", so that ColdFusion will get a list of just the ID values.

Comment by Julian Halliwell – March 27, 2009
Hi Julian. I did spot that a serialise function had been added to the plugin after I'd posted my comment. Using a hyphen instead of an underscore before the id is a top tip - thanks for that!

Comment by John Whish – March 27, 2009
I keep getting an error when trying to load the page. The sql isnt updating either.

Element SORT_ORDER is undefined in FORM.

<html>
<head>
<script type="text/javascript" src="js/jquery-1.4.2.min.js"></script>
<script type="text/javascript" src="js/jquery.tablednd_0_5.js"></script>
<script type="text/javascript">

</script>
</head>
<body>

<cfquery name="getSplashInfo" datasource="DMC">
  select * from splash_order
  order by splash_ordinal asc
</cfquery>

<cfoutput>
<table id="sortable-tbl">
  <cfloop query="getSplashInfo">
  <tr id="splash_items_id_#getSplashInfo.splash_items_id#">
    <td>#getSplashInfo.splash_name#</td>
    <td>#getSplashInfo.splash_ordinal#</td>
    </tr>
  </cfloop>
</table>
</cfoutput>

<form id="frm-sort">
<input type="submit" name="save" id="save" value="save" />
<input type="hidden" name="sort_order" id="sort_order" value="" />
</form>

  
  <cfset form.sort_order = ReplaceNoCase(form.sort_order, "section_id_", "", "all") />

  <cfloop from="1" to="#ListLen(form.sort_order)#" index="ndx">
   <cfquery name="qryOrder" datasource="DMC">
   update splash_Order
    set splash_ordinal = <cfqueryparam value="#ndx#" cfsqltype="cf_sql_integer" />
   where splash_items_id = <cfqueryparam value="#ListGetAt(form.sort_order, ndx)#" cfsqltype="cf_sql_integer" />
   </cfquery>
  </cfloop>

</body>
</html>

Comment by Justin – July 26, 2010
@Justin - sorry bit late with the reply! Have you solved this? If not ping me offline and I'll try to help.

Comment by John Whish – August 17, 2010
I'm also getting a SORT ORDER error message as well. See below:

Element SORT_ORDER is undefined in FORM.

The error occurred in C:\ColdFusion9\wwwroot\HFIntranet\DM\Admin\MaintenanceDragTest.cfm: line 167

166 : 
167 : <cfset form.sort_order = ReplaceNoCase(form.sort_order, "section_id_", "", "all") />
168 :
169 : <cfloop from="1" to="#ListLen(form.sort_order)#" index="ndx">

Comment by Bill – November 29, 2011
I'm also getting a SORT ORDER error message as well. See below:

Element SORT_ORDER is undefined in FORM.

The error occurred in C:\ColdFusion9\wwwroot\HFIntranet\DM\Admin\MaintenanceDragTest.cfm: line 167

166 : 
167 : <cfset form.sort_order = ReplaceNoCase(form.sort_order, "section_id_", "", "all") />
168 :
169 : <cfloop from="1" to="#ListLen(form.sort_order)#" index="ndx">

Comment by Bill – November 29, 2011
Hi Bill ,

Basically you have the page say "foo.cfm" with the form on it. The
form needs to have an action, so something like:

foo.cfm

<html>
...code here...

<form id="frm-sort" action="myscript.cfm" method="post">
<form>

</html>

Then in myscript.cfm you have the code to save it to the database.

So myscript.cfm is:


<cfset form.sort_order = ReplaceNoCase(form.sort_order, "section_id_",
"", "all") />

<cfloop from="1" to="#ListLen(form.sort_order)#" index="ndx">
<cfquery name="qryOrder" datasource="#Request.Datasource#">
update [Sections] set
section_order = <cfqueryparam value="#ndx#" cfsqltype="cf_sql_integer" />
where section_id = <cfqueryparam
value="#ListGetAt(form.sort_order, ndx)#" cfsqltype="cf_sql_integer"
/>
</cfquery>
</cfloop>

Does that help?

Comment by John Whish – December 01, 2011
Thanks John. I'll give that a try. By the way, I'm assuming that each table row generated by a cfoutput query must have a unique ID assigned to it. Would you know what the code is that I owuld have to use to create the id's for each table row?

Comment by Bill – December 01, 2011
Disregard the last post. I now have the page creating id's for the table rows dynamically but it' still not working. I added a cdump to myscript.cfm but I'm getting noting other than the following:
FIELDNAMES   SAVE,SORT_ORDER
SAVE   save
SORT_ORDER   [empty string]

Comment by Bill – December 01, 2011
Anybody come across a hack for this with IE8? The crosshairs icon appears but no drag n' drop.

Comment by Bill – December 01, 2011

If you found this post useful, interesting or just plain wrong, let me know - I like feedback :)

Please note: If you haven't commented before, then your comments will be moderated before they are displayed.

Sorting table rows with jQuery and ColdFusion

* UPDATE *

13 comments

Leave a comment

Search

Wish List

Categories

Recent Posts