• Demos and Downloads!
• MooTools Plugins
• About / Contact

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!

The MySQL Table

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.

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.

Discussion

The Bottom Line