Using jQuery or MooTools For Drag, Drop, Sort, Save

One of my most popular posts has been Using MooTools 1.2 for Drag, Drop, Sort, Save. My post detailed how you can create a drag'n'drop, AJAX-ified system to allow the user to drag and drop elements and quickly save them with PHP and MySQL on the server side. I've chosen to update the post with a faster, more efficient set of MooTools and PHP code. I've also provided a jQuery equivalent. Enjoy!

MooTools Demo jQuery Demo

The MySQL Table

id	title	sort_order
1	Article 1	1
2	Article 2	2
3	Article 3	3
4	Article 4	4
5	Article 5	5
6	Article 6	6

This table shows only the important fields per this functionality: ID, Title, and Sort Order. Your table will likely have many more columns.

The PHP / HTML List Build

<?php
	
	$query = 'SELECT id, title FROM test_table ORDER BY sort_order ASC';
	$result = mysql_query($query,$connection) or die(mysql_error().': '.$query);
	if(mysql_num_rows($result)) {
	
?>
<p>Drag and drop the elements below.  The database gets updated on every drop.</p>

<div id="message-box"><?php echo $message; ?> Waiting for sortation submission...</div>

<form id="dd-form" action="<?php echo $_SERVER['REQUEST_URI']; ?>" method="post">
<p>
	<input type="checkbox" value="1" name="autoSubmit" id="autoSubmit" <?php if($_POST['autoSubmit']) { echo 'checked="checked"'; } ?> />
	<label for="autoSubmit">Automatically submit on drop event</label>
</p>

<ul id="sortable-list">
	<?php 
		$order = array();
		while($item = mysql_fetch_assoc($result)) {
			echo '<li title="',$item['id'],'">',$item['title'],'</li>';
			$order[] = $item['id'];
		}
	?>
</ul>
<br />
<input type="hidden" name="sort_order" id="sort_order" value="<?php echo implode(',',$order); ?>" />
<input type="submit" name="do_submit" value="Submit Sortation" class="button" />
</form>
<?php } else { ?>
	
	<p>Sorry!  There are no items in the system.</p>
	
<?php } ?>

We'll start out by querying the database to retrieve all records from the table. If there are no records available, we simply show a message saying so. Once we have established that records are available, we:

create a message box DIV that will notify users of the status of AJAX request actions.
create a form element.
create an "auto-save" option checkbox that directs whether or not the sort order should be saved on every drag/drop.
create a UL element that outputs the list of records in their current sort order. Each LI element has its ID temporarily stored in its HTML attribute. (For those of you who don't mind using custom element attributes, feel free to create a custom attribute to store the record ID).
create a hidden INPUT element to dynamically store the current sort order.
create a submit button that will work via AJAX or typical form submission.

There's a lot of stuff going on here but it's all necessary to ensure positive user experience, maximal functionality, and reliability.

The CSS

#sortable-list		{ padding:0; }
#sortable-list li	{ padding:4px 8px; color:#000; cursor:move; list-style:none; width:500px; background:#ddd; margin:10px 0; border:1px solid #999; }
#message-box		{ background:#fffea1; border:2px solid #fc0; padding:4px 8px; margin:0 0 14px 0; width:500px; }

None of the code here is required by, as always, you need to style your elements to fit your website. Since the drag and drop effect looks so cool, you're going to want to make your elements look cool too.

The MooTools JavaScript

/* when the DOM is ready */
window.addEvent('domready', function() {
	/* grab important elements */
	var sortInput = document.id('sort_order');
	var submit = document.id('autoSubmit');
	var messageBox = document.id('message-box');
	var list = document.id('sortable-list');
	
	/* get the request object ready;  re-use the same Request */
	var request = new Request({
		url: '<?php echo $_SERVER["REQUEST_URI"]; ?>',
		link: 'cancel',
		method: 'post',
		onRequest: function() {
			messageBox.set('text','Updating the sort order in the database.');
		},
		onSuccess: function() {
			messageBox.set('text','Database has been updated.');
		}
	});
	/* worker function */
	var fnSubmit = function(save) {
		var sortOrder = [];
		list.getElements('li').each(function(li) {
			sortOrder.push(li.retrieve('id'));
		});
		sortInput.value = sortOrder.join(',');
		if(save) {
			request.send('sort_order=' + sortInput.value + '&ajax=' + submit.checked + '&do_submit=1&byajax=1');
		}
	};
	
	/* store values */
	list.getElements('li').each(function(li) {
		li.store('id',li.get('title')).set('title','');
	});
	
	/* sortables that also *may* */
	new Sortables(list,{
		constrain: true,
		clone: true,
		revert: true,
		onComplete: function(el,clone) {
			fnSubmit(submit.checked);
		}
	});
	
	/* ajax form submission */
	document.id('dd-form').addEvent('submit',function(e) {
		if(e) e.stop();
		fnSubmit(true);
	});
	
	
});

The first step in the process is rounding up the list of key elements in the page. Then we create our Request instance which will be used for every AJAX request. Next we create fnSubmit the function that will round up the LI elements (records) and their sort order. Lastly, we create our Sortables instance and connect submission event to the form's submit button. When you split the pieces apart, the system is actually quite simple.

The jQuery JavaScript

/* when the DOM is ready */
jQuery(document).ready(function() {
	/* grab important elements */
	var sortInput = jQuery('#sort_order');
	var submit = jQuery('#autoSubmit');
	var messageBox = jQuery('#message-box');
	var list = jQuery('#sortable-list');
	/* create requesting function to avoid duplicate code */
	var request = function() {
		jQuery.ajax({
			beforeSend: function() {
				messageBox.text('Updating the sort order in the database.');
			},
			complete: function() {
				messageBox.text('Database has been updated.');
			},
			data: 'sort_order=' + sortInput[0].value + '&ajax=' + submit[0].checked + '&do_submit=1&byajax=1', //need [0]?
			type: 'post',
			url: '<?php echo $_SERVER["REQUEST_URI"]; ?>'
		});
	};
	/* worker function */
	var fnSubmit = function(save) {
		var sortOrder = [];
		list.children('li').each(function(){
			sortOrder.push(jQuery(this).data('id'));
		});
		sortInput.val(sortOrder.join(','));
		console.log(sortInput.val());
		if(save) {
			request();
		}
	};
	/* store values */
	list.children('li').each(function() {
		var li = jQuery(this);
		li.data('id',li.attr('title')).attr('title','');
	});
	/* sortables */
	list.sortable({
		opacity: 0.7,
		update: function() {
			fnSubmit(submit[0].checked);
		}
	});
	list.disableSelection();
	/* ajax form submission */
	jQuery('#dd-form').bind('submit',function(e) {
		if(e) e.preventDefault();
		fnSubmit(true);
	});
});

The jQuery code is surprisingly similar to the MooTools version. Please note that duplicating the jQuery functionality will require that you also download the jQuery UI library. It's a good chunk of extra code but the functionality works great.

The "Header" PHP/MySQL

/* on form submission */
if(isset($_POST['do_submit']))  {
	/* split the value of the sortation */
	$ids = explode(',',$_POST['sort_order']);
	/* run the update query for each id */
	foreach($ids as $index=>$id) {
		$id = (int) $id;
		if($id != '') {
			$query = 'UPDATE test_table SET sort_order = '.($index + 1).' WHERE id = '.$id;
			$result = mysql_query($query,$connection) or die(mysql_error().': '.$query);
		}
	}
	
	/* now what? */
	if($_POST['byajax']) { die(); } else { $message = 'Sortation has been saved.'; }
}

The "header" or processing PHP file receives the sort order, splits the string apart by the comma delimiter, and executes queries to update the sort order. Since PHP's mysql_query function wont allow for more than one query at a time, queries need to be executed separately. If you use another MySQL/PHP library (PDO, etc.) you may want to append the queries to a single string and execute them all at once. Depending on the method by which the user submitted the update (AJAX or normal post), the PHP will either die out or reload the page per usual.

MooTools Demo jQuery Demo

My clients have always loved this feature within their CMS. Sorting records can be hugely important and allowing for an easy method by which to do so can make you look like a miracle worker.

Comments

Rob
Hi David,
Unfortunately, the jQuery example doesn’t update, and instead errors out. The MooTools example works well though :)
R.
David Walsh
@Rob: Took out the console.log statement — works again.
Ivan
This is awesome (and frankly, just what I need right now)!
stefan
mootools version doesn’t work on internet explorer … jquery does to bad that the mootools version is so much nicer :P
Jason
I like this and I use similar code (saem affect) in jquery. One thing I am not happy with is having it run an update mysql query for every item on every save. Example lets, say a social network site has a “top friends” section for a user, they have 24 top friends. I let them drag/drop these top friends like myspace and others have done forever now. The user moves 1 friend around in that 24 friends and thats 24 mysql queries made everytime they move 1 friend, that is way to much IMO. I think in the case I speek of that it might be better to store a list of ID’s into 1 mysql field per user. What do you think?
David Walsh
@stefan: Fixed in IE. It’s a MooTools More bug which I will fix.
Bob G
Can’t seem to get jQuery to work. And mooTools breaks with more than 9 items.
David Walsh
@Bob G: I can’t help without more detail as the jQuery issue. As far as your MooTools issue, do you need to make your “sort_order” field longer?
Bob G
@David Walsh: Got it figured out. The jQuery issue was the URL in the request var. I forgot to change it to my page. The problem with the mooTools one was the sort_order type in the database. I had TEXT instead of INT. I had two identical tables, but for some reason, I missed that in the mooTools one (Stupidity would be my guess). By the way, thanks for being here. I learn a lot coming here.
Bob G
Also, somehow I managed to add two more fields to the table, a link and a category. I think if I keep coming here I may actually learn something. Again, thanks.
Bob G
Any way to hide the “Automatically submit on drop event” check box?
Bob G
Got it. Added a hide class.
Also, to get the sort_order on an insert I did this:
$query = mysql_query(“SELECT MAX(sort_order+1) AS sort_new FROM links_test_three”) or die(mysql_error());
list($sort_new) = mysql_fetch_row($query);
$sort_order = $sort_new;
iams
I have a weird issue implementing the jquery version,
if i don’t attach jquery ui i can’t submit the form, just can’t drage the items.
if i attach jquery ui i can then drag the items, but the form won’t submit
iams
i copied the jquery code from the example instead and it works now
Bob G
Any way to get the window to scroll while dragging the element?
iams
what is the purpose of in the text that updates, I removed it and the script seems to work the same
iams
oops, looks like the filter took out code, before the text that updates it has echo message but it seems to serve no purpose
@iams:
eliezer
Does it work with pagination?
iams
I wish it did, one of the main problems is, let’s say you have 10 items on each page, you can only arrange the items within those 10, unless you can figure out using input boxes.
I don’t think even netflix has pagination in the quene
eliezer
@iams:
How about
$query = ‘UPDATE test_table SET sort_order = ‘.($index + 1 + $max*$page).’ WHERE id = ‘.$id;
Where $max is the number of items to show per page multiplying the number of pages ($page).
@iams:
iams
@eliezer:
i could be wrong but i still don’t see it working.
let’s say you’re on the 3rd page and it list 31-40, you want make #32 #4 and #37 #11, i don’t see how, or at leas can’t figure out how to do it.
maybe someone with greater php skills can confirm this for us, cause if it were possible i’d like to implement it that way myself
eliezer
@iams:
i’m afraid you’re right.
it would work only to rearrange the order in each particular page.
eliezer
Now, don’t get me wrong I hate spammers but there’s a component for a image gallery in CodeIgniter, wich supossedly if you drag an item to the page number on the pagination links, moves the picture there.
The information available on this since I got some source code from another developer, is this HTML replica of a CodeIgniter module in this link:
http://www.colegiomarktwain.edu.mx/marktwain.html
The pagination so far hasnt reached enough items to do a second page but trust me, it works. This code, I repeat, is from another developer.
Maybe you guys could give a look to the sctips src=”" in the head contents of the HTML link above and figure out how it’s built. Then share it with us too. ( ;
Jimmy Kirk
Does anybody have this setup scaled for sorting several lists on a single page with dynamic population?
neutral
@eliezer: I was just trying to figure out an obvious way for allowing users to sort and move items to other pages, and simply dragging it onto the page number sounds like a good idea.
eliezer
@neutral: if you can do it i surely would like to take a view to it, maybe even work together but it’s beyond my js possibilities, now.. php….
Blady
it is still not working in IE 8 , for Fire Fox there is no problem at all, can somebody help?
blady
it is still not working in IE 8 , for Fire Fox there is no problem at all, can somebody help?
thanks for your help in advance guys :-)
Kevin
Still can’t get the PHP solution to work. The “Header” PHP/MySQL only gets called on page load, not when submit is clicked or autosubmit. AJAX says it is successful, but db does not get updated.
-k
iams
the header should only get called on page load, the ajax will call the script to update the database. You may just have something wrong in your code, took me a bit of tweaking to get it working
Kevin
@iams: How can I see what query ajax is sending? I get no javascript errors (using Firefox error console). Thanks,
-k
iams
well you can’t really, that’s the problem, the code that updates the database is in the header section which gets called by ajax in the background
make sure your url: is set to the same as it is in your browser, that’s how i have mine
url: http://mydomain/somesectioon/somepage.php
Kevin
It’s working. It was updating all along – there was just a confusion between test and production databases. Ugh :-)
iams
@Kevin: happens to me all the time, the problem with the ajax in there is that you can’t spit out the error as you usually would with just php
ram
I’m not sure where the “header” code is supposed to go – at the beginning of the page (above the doc declaration) in tags it appears, but this is the only part of the script I cannot get to work.
ram
Figured it out: you cannot have the js in it’s own file because it has a php statement in there that will not run in a .js file. Thanks David.
blady
Does it work properly in IE8 for you all?? if it does, please could you be so kind to send me a llink to your sites?
thanks in advance guys
ram
@baldy: Possible IE fix-> Try making the position:relative for the #sortable-list.
ram
@ram: only works for IE8 – any other ideas?
blady
it still does not work for IE8 for me even changing the position to relative.
I get the following error message:
Webpage error details
User Agent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; WOW64; Trident/4.0; GTB0.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0)
Timestamp: Sat, 7 Aug 2010 07:58:15 UTC
Message: ‘d’ is null or not an object
Line: 326
Char: 147
Code: 0
URI: http://www.bardiles.com/admin/admin/source/mootools-1.2.4.4-more.js
i have no clue what is going on here ^^
The thing in David’s Blog WORKS!!!!!! in IE 8 too i checked the code and he is using only one mootools file as attached JS … which I copied but still did not work for me.
Some comments above i read that it was a mootools bug which David was going to fix but after that he did not write on this post anymore :-(
regards
blady
John
Is it possible to download the whole source, ready for use? I am having some trouble integrating everything since I’m just a beginner at coding.
Chris
Hi,
I have a question.
I have just tried the Mootools version of this script, but it doesn’t seem to work…
It displays the update database confirmation, but the data in my table isn’t changed, even when I push the submit button…
Where should I put the “header mysql/php” code ?

Johnny
The exact same thing is happening to me, except with the Jquery version. I am putting everything in the same file, and I’m not sure if that’s correct. I think everything is set up correctly… Help!

Cary Siegler
Does anyone have any ideas on how this can be viewed/used on a Iphone/Ipad?
Ian
jQuery list not working in IE9, just thought I’d let you know
Christian Houmann
Maybe I’m oldfashion, but I am using classic ASP and VBScript. Is it possible to get this to work in ASP? It seems this script is exactly what I need (sorting a list of photos by dragging, and then saving the new order in a database (Access)
Jack Barber
Great post David – just what I needed for a CMS GUI. I’ve been using jQuery for a while now but now really approached many of it’s more ‘complex’ features – it’s great to have a good example to work through.
Nick
This is a lifesaving script. After pasting in the code as laid out above it worked like a charm – except that I kept getting an intermittent “No Database Selected” error. And this morning that’s all I’m getting. I think perhaps it’s something to do with the line:
$result = mysql_query($query,$connection) or die(mysql_error().':
which appears in both the header PHP and body PHP block – but why would it be intermittent? The only PHP I added was the standard Dreamweaver call to the database at the top of the page:
My limited PHP knowledge has led me to another brick wall – any guidance would be much appreciated. Thanks.
Nick
Sorry – fixed it – works like a charm again!
Johnny
By default, there is a 56px wide placeholder that is set to visibility:hidden. How do I override this styling? I want my placeholder to be 100px wide with a white background and a dashed border.
Thanks for the help, awesome script!

Johnny
I figured out the fix for the placeholder. Now I just need to figure out how to actually send the updated values to the database!

Alex
Can’t get it working in IE8. :( With Mootools the elements can’t be moved. With jquery it looks better, but it doesn’t save it to the database.
In FF, Chrome and Opera i don’t have any problems.
IE’s error-message only says “console” is undefined.
richard oreilly
Looks like it’s working but when you refresh it changes the order back to what it was. Should there be an initial record in auto_sort or is it cool to be empty? Thanks in advance.
also this is printing below my submit button:
on form submission if(isset($_POST['do_submit'])) { split the value of the sortation $ids = explode(‘,’,$_POST['sort_order']); run the update query for each id foreach($ids as $index=>$id) { $id = (int) $id; if($id != ”) { $query = ‘UPDATE test_table SET sort_order = ‘.($index + 1).’ WHERE id = ‘.$id; $result = mysql_query($query,$connection) or die(mysql_error().’: ‘.$query); } } if($_POST['byajax']) { die(); } else { $message = ‘Sortation has been saved.’; } }
richard oreilly
full code! http://pastebin.com/d7LGy6yZ what stupid mistake am i making?
Nathan
Is there any way to revert the sort?
Say if users drop li into prohibited area (eg. the top), it will alert user and move that li back to where it was.
Andrew
How would i modify this to omit the submit button and have it automatically update the database on a drop event?
Andrew
Also, with Jquery, can we substitute the li elements with div span or img?
Edgar Serra
Simple and functional!
Congrats guy =p
John Hardie
Is there anyway to make it so you can delete a record?

David Walsh
Yep, you could combine this with:
http://davidwalsh.name/animated-ajax-jquery
http://davidwalsh.name/animated-ajax-record-deletion-mootools

Ethan Chetan
Hi,
The jQuery or MooTools both examples are not working in Apple iPad. It is working fine in all browsers. Can you help me?
Alessandro Rodrigues
Don´t work in IE, any idea???
IE’s error-message only says “console” is undefined.
jem cook
Hi David,
I’ve been trying to implement the jQuery version of this with textareas inside li tags because I want to be able to edit the content as well as reorder the sequence. It’s working beautifully in terms of drag and drop but I can no longer focus on the textarea content. Any chance you have an ‘idiot’s guide’ solution to this? I’m afraid I’m completely new to jQuery and javascript so a blow by blow account for restoring focus would be brilliant.
achim appel
hi david, it doesn’t work in mootools 1.4, isnt it? any idea to fix it?
Osama
Hi guys,
I have a problem when using jQuery for sorting items with Google Chrome.
The problem is, when I’m trying to move an item (li) up or down so I can sort it in the order I want, the item continuously keep moving down without any control.
That’s problem happened only with Google Chrome browser only.
Please guys try to help me :)
Thanks
Paul
I used the mootools script last year, and it worked great – however I’ve just gone back to use the script again and where i’ve embeded ‘open in new window’ but not with a single left click. Help – I need to use it again very soon! Thanks
Paul
I’ll try that again…
I used the mootools script last year, and it worked great – however I’ve just gone back to use the script again and where i’ve embeded <a href tags they no longer work in Firefox (but still work on other browsers and ff 3.6) the link still work with a right click… ‘open in new window’ but not with a single left click. Help – I need to use it again very soon! Thanks
AI
Hi
im trying to display the serial numbers on the right side. check script. issue is it keeps displaying the number on left side
echo '',$item['title']." - ".$count,'';
output:
1- blah blah blah
instead of:
blah blah blah – 1
Kriscd
Hi…
It’s possible to drag a rows inside a html table?
If no there is a roadmap to do this with mootools?
I mean…

cell 1cell 2cell 3cell 4 cell 1cell 2cell 3cell 4 cell 1cell 2cell 3cell 4
many thanks?
karthik
could you put the code for jsp to do this instead of php………..

Be Heard!

Share your thoughts without being a jerk! And wrap your code in <code> tags, f00!

Name*:
Email*:
Website:

Notify me of followup comments via e-mail