Home » Sort table rows with jQuery using Drag and Drop method
Sort table rows with jQuery using Drag and Drop method

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: Latest minified version of jQuery library Table Drag and Drop JQuery plugin 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:ArialVerdanaSans-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(62) 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.

Course Authors


  • Course Level: All
  • Subject: IT and Design
  • Fees: TBA
  • Language: English
  • Certificate: Available
  • Duration:
  • Provider: