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
| 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 |
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 } ?>- 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.
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; }
Some CSS properties may need to be vendor-prefixed.
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 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 "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.'; }
}
