Creating Sortable Lists With PHP And Ajax-..:: Programmer Kingdom ::..

Creating Sortable Lists With PHP And Ajax

Introduction

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.

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

Listing 1 listing-1.sql

create table movies (
movie_id    int             not null    auto_increment,
title       varchar(255)    not null,
ranking     int,
primary key (movie_id)
);

PostgreSQL database schema

Listing 2 listing-2.sql

create table movies (
movie_id    serial          not null,
title       varchar(255)    not null,
ranking     int,
primary key (movie_id)
);

Database data

Listing 3 listing-3.sql

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

Listing 4 database.php

<?php
function dbConnect()
{
$link = mysql_connect('localhost', 'username', 'password');
if (!$link)
return false;
return mysql_select_db('phpriot');
}
?>

database.php for PostgreSQL

Listing 5 database.php

<?php
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

Listing 6 movies.php

<?php
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

Listing 7 movies.php

<?php
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.

Listing 8 index.php

<?php
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">
<?php foreach ($movies as $movie_id => $title) { ?>
<li><?= $title ?></li>
<?php } ?>
</ul>
</body>
</html>

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.

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.

Listing 9 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:

Listing 10 listing-10.js

Sortable.create('movies_list');

The name _movies_list_ refers to the ID of our unordered list.

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:

Listing 11 index.php

<?php
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">
<?php foreach ($movies as $movie_id => $title) { ?>
<li id="movie_<?= $movie_id ?>"><?= $title ?></li>
<?php } ?>
</ul>
<script type="text/javascript">
Sortable.create('movies_list');
</script>
</body>
</html>

Note that we also added an ID to each list item, as these are the values that will be passed to the form. Note that these IDs — and the ID of the list — should use underscores as separators, not hyphens.

So at this point, if you view this page, you should be able to drag the items in your list up and down!

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

Listing 12 movies.php

<?php
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

Listing 13 listing-13.php

<?php
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).

Listing 14 listing-14.php

<?php
require_once('database.php');
require_once('movies.php');
if (!dbConnect())
exit;
processMoviesOrder('movies_list');
?>

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:

Listing 15 listing-15.js

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:

Listing 16 listing-16.js

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

Listing 17 listing-17.php

<?php
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">
<?php foreach ($movies as $movie_id => $title) { ?>
<li id="movie_<?= $movie_id ?>"><?= $title ?></li>
<?php } ?>
</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>
</html>

Now, when you visit this page, you will see the list just as you did before, but now when you drag an item to a new location, it will be saved in the database. If you don’t believe me, try dragging an item, closing your browser, then reloading the page. The order will be just as you left it after dragging the item.

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:

Listing 18 listing-18.js

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);
}