Creating sortable lists with PHP and AJAX

By Quentin Zervaas, 24 February 2006

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

Highlight: 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

Highlight: SQL

create table movies (

    movie_id    serial          not null,

    title       varchar(255)    not null,

    ranking     int,

    primary key (movie_id)

);

Database data

Highlight: 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.

the easiest way to sort things with the help of ajax