I haven’t written anything in a long time so I figured it is time to write something up. Because a huge portion of my traffic seems to be related to my scriptaculous examples I’ve put up, I figured I’d add another one that covers the process of updating a database with the results of drag/drop with an Ajax call. Each time the user changes an item’s position in the list it updates the database.

See the example (note: example uses Scriptaculous 1.7 Beta 2)

The methodology behind this is quite simple:

  1. Load the list values from the DB and show them on the screen
  2. Using JavaScript, setup the sortables
  3. Add a JavaScript callback to the drag/drop that points to a function that serializes the list and sends an Ajax request to the server
  4. On the server, loop through the list and update the database with the proper order

To do this I created three main files:

  • index.php – the UI that shows the list and has the JavaScript
  • ajax.php – the page that processes the Ajax request
  • db.php – a simple database class included in both index.php and ajax.php that makes the connection to the DB and has methods to get the current list and to update the list

Reviewing Index.php

Index.php looks like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

<?
require('db.php');
$demo = new SortableExample();
$list = $demo->getList();
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
	<title>Scriptaculous 1.7 Sortables Ajax Example</title>
	<link rel="stylesheet" type="text/css" href="style.css">
	<script src="js/prototype.js"></script>
	<script src="js/scriptaculous.js"></script>
	<script>
		Event.observe(window,'load',init,false);
		function init() {
			Sortable.create('listContainer',{tag:'div',onUpdate:updateList});
		}
		function updateList(container) {
			var url = 'ajax.php';
			var params = Sortable.serialize(container.id);
			var ajax = new Ajax.Request(url,{
				method: 'post',
				parameters: params,
				onLoading: function(){$('workingMsg').show()},
				onLoaded: function(){$('workingMsg').hide()}
			});
		}
		function handleResponse(req) {
			// this function will fire after the ajax request is complete...but we have nothing to do here
		}
	</script>
</head>
<body>
 
<h2>Scriptaculous Sortables Demo with Ajax Callback</h2>
Built with Scriptaculous 1.7 Beta 2.  Drag items in the list below.  Each time you update the list an Ajax call is made
that updated the database with the new order.<br><br>
 
<div id="listContainer">
	<?
	foreach($list as $item) {
		?>
		<div id="item_<?=$item['catid'];?>"><?=$item['category'];?></div>
		<?
	}
	?>
</div>
<div id="workingMsg" style="display:none;">Updating database...</div>
 
</body>
</html>

Let’s look at this in reverse order. At the bottom of the file there is a div with id ‘containerDiv’ (line 36). This is the container for our list. Within this is a small snippet of PHP that loops through an associative array and prints out each list item as a div with id ‘item_12′ where 12 is the ID for that record in the database (line 40). Lines 2-4 contain the PHP code that include the db.php file and use it’s class to get the associative array that is used here.

Underneath the container div is another div that has the text ‘Updating database…’ – this div is hidden by default and will be shown while Ajax calls are in progress (line 45).

Now to the JavaScript…it starts off on line 14 with a call to Event.observe that tells the browser to call the init function after the page loads. The init function (line 15) creates a new sortable list on the listContainer element, setting all divs within it to be sortable and setting the updateList function as a callback whenever the list is updated. This means that when the order of items in the list change, this function will be called with the list element passed as an argument. This function will perform the Ajax request to update the database.

The updateList function (line 18) first sets a simple variable for the url used for the Ajax request. Next (line 20) it calls Sortable.serialize, passing the id of the container as an argument, which serializes the list to a format such as listContainer[]=5,listContainer[]=7,listContainer[]=2 – here, the first three items of the list would be the items 5, 7, and 2 and the divs representing these items would have the following id values: item_5, item_7, and item_2. This serialized list is set in the variable ‘params’. Next, on line 21, an Ajax request is opened with Prototype’s Ajax.Request class. It is called with two arguments, the url and an object with various options. The options include the type of request (POST in this example, parameters to pass (the params variable constructed on line 20), and two functions (onLoading/onLoaded) that handle the showing and hiding of the ‘Updating database…’ div. Normally in an Ajax request I’d also add a callback for onComplete to handle the response from the server, but in this case the Ajax response doesn’t really matter to me because it is really only a one way communication in this demo – in a real application I’d have more error handling and would need to handle error conditions.

That’s about it for the index page…not too bad, right?

Reviewing ajax.php

The file ajax.php is a simple file that is designed to receive the Ajax request and call the appropriate methods defined in db.php to update the list. Its contents are shown below:

1
2
3
4
5
6

<?
session_start();
require('db.php');
$demo = new SortableExample();
$demo->updateList($_POST['listContainer']);
?>

Yup, that’s it. Basically it starts off by calling session_start() – I typically call session_start() at the top of pages like this because starting the session sends the right http headers to prevent the file from being cached and I’m too lazy to look up what the right headers are! Next it requires the db.php file, instantiates an object, and calls the updateList method of the object, passing the listContainer variable from the POST request (containing our serialized list). That’s it for the ajax.php page.

Reviewing db.php

The file db.php is a simple database class that connects to the database and has methods to get the list and to update the order of the list. In a ‘real’ application I would definitely have a much cleaner implementation of the database access and would use other DB libraries to connect to the db – I used the simple mysql db functions to minimize on the number of lines needed here. Here are the contents of the file:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

<?
class SortableExample {
	protected $conn;
	protected $user = 'test';
	protected $pass = 'test';
	protected $dbname = 'scriptaculous';
	protected $host = 'localhost';
 
	public function __construct() {
		$this->conn = mysql_connect($this->host, $this->user, $this->pass);
		mysql_select_db($this->dbname,$this->conn);
	}
 
	public function getList() {
		$sql = "SELECT * FROM categories ORDER BY orderid";
		$recordSet = mysql_query($sql,$this->conn);
		$results = array();
		while($row = mysql_fetch_assoc($recordSet)) {
			$results[] = $row;
		}
		return $results;
	}
 
	public function updateList($orderArray) {
		$orderid = 1;
		foreach($orderArray as $catid) {
			$catid = (int) $catid;
			$sql = "UPDATE categories SET orderid={$orderid} WHERE catid={$catid}";
			$recordSet = mysql_query($sql,$this->conn);
			$orderid++;
		}
	}
}
?>

Important note – this file uses PHP 5 syntax in this object…you’d need some changes to get this to run in PHP 4. I’ll skip talking about the constructor (which connects to the database) and the getList function (which…gets the list) in order to focus on the last method, ‘updateList’. This function takes in an array of items, i.e. array(5,7,2), that come from the serialized list. It loops through the list and generates SQL UPDATE queries for each item. Each time it increments $orderid so that the first item has an orderid of 1, the 2nd an orderid of 2, etc. This means that whenever we get the list, as long as the SQL ‘ORDER BY orderid’ clause is used the list will show in the correct order.

Summary

That’s it…hope you enjoyed it. The hopeful moral of the story is…Ajax is easy, and so is drag and drop….especially if you’re using a good library. Personally I think that Prototype is incredibly useful for JavaScript development and Scriptaculous is a good sidekick for effects and drag/drop. I’ve also used the Yahoo YUI widgets at work where we needed to do some more dynamic widgets such as trees – it worked really well. Jack Slocum’s YUI extension is incredibly well done too…though it’s a heavy one in terms of file size! In the end, it comes down to finding the right tool for the job…

Download the sample application if you want to take a closer look….