Scroll page back to the top

Sort table rows with jQuery using Drag and Drop method

Download Exercise FilesDemo
 
 

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:

* {
	margin:0;
	padding:0;
	border:none;
	outline:none;
	color:#333;
	font-size:12px;
}
body {
	padding:30px 0;
	text-align:center;
	font-family:Arial,Verdana,Sans-serif;
}
#wrapper {
	text-align: left;
	width:800px;
	margin:0 auto;
}
.tbl_repeat {
	width:100%;
}
.tbl_repeat th,.tbl_repeat td {
	padding:5px 10px;
}
.tbl_repeat th {
	background:#eee;
	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,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

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:

<!DOCTYPE HTML>
<html lang="en">
<head>
	<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="http://html5shiv.googlecode.com/svn/trunk/html5.js"></script>
	<![endif]-->
</head>
<body>

<section id="wrapper">

	<table cellpadding="0" cellspacing="0" border="0" class="tbl_repeat">
		<thead>
			<tr>
				<th>Title</th>
				<th>Author</th>
			</tr>
		</thead>
		<tbody>
			<tr id="order_1">
				<td></td>
				<td></td>
			</tr>
		</tbody>
	</table>

</section>

<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>
</body>
</html>

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:

<!DOCTYPE HTML>

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

<?php
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:

<tbody>
	<tr id="order_1">
		<td></td>
		<td></td>
	</tr>
</tbody>

and replace it with:

<?php if (!empty($results)) { ?>
<tbody>
	<?php foreach($results as $row) { ?>
	<tr id="order_<?php echo $row['id']; ?>">
		<td><?php echo $row['title']; ?></td>
		<td><?php echo $row['author']; ?></td>
	</tr>
	<?php } ?>
</tbody>
<?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:

[]=order_2&[]=order_1&[]=order_5&[]=order_4&[]=order_6&[]=order_7&[]=order_3

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:

<?php
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.

 
 
 

Discussion (42 comments)

  • Jasper

    Jasper on Wednesday, 19th October 2011

    Wow! thank you for this tutorial! Its awesome!

    Reply

  • Sergio

    Sergio on Monday, 28th November 2011

    Thanks a lot for this tutorial. Really helped me a lot.
    Do you know how to or where can I find the same script but with another db table? For example, I want to use one table on the left with "active items" and one on the right with "non active items"
    And I want to be able to sort the active ones.

    Thanks a lot again.

    Reply

  • Sebastian Sulinski

    Sebastian Sulinski : @designtutorials on Monday, 28th November 2011

    Hi Sergio,
    You should be able to do it using the same approach as in this tutorial. If I understand well, the only thing you have to do is to create two tables next to each other, fetch the records and feed them into both of the tables accordingly.

    Now, make sure that the table that you want to have sorting by dragging applied has the tbody tag with some class - say sort like so:

    <tbody class="sort">
    

    Then the last thing is to update the calling of the method, which would now be:

    $(".tbl_repeat tbody.sort").tableDnD({
    

    This way sorting is only applied to the tbody tag that has sort class assigned. The other ones will be ignored.

    I hope this answers your question.

    Reply

  • Sergio

    Sergio on Monday, 28th November 2011

    Thanks for the answer. I didnĀ“t explain well.
    What I want to do is to mix the items in two columns. For example:

    table books. Items shown in home | table books. Items NOT shown in home

    book1 | book2
    book3 | book4
    book5

    And I want to put book2 in the 1st column (in home) and change a value in a query (UPDATE books SET home = 1 WHERE id = 2)

    like this one: http://jqueryui.com/demos/sortable/#connect-lists

    Thanks a lot

    Reply

  • Sebastian Sulinski

    Sebastian Sulinski : @designtutorials on Monday, 28th November 2011

    The link you've provided is probably the best option, but as you can see it uses unordered list ul rather than table.

    It would be really difficult to achieve something similar due to the table structure. My suggestion would be to have it built with ul and your link shows pretty well how to use it.

    Reply

  • Sergio

    Sergio on Monday, 28th November 2011

    Thanks a lot. And again, great work!

    Reply

  • Thales

    Thales on Monday, 28th May 2012

    Hello !! Thank you for this beautiful tutorial. I have serius problem with the file order.php(I think...)
    Can i use standar db connection to update the mysql table instead of PDO.
    I successfully drag n drop table rows but i cannot save the order in DB.

    Reply

  • Sebastian Sulinski

    Sebastian Sulinski : @designtutorials on Wednesday, 30th May 2012

    Hi Thales,
    Absolutely - you can use any connection type you like - I've used PDO because it's easier to use and object oriented plus it is safer then the preceding methods, but if you prefer the standard or mysqli approach you'll be absolutely fine.

    Reply

  • Thales

    Thales on Thursday, 31st May 2012

    Thank you for your quick reply.
    I tried another connection but i have update problem
    Can you give me a sort example beacuase I am newbie.

    Thank you for your time!

    Reply

  • Manny

    Manny on Sunday, 3rd June 2012

    Hello Sebastian,

    I tried to follow your tutorial, but could not get the drag and drop going, maybe the versions of the js. So, I downloaded your sample files. Everything works, except when I make the changes they are not save, as in your demo. In your demo, when I drag the drop and refresh the changes are kept. When I do the same on mine, the order goes back to original. Would you have any idea why this would be happening?

    Thank you,
    Manny

    P.S. I have looked around for days, and your tutorial is the only one I have found that actually worked!

    Reply

  • Sebastian Sulinski

    Sebastian Sulinski : @designtutorials on Sunday, 3rd June 2012

    Hi Manny,
    It is most probably the problem with database connection - did you update all connection parameters to match your database set up?

    The easiest way to find out what's wrong is to use Firefox and Firebug add-on. Once you've installed it open the Firebug Console tab and drag the row - you'll see the url has been called and if you click on that url you'll see the response, which should indicate what the problem is.

    Reply

  • Manny

    Manny on Sunday, 3rd June 2012

    You are AWESOME Sebastian! I had the password incorrect in order.php (two letters switched).

    I was doing a comparison between your working demo and mine in the Firebug Console, and may I ask, why in my Response tab I see only {"error":false} while in yours shows that and the array, and also in the JSON tab I see just the "error false" while yours again is neatly showing the array?

    Sebastian, thank you for your excellent tutorial and much of your time!

    Best regards,
    Manny

    Reply

  • Sebastian Sulinski

    Sebastian Sulinski : @designtutorials on Sunday, 3rd June 2012

    Hi Manny,
    I'm not sure - are you adding the array to the json response in php file?

    Reply

  • Manny

    Manny on Sunday, 3rd June 2012

    Hello Sebastian,

    I made no changes to the exercise files/code other than altering db connection statement to match my environment. What is important, is that the drag and drop is working. I will continue to figure out how to make my demo to function like yours. When I do, I will post back.

    Thank you for helping me in my continued search for knowledge in PHP, MySQL, JavaScript/jQuery! I really enjoy your tutorials!

    Kindest regards,
    Manny

    Reply

  • Andy

    Andy on Monday, 18th June 2012

    Sebastian,
    Great work. I'm slowly getting back into the web development scene after a decade or so off. This was a great piece and would like to know what license you may have on it? I may like to utilize some of your code/ideas in the future for some potentially commercial work if at all possible.

    Best,
    Andy

    Reply

  • Sebastian Sulinski

    Sebastian Sulinski : @designtutorials on Monday, 18th June 2012

    Hi Andy,
    Thanks - glad you like the tutorial. There is no licensing as to the outcome of the tutorial so feel free to use the code in any project you work on. The only licensing might apply if the tutorial involves some external libraries, plugins etc. so you will have to check those, but the code written by me is all free to use.

    Reply

  • Andy

    Andy on Monday, 18th June 2012

    Thanks so much for you contribution! I will be suggesting ssdtutorials.com to my buds for quality tutorials!

    Thanks again,
    Andy

    Reply

  • Sebastian Sulinski

    Sebastian Sulinski : @designtutorials on Monday, 18th June 2012

    Thanks Andy - appreciate that!

    Reply

  • Djani

    Djani on Wednesday, 1st August 2012

    Hello,
    Is there a way for columns to contain click-able data?I mean, for example, column author which contains authors name , could be a link to some other resource....
    Also, how can I make row that is being dragged highlighted?

    Reply

  • Sebastian Sulinski

    Sebastian Sulinski : @designtutorials on Thursday, 2nd August 2012

    Hi Djani,
    You sure can create a link from any entry - simply add it in your html code.
    With regards to the highlight of the row while dragging - if you check the plugin's page you'll find there is a proparty called onDragClass, which allows you to define the css class you want to apply to the row while dragging. You can create the class in your css like so:

    .highlight {
    	background-color:#900;
    }
    

    Then simply add the onDragClass to your tableDnD list of properties:

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

    Reply

  • Djani

    Djani on Thursday, 2nd August 2012

    Thank you , Sebastian!

    Reply

 
Page 1 of 2
 
 
Add a comment
Add Comment