I’ve had a lot of response to the jQuery sortable list post I wrote a while back, and a lot of them ask how to send the results to a database. I’ve often said the best thing to do would be to update it by AJAX, but I’ve never really explained how. So here it is!

There was a bug a few people noticed with the new jQuery UI a short while ago that stopped the update callback working. That bug has since been rectified and all is kosher again. I have since updated the example to the latest jQuery and UI downloads. The examples and downloads have also been updated accordingly.

Step one: Create the page with the sortable, and the CSS

(described on my previous post) We have however altered it slightly, so here is the updated code (without any css, the css is in the sourcecode)
Include your javascript files, you’ll need jQuery and the jQuery UI files (sortable)

<script type="text/javascript" src="jquery-1.3.2.min.js"></script> 
<script type="text/javascript" src="jquery-ui-1.7.1.custom.min.js"></script> 
<link rel='stylesheet' href='styles.css' type='text/css' media='all' />

Then you’ll need to create your Javascript function. We’re going to listen for the list to be updated, then call the php page, pass it the new order by GET and update the #info div with whatever the PHP file prints out:

<script type="text/javascript"> 
// When the document is ready set up our sortable with it's inherant function(s) 
$(document).ready(function() { 
  $("#test-list").sortable({ 
    handle : '.handle', 
    update : function () { 
      var order = $('#test-list').sortable('serialize'); 
      $("#info").load("process-sortable.php?"+order); 
    } 
  }); 
}); 
</script>

Now here’s the HTML to output the list etc. etc:

<pre> 
    <div id="info">Waiting for update</div> 
</pre> 
<ul id="test-list"> 
  <li id="listItem_1"> 
    <img src="arrow.png" alt="move" width="16" height="16" /> 
    <strong>Item 1 </strong>with a link to <a href="http://www.google.co.uk/" rel="nofollow">Google</a> 
  </li> 
  <li id="listItem_2"> 
    <img src="arrow.png" alt="move" width="16" height="16" /> 
    <strong>Item 2</strong> 
  </li> 
  <li id="listItem_3"> 
    <img src="arrow.png" alt="move" width="16" height="16" /> 
    <strong>Item 3</strong> 
  </li> 
  <li id="listItem_4"> 
    <img src="arrow.png" alt="move" width="16" height="16" /> 
    <strong>Item 4</strong> 
  </li> 
</ul> 
<form action="process-sortable.php" method="post" name="sortables"> 
  <input type="hidden" name="test-log" id="test-log" /> 
</form>

Step two: Create the PHP to handle the serialized list jQuery has created

The PHP page will simply take the url it’s been handed and pick out the items from the array. You can then manipulate your database with the new values, or just format however you want. The code could look like this:

&lt;?php 
/* This is where you would inject your sql into the database 
but we're just going to format it and send it back 
*/ 
foreach ($_GET['listItem'] as $position => $item) : 
  $sql[] = "UPDATE `table` SET `position` = $position WHERE `id` = $item"; 
endforeach; 
print_r ($sql); 
?&gt;

You could then happily run each sql statement.

As always, I’d love the feedback, it’s great for improving the example etc. Enjoy!

http://wil-linssen.com/musings/entry/extending-the-jquery-sortable-with-ajax-mysql/

http://www.webresourcesdepot.com/dynamic-dragn-drop-with-jquery-and-php/

Leave a comment