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:

jQuery Drag'n DropjQuery Drag'n Drop DemoDownload

The Database:

We create a simple database as below:

jQuery Drag'n Drop Database

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:

<div id="contentLeft">
	<ul>
		<li id="recordsArray_&lt;?php echo $row['recordID']; ?&gt;">&nbsp;</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 				= mysql_real_escape_string($_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.

Answerbase
WebResourcesDepot Feed