jQuery UI Sortable Tutorial

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>


Related Entries

14 people found this page useful, what do you think?

 Download FuseGuard WAF for ColdFusion

Trackbacks

Trackback Address: 736/B4080E5B30D0774B9E945AC76C75019A

Comments

On 02/24/2010 at 7:12:35 AM EST Prashant wrote:
1
Thanks for this nice tip, it helped me a lot.

On 03/18/2010 at 3:59:11 PM EDT James Moberg wrote:
2
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.

On 06/24/2010 at 11:47:39 AM EDT Lee Boardman wrote:
3
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?

On 07/14/2010 at 3:17:47 PM EDT Pete Freitag wrote:
4
@Lee try using FireBug to watch the AJAX HTTP requests.

On 10/01/2010 at 9:22:45 PM EDT bryan wrote:
5
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)

On 11/01/2010 at 12:45:03 PM EDT Salvatore Fusto wrote:
6
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

On 01/31/2011 at 11:11:30 PM EST Joe Russell wrote:
7
Dude, this example saved the day for me. Great job!

On 08/05/2011 at 3:41:31 AM EDT Madly wrote:
8
Thanks a lot. Great tut! Helped me a lot. Greets from Germany

On 09/08/2011 at 4:54:15 AM EDT Mateo wrote:
9
Thx for this article it helped a lot

On 01/26/2012 at 6:16:48 PM EST Marc Funaro wrote:
10
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?

On 01/26/2012 at 6:33:35 PM EST Pete Freitag wrote:
11
@Marc - rather than building the SQL and then executing it, I would rather loop inside the <cfquery> tag, that way you can use cfqueryparam.

On 02/15/2012 at 10:35:45 AM EST Tzvi wrote:
12
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?

Post a Comment




  



Spell Checker by Foundeo

Recent Entries



foundeo


did you hack my cf?