Creating Sortable Lists With PHP And Ajax
You might have been in a situation before where you had a list of items in your database that needed to be output in a specific order. These items could be anything: perhaps a listing of your favourite movies or your favourite books. For whatever reason, you want them ordered in a custom way that can’t be determined automatically (such as alphabetical).
This article covers the implementation of a system that lets you easily define the order of such a list.
Traditionally, implementations of such functionality involve you clicking a “move up”, “move down”, “move to top”, or “move to bottom” button that switches the order the items (one item at a time). Or perhaps each item has a text box with a number in it, that by changing the numbers you can change the order of the list.
In any case, these methods are much more difficult to use than they should be. In this article, we’ll create a drag drop system using JavaScript that will let you drag an item to its new position, and then save the new order as soon as you drop the item.
To achieve the Ajax effects (that is, the drag/drop effect, and the seamless saving of ordering data), we will be using the Prototype and Scriptaculous libraries.
Firstly, we will create a database table (compatible with MySQL and PostgreSQL) and populate it with data. Then we will output our list and apply the drag and drop effects to it. Finally, we will deal with the saving of the new ordering data.
For our example, we will use a list of “favourite movies”, and implement functionality to change the order of our movies.
Creating Our Database And Populating It
We will now create the database table we need in order to create this example. We won’t be writing all the code for inserting, editing and deleting of data, as it is beyond the scope of this example. As such, we will simply provide insert statements to create a static list of data.
The examples below are for PostgreSQL and MySQL.
Create your database
First up, you need to create a database for this article. This may be in either PostgreSQL or MySQL. Additionally, you may need to setup a username and password to access the database, depending on your system setup.
MySQL database schema
create table movies ( movie_id int not null auto_increment, title varchar(255) not null, ranking int, primary key (movie_id) );
PostgreSQL database schema
create table movies ( movie_id serial not null, title varchar(255) not null, ranking int, primary key (movie_id) );
Database data
insert into movies (title) values ('American Pie'); insert into movies (title) values ('Die Hard'); insert into movies (title) values ('Clerks'); insert into movies (title) values ('Air Force One'); insert into movies (title) values ('Titanic'); insert into movies (title) values ('The Shawshank Redemption'); insert into movies (title) values ('Gone In 60 Seconds');
About the schema
The database table is fairly simple, it just consists of an ID, a movie title, and a field to store the ordering. There’s no particular reason why the ranking field is allowed to be null, other than the values won’t be set when we initially insert our data.
If we were being really tricky, we would write a trigger on the database that would assign the next ranking value when a row is inserted, but that is beyond the scope of this article.
Outputting The Database Data
Now that we’ve made and populated our database, we’re going to write a PHP script to connect to this database and select all of this data. Because we are making both a MySQL version and a PostgreSQL version, some of this code will be implemented twice (once for each).
Hopefully you are using database abstraction in your web applications, but for the purpose of this article we’ll assume that you aren’t.
database.php for MySQL
function dbConnect() { $link = mysql_connect('localhost', 'username', 'password'); if (!$link) return false; return mysql_select_db('phpriot'); }
database.php for PostgreSQL
function dbConnect() { $str = sprintf('host=%s user=%s password=%s dbname=%s', 'localhost', 'username', 'password', 'phpriot'); $link = pg_connect($str); return (bool) $link; }
movies.php for MySQL
function getMovies() { $query = 'select movie_id, title from movies order by ranking, lower(title)'; $result = mysql_query($query); $movies = array(); while ($row = mysql_fetch_object($result)) { $movies[$row->movie_id] = $row->title; } return $movies; }
movies.php for PostgreSQL
function getMovies() { $query = 'select movie_id, title from movies order by ranking, lower(title)'; $result = pg_query($query); $movies = array(); while ($row = pg_fetch_object($result)) { $movies[$row->movie_id] = $row->title; } return $movies; }
index.php for MySQL and PostgreSQL
Here’s the main script that displays the list of movies. It is the same for both MySQL and PostgreSQL, as it will include the necessary code. At this point it is not styled and it is not yet possible to change the ordering. We’ll be adding each of those things in next.
require_once('database.php'); require_once('movies.php'); if (!dbConnect()) { echo 'Error connecting to database'; exit; } $movies = getMovies(); <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "DTD/xhtml1-strict.dtd"> <html> <head> <title>phpRiot Sortable Lists</title> </head> <body> <h1>phpRiot Sortable Lists</h1> <ul id="movies_list"> foreach ($movies as $movie_id => $title) { <li> $title </li> } </ul> </body>
Adding Drag And Drop Functionality To Our List
We will now add the drag/drop functionality to our list, as well as applying CSS styles to the list. At this point the ordering of the list will not be saved, as we will do this in the next step.
Installing Scriptaculous
Since we are using Scriptaculous to create the drag/drop effect, we must now download and install it. Note that we also need the Prototype library, however, this is included with the Scriptaculous download.
This example uses Scriptaculous 1.5.3.
Once downloaded, extract the library in the directory where you saved index.php. You may save this elsewhere, but we will assume this is where you have saved it.
Styling the list – styles.css
Before we add the drag/drop, we will style the list. Below is a generic CSS class we will save to a file called styles.css.
.sortable-list { list-style-type : none; margin : 0; } .sortable-list li { border : 1px solid #000; cursor : move; margin : 2px 0 2px 0; padding : 3px; background : #f7f7f7; border : #ccc; width : 400px; }
The Scriptaculous drag sort code
It’s really simple to make our list drag-sortable. At this point we’re not actually saving the drag changes, but to make the list sortable, the following code is used:
Sortable.create('movies_list');
The name _movies_list_ refers to the ID of our unordered list.
There are many more options and effects that can be applied, but the default options work just fine for what we’re doing. You can always read the Scriptaculous documentation for more options.
Our new index.php
So here is the new version of index.php, with styles added, Scriptaculous and Prototype loaded, and our draggable list created:
require_once('database.php'); require_once('movies.php'); if (!dbConnect()) { echo 'Error connecting to database'; exit; } $movies = getMovies(); <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "DTD/xhtml1-strict.dtd"> <html> <head> <title>phpRiot Sortable Lists</title> <link rel="stylesheet" type="text/css" href="styles.css" /> <script type="text/javascript" src="scriptaculous-js-1.5.3/lib/prototype.js"></script> <script type="text/javascript" src="scriptaculous-js-1.5.3/src/scriptaculous.js"></script> </head> <body> <h1>phpRiot Sortable Lists</h1> <ul id="movies_list" class="sortable-list"> foreach ($movies as $movie_id => $title) { <li id="movie_ $movie_id "> $title </li> } </ul> <script type="text/javascript"> Sortable.create('movies_list'); </script> </body>
Creating The Order Processing Script
Now we need to write the script that processes any ordering changes to the list. Once this is done, we’ll add the functionality to our list to actually call this script.
When a change to the list occurs, an array of the movie ID’s in their new order is generated, so our processor needs to take this array, and then update the ranking field in the database accordingly.
To achieve this, we create a new function in our movies.php, called processMoviesOrder(). Add this function after the getMovies() function in movies.php.
processMoviesOrder() for MySQL
function processMoviesOrder($key) { if (!isset($_POST[$key]) || !is_array($_POST[$key])) return; $movies = getMovies(); $queries = array(); $ranking = 1; foreach ($_POST[$key] as $movie_id) { if (!array_key_exists($movie_id, $movies)) continue; $query = sprintf('update movies set ranking = %d where movie_id = %d', $ranking, $movie_id); mysql_query($query); $ranking++; } }
processMoviesOrder() for PostgreSQL
function processMoviesOrder($key) { if (!isset($_POST[$key]) || !is_array($_POST[$key])) return; $movies = getMovies(); $queries = array(); $ranking = 1; foreach ($_POST[$key] as $movie_id) { if (!array_key_exists($movie_id, $movies)) continue; $query = sprintf('update movies set ranking = %d where movie_id = %d', $ranking, $movie_id); pg_query($query); $ranking++; } }
processor.php for MySQL and PostgreSQL
Now here is the script that calls the processMoviesOrder script. Note that we pass the form index that holds the ordering values. There’s no great reason for doing this other than if you change the form key then you only have to change it here (note that this is the unordered list ID from index.php).
require_once('database.php'); require_once('movies.php'); if (!dbConnect()) exit;
Adding The Javascript Sorting Callback
The final item we must add is the JavaScript code to invoke processor.php when the list is updated. This involves creating a function that makes the Ajax update request, as well as telling the Scriptaculous Sortable.create() method about it.
Here’s the callback function:
function updateOrder() { var options = { method : 'post', parameters : Sortable.serialize('movies_list') }; new Ajax.Request('processor.php', options); }
Here we invoke the Prototype library’s Ajax request handler to call processor.php. Additionally, we use the serialize() method on the Scriptaculous Sortable object to create the POST variable we access in processor.php.
Finally, we modify our list creation to tell it about this updateOrder() callback:
Sortable.create('movies_list', { onUpdate : updateOrder });
The second parameter to Sortable.create() is an optional list of extra parameters. In this case we are just specifying the onUpdate parameter, which tells Sortable which function to call when the list is changed.
index.php for MySQL and PostgreSQL in full
require_once('database.php'); require_once('movies.php'); if (!dbConnect()) { echo 'Error connecting to database'; exit; } $movies = getMovies(); <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "DTD/xhtml1-strict.dtd"> <html> <head> <title>phpRiot Sortable Lists</title> <link rel="stylesheet" type="text/css" href="styles.css" /> <script type="text/javascript" src="scriptaculous-js-1.5.3/lib/prototype.js"></script> <script type="text/javascript" src="scriptaculous-js-1.5.3/src/scriptaculous.js"></script> </head> <body> <h1>phpRiot Sortable Lists</h1> <ul id="movies_list" class="sortable-list"> foreach ($movies as $movie_id => $title) { <li id="movie_ $movie_id "> $title </li> } </ul> <script type="text/javascript"> function updateOrder() { var options = { method : 'post', parameters : Sortable.serialize('movies_list') }; new Ajax.Request('processor.php', options); } Sortable.create('movies_list', { onUpdate : updateOrder }); </script> </body>
Summary
In this article we learned how to create a sortable list using PHP and Ajax. We used Scriptaculous and Prototype libraries to make light work of our JavaScript requirements (the sorting and Ajax requests), as these libraries provide a very powerful and simple interface to advanced features and effects.
Error handling
We didn’t deal with error handling at all in this article, for the sake of simplicity. Specifically, we didn’t specify what would happen if the update didn’t work. If the update failed, the list would appear to be updated, but when you refreshed the list it would be the old state.
One possible way to handle this would be to send a success/failure indication from processor.php, and then to read this response in index.php, rolling back the drag and drop if failure was returned.
Extra features
When you update the list, the saving of the new ordering is a very quick process, but it is possible that sometimes it could take longer due to latency or server load. As such, you might think about showing then hiding a message while performing the update.
To do this, you would make the message appear when updateOrder() is called, and then create another function to hide the message once complete. This is achieved by specifying the onComplete parameter in the options array for the Ajax request.
Here’s an example:
function updateOrder() { // turn on update message here var options = { method : 'post', parameters : Sortable.serialize('movies_list'), onComplete : function(request) { // turn off update message here } }; new Ajax.Request('processor.php', options); }
I’ll leave this as an exercise for you to complete. Hint: create a div which you initially set the CSS display property to none. Then set it to block to show the div, and set it back to none to hide it again.
Quentin Zervaas
