Efficient Drag and Drop with JQuery, PHP and Mysql

I have recently implemented a very nice drag and drop functionality using jquery ‘s drag and drop interface in combination with php/mysql and ajax. A few minutes ago I came across a post on www.webresourcesdepot.com/dynamic-dragn-drop-with-jquery-and-php/ which does essentially the same thing but in a less efficient manner.

My method consists of only 3 queries to update the sorting columns for all the records. First I latch onto the jquery-drag&drop ‘stop’ event which lets me know when an item has changed position in the ordered list.

The event handler for the stop event parses the ID of the record from the li element’s ID attribute, and the index (position) of the item:

              stop: function (ev, ui){
                        var item_ID = ui.item.attr('id').match(/item-([0-9]+)/).pop();
                        var index = ui.item.index() ;
                            data: {'item_ID': item_ID,
                                    'index': index
                            type: 'POST',
                            success: function (){}

The list has this basic structure:

<ul class="sortable">
	<li id="item-99">Item 99</li>

Later, sort.php makes the necessary changes to the database. Each record has a ‘sort’ column. The sort column is incremented by 10 and the first item has a sort value of 0. This means that decreasing the sort value of a record by 15 would move it above it’s preceding item and adding 15 would move it after its next item. So, the query for reassigning a value to the sort column for a record given its “new” zero-based position in the list is the following:

update itemstable SET sort=(index*10)+if(index*10>sort,5,-5) WHERE id=item_ID

the if condition is necessary because setting the ‘sort’ value of an item with a previous index of 1 (index=1,sort=10) to the first position in the list (index=0) would render its query to:

update itemstable SET sort=(0*10)+if(0*10>10,5,-5)
 WHERE id=item_ID; // sort is now 0+-5=-5, which would move it a spot above the item which had the sort=0 position.

the query sets the sort value of the given record to -5 making it the new first element of the list, followed by the element with sort value of 0 and then the element with sort value of 10,..20,.. and so on. After the sort value of the record has changed, it’s necessary to normalize the sort columns so that the sort sequence begin with 0 and are incremented by 10. That’s done by executing the following query:

SET @F=-1;
UPDATE itemstable SET sort=10*(@F:=@F+1) ORDER BY sort ASC

The query would increment F by 1 and use it to calculate the normalized sort position of each of the records.The first item (which is the item with sort=-5) has its sort value reassigned to 10*(-1+1)=0, the second item (with sort=0) has now a sort of 10*(0+1)=10,.. and so on.

I used PDO and of course a set of custom developed OOP classes which makes my ‘sort.php’ look nice and clean:

// update the sort value of the particular record.

$stmt = $D->UpdateObject('Section', 'sort = (?*10)+if(?*10>sort,5,-5)', 'ID = ?');
$stmt->execute(array($_POST['index'], $_POST['index'], $_POST['section_ID']));

* @var PDO
$Db = System::begin('db');

// normalize the sort values so they begin with 0 and incremented by 10 consecutively.
$Db->query('SET @F=-1, @G=(SELECT parent_ID FROM ' . Section::TABLE . ' WHERE ID = ' . (int)$_POST['section_ID'] . ')');
$Q = $D->UpdateObject('Section', 'sort = 10*(@F:=@F+1)', 'parent_ID = @G', 'ORDER BY sort ASC');

My actual code uses a secondary SQL variable (@G) which I use to store the parent_ID of the items which I’m actually sorting. For example, if you have different lists identified by category_ID or something similar, you could get the category_ID of the affected items by storing the ID into such temporary variable and then using the variable in the Update Query.

  • Thank you Amado for this nice tutorial