How do we allow users to reorder the rows of an HTML table and then save those changes back to a table in a MySQL database? This post will explain how to do it in two steps:

1. Enable table drag and drop functionality with jQuery UI
2. Write any changes the user makes back to the MySQL table using a PHP script

If you want your users to be able to sort a table by clicking on a column header, then check out this article instead.

Drag and Drop

The most user-friendly way of reordering table rows is to enable drag and drop functionality so that the user can simply drag a row to a new position within the table.

You can also use native HTML5 to do this, but jQuery UI is simpler and will handle the quirks of different web browsers for you.

Include jQuery and jQuery UI

You will need to include the jQuery and jQuery UI libraries in the page. You can either pull the libraries from a content provider like Google, or download the latest versions and host them on your own website.

Use Google

Links to the libraries that Google hosts can be found here.

Include the libraries at the bottom of the page, like so:

<!-- jQuery -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
<!-- jQuery UI -->
<script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.js"></script>

Download

If you prefer the download the libraries then head over to the jQuery and jQuery UI download pages. You can reduce the size of the jQuery UI file if all you need is the sortable function. Uncheck the “Toggle All” option under Components, and then check off “Sortable” under Interactions. The other components which Sortable depends on will automatically be selected.

Download the files, extract them, and upload the jquery-[version].min.js and jquery-ui.min.js files into your web server’s JavaScript folder.

Include the libraries at the bottom of the page, like so:

<!-- jQuery -->
<script src="/javascript/jquery-2.1.4.min.js"></script>
<!-- jQuery UI -->
<script src="/javascript/jquery-ui.min.js"></script>

Sample Table

Here’s the HTML code we’ll use for the example table:

<table id="table1">
 <thead>
  <tr>
   <th>First Name</th>
   <th>Last Name</th>
   <th>Birth Year</th>
  </tr>
 </thead>
 <tbody>
  <tr>
   <td>Bill</td>
   <td>Smith</td>
   <td>1965</td>
  </tr>
  <tr>
   <td>Mary</td>
   <td>Adams</td>
   <td>1970</td>
  </tr>
  <tr>
   <td>James</td>
   <td>Hart</td>
   <td>1950</td>
  </tr>
  <tr>
   <td>Jesse</td>
   <td>Phillips</td>
   <td>1991</td>
  </tr>
 </tbody>
 <tfoot>
</table>

Make the Table Sortable

Let’s use jQuery UI’s sortable() function to make the table sortable via drag and drop. Call the sortable function on the tbody element of the table:

$(document).ready(function(){
    $( "tbody" ).sortable();
});

This code can be placed at the bottom of your web page (in which case <script> tags are needed), or it can be made part of a separate JavaScript file. If you add it directly to the page, make sure this code comes after your jQuery & jQuery UI script statements. Otherwise, your browser won’t recognize the jQuery $() function.

Refresh the page and you should now be able to drag and drop the rows of the table into the desired order. To see it in action, check out this sample table.

Save the New Order to MySQL

That’s great, but how do we save the order of newly arranged table rows back to the database?

Before proceeding, make a backup of your database before attempting to update it with these scripts.

Let’s expand the $( “tbody” ).sortable(); function we called earlier.

You can use the built-in jQuery UI serialize method to create an array of the sortable elements (in this case, <tr> elements) and send it back to the server via an AJAX POST. This code is placed within the update event, which occurs whenever the user finishes moving an item.

$( "tbody" ).sortable({
    // Cancel the drag when selecting contenteditable items, buttons, or input boxes
    cancel: ":input,button,[contenteditable]",
    // Set it so rows can only be moved vertically
    axis: "y",
    // Triggered when the user has finished moving a row
    update: function (event, ui) {
        // sortable() - Creates an array of the elements based on the element's id. 
        // The element id must be a word separated by a hyphen, underscore, or equal sign. For example, <tr id='item-1'>
        var data = $(this).sortable('serialize');
        
        //alert(data); <- Uncomment this to see what data will be sent to the server

        // AJAX POST to server
        $.ajax({
            data: data,
            type: 'POST',
            url: 'refresh_order.php',
            success: function(response) {
	        // alert(response); <- Uncomment this to see the server's response
	    }
        });
    }
});

Make sure the id within each <tr> element is uniquely identified and in a format similar to “item_100”. As per jQuery UI’s online documentation: You can use an underscore, equal sign or hyphen to separate the set and number. For example "foo=1", "foo-1", and "foo_1" all serialize to "foo[]=1".

On the server side, we use a PHP script (refresh_order.php) to loop through the items and update the corresponding row with its new position:

<?php
// Your code here to connect to MySQL server
    $i = 0;
    foreach ($_POST['item'] as $value) {
        // Sanitize the input
        $id = mysqli_real_escape_string($conn, $value);
        // Build statement:
        $sql = "UPDATE table SET position = $i WHERE id = $id";
        // Execute statement:
        if(mysqli_query($conn, $sql)) {
	    echo "Record modified successfully.";
        } else {
	    echo "ERROR: Could not execute $sql. " . mysqli_error($link);
        }
        $i++;
    }

// Close MySQL connection
?>

The new order will be saved to the database when you drag a row to a new position.

References

This blog post wouldn’t have been possible without information from many other sources:

http://stackoverflow.com/questions/2072848/reorder-html-table-rows-using-drag-and-drop

http://jqueryui.com/sortable/

http://stackoverflow.com/questions/15633341/jquery-ui-sortable-then-write-order-into-a-database

http://stackoverflow.com/questions/2995329/if-i-do-jquery-sortable-on-a-contenteditable-items-i-then-cant-focus-mouse/6058668#6058668