Drag and Drop, Autosave Position on Database

I’ve written this tutorial way back in 2012, but I still find this code helpful.

There are lots of codes for drag and drop all over the net, but this code is the most simple and easy to use for me. I used this code on some projects I created on my OJT days. I just forgot to save the link where I got this code but these scripts truly help me.

This drag and drop UI used JQuery UI, PHP, and MySQL Database. This simple program autosaves the position of the item once you drag it in a certain position. This is not my own code as I’ve said I just got this from the internet, but I just made some sort of modification needed in my project. This drag and drop works in IE, Chrome, and Mozilla but the autosave function doesn’t work in IE. The database table, only needs three(3) important fields id, title, and position.

Here is some code inside the magic of drag and drop, I will try to explain some of the important code in the way how I understand it. ( Just correct me if I’m wrong.)

$(function() {
    $(".unstyled").sortable({ opacity: 0.6, cursor: 'move', update: function() {
 var order = $(this).sortable("serialize") + '&action=updateRecordsListings'; 
 $.post("#updateDB", order, function(theResponse){}); 
    }
    });
});

The above script is the one responsible for moving and calling the function to save the position in the database.

You need to have <div id="updateDB"></div>as you notice at the javascript above $.post("#updateDB", order, function(theResponse){}); the #updateDB the id at the div tags. Inside the div tags are the PHP script needed to save the position of the item. The code is below:

<div id="updateDB">
<?php
$action = mysql_real_escape_string($_POST['action']);
$updateRecordsArray = $_POST['recordsArray']; 
if($action == 'updateRecordsListings'){ 
    $listingCounter = 1; 
    foreach ($updateRecordsArray as $recordIDValue) {
        $query = "UPDATE tbl_dragdrop SET position = " . $listingCounter . " WHERE id = " . $recordIDValue;
        mysql_query($query) or die('Error, insert query failed'); 
        $listingCounter += 1;
     }
 }     
?>
   <ul class="unstyled">
            <?php
            $select = $mysqli->query($sql);
            while($row = $select->fetch_assoc()){
                echo '<li id="recordsArray_'.$row['id'].'"><div class="dragdrop"><strong><span role="textbox" contenteditable>'.$row['title'].'</span></strong></div></li>';
            }
            
            ?>
        </ul>
</div>

Did you notice the $action == 'updateRecordsListings' on the if condition? and have you also noticed the '&action=updateRecordsListings' on javascript? On the javascript, once you drag an item the action is set to updateRecordsListings. And on the PHP side the if condition checks if the action is equal to updateRecordsListing.

Then the foreach loop sets the row id as $recordIDValue is used to save the new position of an item in a query as you see in the next line of code. If you are confused about where $updateRecordsArray = $_POST['recordsArray']came from, actually I don’t know also (hehe), as I understand $_POST['recordsArray']; came from the id of li tags plus the id ($row['id']) of the item which is separated by underscore “_”.It is still magic for me how the get the id of the item and store it in recordsArray. The $listingCounter is the new position of the item. It is incremented in foreach loop.

The .unstyled at $(".unstyled").sortable({...})is the class at ul tags.

And some of the code is SQL queries, I will not explain those. I hope you understand my explanation.

You can download the demo files here.

https://github.com/khatzie/drag-drop/

References:

https://jqueryui.com/sortable/

Just leave a comment if you have any questions or violent reactions. Enjoy Coding! 🙂