Drag and drop rows with a little Php

I have been working on this membership site script lately that is going to include in it a forum. One of the things I wanted to add in the admin was drag and drop so that an administrator could arrange forums and categories. Not only did I think it would look cool, but it would make it a whole heck of a lot easier to use.

After doing a little research on how to do it, I came across a nice “Library” so to speak that would allow me to do just that plus it even had some more features.

It’s name is Script.aculo.us.

What this allowed me to do is create the drag and drop rows using some javascript and then every time the user re-ordered the rows, it would automatically send an array to an external script for processing. This way the external script could save the new ordering in the database and the page wouldn’t need to be re-loaded.

This is how I did it:

First you need to set up a table in your database similar to the following:

CREATE TABLE `dragdropdemo` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 50 ) NOT NULL ,
`ordering` INT NOT NULL
) ENGINE = MYISAM ;

After you have that set up it’s time to create the html and javascript. First go to the Script.aculo.us website, download the files and upload them to your webserver.

Create a new file and code up some mysql/php to output the data in the database table we created earlier. Name the file something like “mydragdrop.php”… well I guess it really doesn’t matter.

The first thing you need to do is connect to the database. Lets do that:

<?php

// database Host (usually "localhost")
$host = "";

// database username
$user = "";

// database password
$pass = "";

// database name
$db = "";

// first we gotsta connect to the database
mysql_connect($host, $user, $pass);
mysql_select_db($db);

?>

I would save the connection stuff in a seperate file named “connect.php” because there is one more file that will use it later.

Ok now let’s get on to the page that will showcase your magic “dragdropdemo.php“. We need to include “scriptaculous.js” and “prototype.js” from the Sript.aculo.us files we uploaded to our server earlier in the head of our page.

<script src="http://www.nicklarson.com/scriptaculous/prototype.js" type="text/javascript"></script>
<script src="http://www.nicklarson.com/scriptaculous/scriptaculous.js" type="text/javascript"></script>

Ok, next it’s time to output the rows that we have stored in our database. This should be the easy part since we already did the connecting above. You need to make sure your UL tag has an id, as well as your LI rows because the javascript needs to call upon the elements to make this whole thing work. Also note that the LI element id’s need to be in this format: item_1 where “item” is any word you want it to be, and the number of the element is after the underscore.

<ul id="mylist">
<?php

$query = "SELECT * FROM dragdropdemo ORDER BY ordering";
$result = mysql_query($query);
while ($row = mysql_fetch_object($result)) {

      echo "<li id=\"item_" . $row->id . "\" style=\"cursor: move;\">" . $row->name . "</li>";

}

?>
</ul>

Alright once you have that figured out, we need to initialize the javascript to get these suckers rolling.

<script type="text/javascript">

Sortable.create('mylist', {constraint:'vertical', onUpdate : updateRows});

function updateRows(){
  var options = {
    method : 'post',
    parameters : Sortable.serialize('mylist')
  };
  new Ajax.Request('processor.php', options);
}
</script>

So what that’s saying is whenever someone drags and drops a row in the UL id “mylist” it will POST that information to “processor.php” which is where a little extra php will update the new ordering to the database.

On “processor.php” throw in the following code:

<?php

include "connect.php";

$i=1;
foreach($_POST[mylist] as $key=>$value) {
  mysql_query("UPDATE dragdropdemo SET ordering='".$i."' WHERE id ='".$value."'");
  $i++;
}

?>

processor.php takes the string that was POSTed over from the javascript/ajax on the dragdropdemo.php and update the database with the new order.

And that’s it! You’re done! It’s actually a lot easier to do than one might thing, especially with stuff like Script.aculo.us.

If you want to see this script in action you can click here for a demo. Notice that if you refresh your page, the order stays the same as you left it! That’s because everytime you resort the list, it automatically gets updated with Script.aculo.us! Cool huh!

as per request click here for a download in zip form of all the files!