Pete Freitag Pete Freitag

jQuery UI Sortable Tutorial

Published on January 07, 2010
By Pete Freitag
coldfusionweb

Here's a simple tutorial showing how to use jQuery UI's sortable plugin to update a database table's sort order field on the fly using ajax.

Here's the HTML markup used to define the content that will be sortable (note you don't need to use a ul as most examples will show, you can use any container, and its children will be sortable in this case we used divs.):

<div id="fruit_sort">
	<div id="fruit_1">Apple</div>
	<div id="fruit_2">Orange</div>
	<div id="fruit_3">Pear</div>
</div>

In order to make the fruit_sort container sortable, we need to run $('#fruit_sort').sortable(); at a bare minimum. But how can we update our database every time the list is sorted? To do this we must bind to the sortable update event. Accordign to the docs this event is triggered when the user stopped sorting and the DOM position has changed.

So here's the JavaScript we are going to use to make our fruit_sort work:

<script language="javascript">
	$(document).ready(function(){
		$('#fruit_sort').sortable({
			update: function(event, ui) {
				var fruitOrder = $(this).sortable('toArray').toString();
				$.get('update-sort.cfm', {fruitOrder:fruitOrder});
			}
		});
	});
</script>

By calling $(this).sortable('toArray').toString() in our example update event handler we are given a string list of all the item id's, it might look like fruit_2,fruit_1,fruit_3. We can then send the new order to our server in an ajax request using $.get()

Finally to handle things on the server side (using ColdFusion in this demo) we might have some code that looks like this update-sort.cfm:

<cfparam name="url.fruitOrder" type="string">
<!--- fruitOrder = fruit_1,fruit_2, etc...--->
<cfset order = 0>
<cfloop list="#url.fruitOrder#" index="item">
	<cfset fruit_id = Val(ListLast(item, "_"))>
	<cfset order = order+1>
	<cfquery>
		UPDATE fruit
		SET sort_order = <cfqueryparam value="#order#" cfsqltype="cf_sql_integer">
		WHERE fruit_id = <cfqueryparam value="#fruit_id#" cfsqltype="cf_sql_integer">
	</cfquery>
	<cfoutput>#fruit_id#</cfoutput>
</cfloop>


jquery jqueryui javascript js sortable ajax

jQuery UI Sortable Tutorial was first published on January 07, 2010.

If you like reading about jquery, jqueryui, javascript, js, sortable, or ajax then you might also like:

FuseGuard Web App Firewall for ColdFusion

The FuseGuard Web Application Firewall for ColdFusion & CFML is a high performance, customizable engine that blocks various attacks against your ColdFusion applications.

CFBreak
The weekly newsletter for the CFML Community


Comments

Thanks for this nice tip, it helped me a lot.
by Prashant on 02/24/2010 at 5:12:35 AM UTC
I used to do something similar to this until I discovered the jQuery Nested Sortable Widget:
http://plugins.jquery.com/project/NestedSortableWidget

It uses JSON, so it's a little more difficult than the technique you are using, but it additionally allows for nested relationship data to be exchanged.
by James Moberg on 03/18/2010 at 1:59:11 PM UTC
I don't seem to be able to get this to work. The sorting works but it's the server side updating that doesn't seem to be happening. I don't get any error messages at all - is there any way I can tell where or what is going wrong?
by Lee Boardman on 06/24/2010 at 9:47:39 AM UTC
@Lee try using FireBug to watch the AJAX HTTP requests.
by Pete Freitag on 07/14/2010 at 1:17:47 PM UTC
I've been googling for a solution to a sortable project that I've been asked to create. This tutorial and a couple others helped me figure out persistence with a single list, but I am trying to use connected lists, and am perplexed at figuring this one out. Basically I have 5 columnar divs, each containing sortables - I need to not only be able to change order within a div (easy), but to move an item from one div to another - obviously this will change the ordering within 2 divs. I'm perplexed at getting that back from jquery. So far I can only get the re-ordering of the first div. (I'm a php guy but I'm sure the principle of any solution will be the same)
by bryan on 10/01/2010 at 7:22:45 PM UTC
may be that this sorting widget is part of a form so, instead of firing an ajax call for every update, i suggest to use an hidden field containing the list of the id's, updating this field for every order change, and then submitting it at end.
regards
Salvatore
by Salvatore Fusto on 11/01/2010 at 10:45:03 AM UTC
This article came in handy today, on the jquery ui example side of things. One suggestion I'd like your opinion on... rather than executing a query inside the loop, for better performance (?) I would simply build out the SQL and execute it once at the end of the loop (MSSQL/T-SQL). My example below was for sorting slides, and I wanted the interval to be in increments of ten:

<!--- hint: attributes.slideOrder is a list, in the format "slide_{id},...slide_{id}" --->
<cfparam name="attributes.slideOrder">


<cfoutput>

<cfset intPositionBase = 0 />
<cfset strSqlStatement = "" />
<cfset strCurSqlStatement = "" />

<cfloop list="#attributes.slideOrder#" index="strSlideId">

<cfset intPositionBase++ />
<cfset intPosition = intPositionBase * 10>
<cfset slideId = ListLast(strSlideId, "_")>

<cfsavecontent variable="strCurSqlStatement">
UPDATE Slides SET intPosition = #intPosition# WHERE id = #slideId#;
</cfsavecontent>

<cfset strSqlStatement = strSqlStatement & strCurSqlStatement />

</cfloop>

<cfquery name="rsUpdateSlides" datasource="#request.webDsn#">
#strSqlStatement#
</cfquery>


</cfoutput>


Thoughts?
by Marc Funaro on 01/26/2012 at 4:16:48 PM UTC
@Marc - rather than building the SQL and then executing it, I would rather loop inside the <cfquery> tag, that way you can use cfqueryparam.
by Pete Freitag on 01/26/2012 at 4:33:35 PM UTC
Thanks you very much!

Now I have a string with the list of elements divided by commas on my db.

Whats the best way to display the elements sorted when user loads his page after he sorted, is there a jquery script to resort based on the string I have on my db?
by Tzvi on 02/15/2012 at 8:35:45 AM UTC