PhpRiot
Become Zend Certified

Prepare for the ZCE exam using our quizzes (web or iPad/iPhone). More info...


When you're ready get 7.5% off your exam voucher using voucher CJQNOV23 at the Zend Store

Creating Sortable Lists With PHP And Ajax

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

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.

In This Article