After publishing ScheduledTweets yesterday, I received e-mails asking "how the drag’n drop & saving the new positions to the database was working".
Drag’n drop generally looks hard-to-apply but it is definitely not by using JavaScript frameworks. Here is, how it is done by using jQuery & jQuery UI:
The Database:
We create a simple database as below:
The most important column in the database is recordListingID which shows us the order of the records.
This feature can be applied to any table by adding such a column to it.
The HTML:
We’ll be using an unordered list that is generated from a PHP query that lists the items according to the recordListingID value mentioned above.
Here it is:
</p>
<div id="contentLeft">
<ul>
<li id="recordsArray_<?php echo $row['recordID']; ?>"> </li>
</ul>
</div>
<p>
The JavaScript:
We will be using jQuery UI’s sortable plugin.
<script type="text/javascript">
$(document).ready(function(){
$(function() {
$("#contentLeft ul").sortable({ opacity: 0.6, cursor: 'move', update: function() {
var order = $(this).sortable("serialize") + '&action=updateRecordsListings';
$.post("updateDB.php", order, function(theResponse){
$("#contentRight").html(theResponse);
});
}
});
});
});
</script>
We made the unordered list inside #contentLeft a sortable item, used the serialize function of jQuery to create the array and posted it to updateDB.php.
The PHP:
After posting the array of "new order of the items" to updateDB.php, we must run a query to update our database that will reflect the last positions of every item:
<?php
require("db.php");
$action = $_POST['action'];
$updateRecordsArray = $_POST['recordsArray'];
if ($action == "updateRecordsListings"){
$listingCounter = 1;
foreach ($updateRecordsArray as $recordIDValue) {
$query = "UPDATE records SET recordListingID = " . $listingCounter . " WHERE recordID = " . $recordIDValue;
mysql_query($query) or die('Error, insert query failed');
$listingCounter = $listingCounter + 1;
}
echo '<pre>';
print_r($updateRecordsArray);
echo '</pre>';
echo 'If you refresh the page, you will see that records will stay just as you modified.';
}
?>
You can see that this is the easiest part. We handled the array as $updateRecordsArray and used it inside a for each statement.
With a new variable named $listingCounter, while the for each statement runs, we have updated the values of recordListingID column of every item in the database with $listingCounter values. And that’s it.
