Course lectures : 1 of 1

Sort table rows with jQuery using Drag and Drop method

18th August 2011

sort table rows jqueryjquery table row sortingjquery tutorialjquery dag and dropjquery course

Today we will have a look at how we can update order of the records displayed in the table format using Drag and Drop method.

First we need to download the following files and put them inside of the js folder:

Inside of the js folder create a new file and call it core.js.

Now create a new folder called css and a file inside of it called core.css.
Open newly created file and copy and paste the following css definitions to it:

* {
body {
	padding:30px 0;
#wrapper {
	text-align: left;
	margin:0 auto;
.tbl_repeat {
.tbl_repeat th,.tbl_repeat td {
	padding:5px 10px;
.tbl_repeat th {
	border-top: solid 1px #aaa;
	border-bottom: solid 1px #aaa;
.tbl_repeat td {
	border-bottom: dashed 1px #aaa;

You can now save and close the core.css file.

Now open your favorite application for interacting with MySQL database and type the following sql statement in order to create a new database and put some records for testing:

CREATE TABLE `books` (
	`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
	`title` varchar(255) NOT NULL,
	`price` decimal(6,2) NOT NULL DEFAULT '0.00',
	`category` varchar(100) DEFAULT NULL,
	`author` varchar(200) DEFAULT NULL,
	`qty` tinyint(11) NOT NULL DEFAULT '0',
	`rating` tinyint(4) NOT NULL DEFAULT '0',
	`ratings` int(11) unsigned NOT NULL DEFAULT '0',
	`votes` int(11) unsigned NOT NULL DEFAULT '0',
	`order` int(11) unsigned NOT NULL,

INSERT INTO `books` VALUES(1, 'Logo Design Now (Midi Series)',
	17.49, 'Design', 'Julius Wiedemann', 10, 0, 0, 0, 1);
INSERT INTO `books` VALUES(2, 'PHP and MySQL Web Development',
	17.70, 'Web Programming', 'Luke Welling', 5, 0, 0, 0, 4);
INSERT INTO `books` VALUES(3, 'Composition: From Snapshots to Great Shots',
	9.00, 'Photography', 'Laurie Excell', 11, 0, 0, 0, 7);
INSERT INTO `books` VALUES(4, 'From Still to Motion: A Photographer''s
	Guide to Creating Video rnwith Your DSLR', 18.48,
	'Photography and Video', 'James Ball', 3, 0, 0, 0, 2);
INSERT INTO `books` VALUES(5, 'Sams Teach Yourself HTML5 Mobile Application
	Development rnin 24 Hours', 22.94, 'Web Programming', 'Jennifer Kyrnin',
	8, 0, 0, 0, 3);
INSERT INTO `books` VALUES(6, 'Customised Mobile Application Development:
	Using XHTML rnParser for Google Android Platform', 37.40, 'Web Programming',
	'Kaustubh Duraphe', 7, 0, 0, 0, 5);
INSERT INTO `books` VALUES(7, 'Mobile Commerce Application Development', 55.05,
	'Web Programming', 'Lei-Da Chen', 1, 0, 0, 0, 6);

Now that database is ready, in the root of your site create a file called index.php and open it for editing. Copy and paste the following page structure to it:

<html lang="en">
	<meta charset="utf-8" />
	<title>Draggable table row</title>
	<meta name="description" content="Draggable table row" />
	<meta name="keywords" content="Draggable table row" />
	<link href="/css/core.css" rel="stylesheet" type="text/css" />
	<!--[if lt IE 9]>
	<script src=""></script>

<section id="wrapper">

	<table cellpadding="0" cellspacing="0" border="0" class="tbl_repeat">
			<tr id="order_1">


<script src="/js/jquery-1.6.2.min.js" type="text/javascript"></script>
<script src="/js/jquery.tablednd_0_5.js" type="text/javascript"></script>
<script src="/js/core.js" type="text/javascript"></script>

Make sure you replace the name of the jquery file to match the version you've downloaded.

Right at the top of the index.php file, before:


put the following php code to get the book records from our database:

try {

	// new pdo connection
	$objDb = new PDO('mysql:host=localhost;dbname=books', 'root', 'password');
	$objDb->exec("SET CHARACTER SET utf8");

	// get all records
	$sql = "SELECT *
			FROM `books`
			ORDER BY `order` ASC";
	$statement = $objDb->query($sql);
	$results = $statement->fetchAll(PDO::FETCH_ASSOC);

} catch(Exception $e) {

	echo 'There was a problem with the database';


Above we've created a PHP PDO connection (make sure you replace the connection parameters with the right ones for your environment), set the character set to utf-8 and then simply get all records from the books table.

Now identify the block which reads:

	<tr id="order_1">

and replace it with:

<?php if (!empty($results)) { ?>
	<?php foreach($results as $row) { ?>
	<tr id="order_<?php echo $row['id']; ?>">
		<td><?php echo $row['title']; ?></td>
		<td><?php echo $row['author']; ?></td>
	<?php } ?>
<?php } ?>

With the above we are now displaying all records populated from the database.

You can now save and close the index.php.

Open core.js file and type the following:

$(function() {

	$(".tbl_repeat tbody").tableDnD({
		onDrop: function(table, row) {
			var orders = $.tableDnD.serialize();
			$.post('/mod/order.php', { orders : orders });


With these few lines of code we are simply calling the tableDnD function and perform some operation when the onDrop method is executed.

First we create a variable called orders and we assign the collected ids of the table rows after the sorting has been completed.

The value assigned to the variable will look something like this:


Next we are sending this value to the file order.php stored inside of the mod folder (which we'll create next) using jQuery's post() method.

Now create a new folder called mod and a new file within this folder called order.php.

Open the new file and start with checking whether the $_POST['orders'] has been set - and if not, echo the json array with the index error set to true:

if (isset($_POST['orders'])) {

} else {
	echo json_encode(array('error' => true));

Inside of the isset statement start with converting the received string to PHP array with the following:

$orders = explode('&', $_POST['orders']);

Using the explode function with & symbol as delimiter we have converted the string into array.

We can create another, empty array and loop through our newly created $orders array:

$array = array();

foreach($orders as $item) {
	$item = explode('=', $item);
	$item = explode('_', $item[1]);
	$array[] = $item[1];

As you can see we have exploded each item of the initial array two more times - first using = then _ symbol as delimiter to get the id of the record we are currently looping through.

We are now ready to update our records:

try {

	$objDb = new PDO('mysql:host=localhost;dbname=books', 'root', 'password');
	$objDb->exec("SET CHARACTER SET utf8");

	foreach($array as $key => $value) {
		$key = $key + 1;
		$sql = "UPDATE `books`
			   SET `order` = ?
			   WHERE `id` = ?";

		$objDb->prepare($sql)->execute(array($key, $value));

	echo json_encode(array('error' => false));

} catch(Exception $e) {

	echo json_encode(array('error' => true));


Above we are using the try / catch statement to check whether the code has been executed successfully - if so, then we echo json array with error index set to false, otherwise we set it to true.

We don't really use these responses for anything, but if you had to debug the application at any time - this will give you a rough idea of what might be going wrong.

Finally - it's time to test it, as our tutorial is now completed.
Any questions / problems - submit a comment below.


If you'd like to include code example please wrap your code within <pre><code> tags. See example here

function fullName(firstName, lastName)
    return firstName + ' ' + lastName;