Use JQueryUI Sortable to Manipulate Sort Order in a Database Table

This article illustrates one of the many methods of using JQuery Sortable to update the sort order in a database table. Examples are provided using PHP and MySQL but the theory can be applied to any database and server side programming language.

JQueryUI is a JavaScript library that can be used on any Hosting.com platform service. You can download the latest JQuery library with the sortable module from JQueryUI.com.

Example JavaScript Code for JQueryUI Sortable

Below is the Javascript code required to make a list sortable and process a callback function each time the list order has changed.

<script type="text/javascript" src="jquery-1.4.4.min.js"></script>
<script type="text/javascript">
$(function() {
 $( "#sortable" ).sortable({
   update: function(event,ui)
        {
          //create an array with the new order
          var order = $( "#sortable" ).sortable('toArray');
          for(var key in order) {
          var val = order[key];
          var part = val.split("_");
          //update each hidden field used to store the list item position
          document.getElementById("order"+part[1]).value = key;
           }
        }
  });
});

The code above captures the new order and updates hidden html fields that can be used to update the database when the form is submitted. It works by using the .sortable('toArray') feature to take the list item (li) ids and generate a JavaScript array. The array keys represent the new list order from 0 to the number of items in your list.

Consider a sortable list that looks like this:

In the example above the third item has been moved to the second position. The resulting array would look like this: order[0] = sort_1, order[1] = sort_3, order[2] = sort_2

While looping through the array we extract the keys and use them to update each hidden field value with the new position. 

Example PHP Code to Generated the Form

The following is the PHP used to generate the form needed to support this method. First we select our data from the database. Then we loop through the resulting rows dynamically generating the HTML for our list items and form fields with some very specific ids.  The generated item ids must be in the same format used in our JavaScript code above.

#select your list from a database
$sql = 'SELECT `id`,`name`,`order` FROM sometable ORDER BY `order`';
if($results = mysql_query($sql)){
  
#loop through the results to generate a sortable html form
echo '<form action="" method="post">
       <fieldset>
        <ul id="sortable">';
while($row = mysql_fetch_row($results)) 
 {
 $id = $row[0];
 $name = $row[1];
 $order = $row[2];
         
 echo '<li class="ui-state-default" id="sort_'.$id.'">          
        <span class="ui-icon ui-icon-arrowthick-2-n-s"> </span>
         '.$name.'
 <input type="hidden" id="order'.$id.'" name="order_'.$id.'" value="'.$order.'" />
 <input type"hidden" name="ids[]" value="'.$id.'" />
 </li>';
 }
echo '</ul>
     <input type="submit" name="submit" value="Submit" />
     </fieldset>
     </form>';
 } 

Example PHP Code to Process the Form and Update MySQL

When the form is submitted we need to parse the post data and update our MySQL table.

#process the form if it was submitted
if(isset($_POST['submit'])) {
foreach($_POST['ids'] as $id)
     { 
         $order = $_POST['order_'.$id];
          $sql = 'UPDATE sometable SET order = '.$order.' WHERE id = '.$id;
          if(!mysql_query($sql)) { 
                #an error can be handled here
          } 
          
     } 
} 

Be sure to add input validation and error handling before putting this into production.