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.
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
[source:sql]
create table movies (
movie_id int not null auto_increment,
title varchar(255) not null,
ranking int,
primary key (movie_id)
);
[/source
MySQL database schema
[source: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‘);
[/source ]
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
[source:php]
<?php
function dbConnect()
{
$link = mysql_connect(‘localhost’, ‘username’, ‘password’);
if (!$link)
return false;
return mysql_select_db(‘phpriot’);
}
?>
[/source]
movies.php
[source: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;
}
?>
[/source]
index.php
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.
[source: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>
[/source]
