How to create a drag and drop sortable table of data

Drag and drop is where it's at; it's what the cool kids are doing. Here's how to create a drag and drop sortable list of data using PHP and JavaScript.

1. Create the table of data.

CREATE TABLE `users` (
	`userId` int(11) NOT NULL auto_increment,
	`fname` varchar(255) collate latin1_general_ci NOT NULL,
	`lname` varchar(255) collate latin1_general_ci NOT NULL,
	`sort` tinyint(4) NOT NULL default '1',
	PRIMARY KEY (`userId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

2. Create an HTML table of users and wrap it in a form.

Each row should have a hidden input that includes the userId. Somtimes it's necessary to have the page refresh after the user has drag and dropped a row. In that case, make sure the hidden input "reloadRequired" is set to "1".

<form id="sortableForm" method="post" action="http://www.moxietechnoxy.com/sortable.php">
	<input name="reloadRequired" id="reloadRequired" value="1" type="hidden">
	<div class="tableWrap">
		<table>
			<thead> 
				<tr>
					<th>First name</th>
					<th>Last name</th>
					<th><input name="placeholder" type="hidden"></th>
				</tr>
			</thead> 
			<tbody>
				<tr class="odd">
					<td>Jane</td>
					<td>Doe</td>
					<td><input name="itemId-1" type="hidden"></td>
				</tr>
				<tr class="even">
					<td>John</td>
					<td>Smith</td>
					<td><input name="itemId-2" type="hidden"></td>
				</tr>
			</tbody>
		</table>
	</div>
</form>

3. Install jQuery's Sortable plugin.

After including all the necessary jQuery files, create and include a file called sortable.js that looks like this:

$(document).ready(function() {
 
	//Allow the table rows to be drag and drop sortable.
	$("form#sortableForm div.tableWrap table tbody").sortable({
  
		helper: function(e, ui) {
			ui.children().each(function() {
				$(this).width($(this).width());
			});
			return ui;
		},  
  
		sort: function(e, ui) {
  
			/* When using a table for sorting, the placeholder tr must have cells, or it
			* is incorrectly formed. Make sure the placeholder row always has cells. */
			var numCells = $("tr.ui-state-default:first").children().size();
   
			if ($("tr.ui-state-highlight").children().size() !== numCells) {
				i = 1;
				while (i <= numCells) {
					$("tr.ui-state-highlight").append("");
					i++;
				}
			}
		},

		stop: function(e, ui) {
   
			/*Preserve alternating rows.*/
			if ($("form#sortableForm div.tableWrap table tr.odd").length) {
    
				//Loop over each row and reset the odd/even classes.
				var c = 0;
				$("form#sortableForm div.tableWrap table tbody tr").each( function() {
     
					if (c % 2) {
						$(this).removeClass("odd").addClass("even");
					} else {
						$(this).removeClass("even").addClass("odd");
					}
     
					c++;
				});    
			}
   
			//Submit the form so the table row positions will be kept on page reload.
			var callback_url = $('form#sortableForm').attr('action');
   
			//Loop over all form fields and create an object of name => value.
			var form_fields = new Object();
			$("form#sortableForm input").each (function() {

				//Append to the object.
				form_fields[$(this).attr("name")] = $(this).attr("value");
			});
   
			//Perform an ajax request and pass all the form variables in.
			//Get the meta data for this form handler type.
			$.ajax({
				type: "POST",
				data: form_fields,
				url: callback_url,
				dataType: "html",
				error: function(XMLHttpRequest, status, error){
					alert("XML HTTP Request:" + XMLHttpRequest);
					alert("Status:" + status);
					alert("Error:" + error);
				}, 
				success: function(html) {

					//Some forms require a refresh. 
					//Refressh if necessary.
					if ($("#reloadRequired").attr("value") == "1") {
						window.location.reload();
					}
				}
			});    
		},
  
		placeholder: 'ui-state-highlight'
	}).disableSelection();
 
});

4. Create a file called sortable.php.

When the user drops a row, an AJAX callback to the server is fired (to sortable.php) that posts all the form values and saves them to the database in their new sort order.

//Get the form values.
$post = $_POST;

//We have an array of item id's. There are received in their new sort order.
//Save the new sort order to the database.
$items = array();
foreach($post as $post_name => $post_value) {   
 
	//Is this a field value?
	if (strstr($post_name, 'itemId-') !== false) {
		$items[] = $post_value;
	}
}

//Save the new sort order.
$sort = 0;
foreach($items as $itemId) {
	execute("update users set sort=$sort where userId = $itemId");
	$sort++;
}

5. You're done!

You now have a user-friendly, fast and easy way to allow users to sort table rows.