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

A couple of days ago I was browsing the net for some remote scripting (also known as AJAX) examples and how to use the XmlHttpRequest object. However, all examples were quite nasty in imiplementation so I decided to write an nice little javascript wrapper around the XmlHttpRequest object.

// JavaScript Document
function EzRemoteScripter() {
//private variables
var _XmlHttpRequest = null;
var _This = null;

//public properties
this.GetResponseXML = function() {
return (_XmlHttpRequest) ? _XmlHttpRequest.responseXML : null;
}

this.GetResponseText = function() {
return (_XmlHttpRequest) ? _XmlHttpRequest.responseText : null;
}

this.GetXMLHttpRequestObj = function() {
return _XmlHttpRequest;
}

//public methods
this.InitXmlHttpRequest = function(Method, CallUri) {
_InitXmlHttpRequest();
_This = this;
switch( arguments.length ) {
case 2:
_XmlHttpRequest.open(Method, CallUri);
break;
case 3:
_XmlHttpRequest.open(Method, CallUri, arguments[2]);
break;
}

if( arguments.length >= 4) {
_XmlHttpRequest.open(Method, CallUri, arguments[2], arguments[3]);
}
}

this.Commit = function(Data) {
if( _XmlHttpRequest ) {
_XmlHttpRequest.send(Data);
}
}

this.Close = function() {
if( _XmlHttpRequest ) {
_XmlHttpRequest.abort();
}
}

//public events
this.OnUninit = function() {};
this.OnLoading = function() {};
this.OnLoaded = function() {};
this.OnInteractive = function() {};
this.OnSuccess = function() {};
this.OnFailure = function() {};

//private events
function _OnUninit() { _This.OnUninit(); };
function _OnLoading() { _This.OnLoading(); };
function _OnLoaded() { _This.OnLoaded(); };
function _OnInteractive() { _This.OnInteractive(); };
function _OnSuccess() { _This.OnSuccess(); };
function _OnFailure() { _This.OnFailure(); };

//private methods
function _InitXmlHttpRequest() {
_XmlHttpRequest = _GetXmlHttpRequest();
_XmlHttpRequest.onreadystatechange = _StateHandler;
}

function _StateHandler() {
switch(_XmlHttpRequest.readyState) {
case 0:
window.setTimeout("void(0)", 100);
_OnUninit();
break;
case 1:
window.setTimeout("void(0)", 100);
_OnLoading();
break;
case 2:
window.setTimeout("void(0)", 100);
_OnLoaded();
break;
case 3:
window.setTimeout("void(0)", 100);
_OnInteractive();
break;
case 4:
if (_XmlHttpRequest.status == 200) {
_OnSuccess();
} else {
_OnFailure();
}
return;
break;
}
}

function _GetXmlHttpRequest() {
var requester
try {
requester = new XMLHttpRequest();
} catch (error) {
try {
requester = new ActiveXObject("Microsoft.XMLHTTP");
} catch (error) {
return null;
}
}
return requester;
}
}

The code is quiet selfexplanatory. However i have here also an exmaple of the usage. It is very simple.

Here is first the html (yes html file) that will show us our little example:

DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Documenttitle>
<script language="javascript" type="text/javascript" src="NIS.RemoteScripter.js">

and here out php script that will supply us with data:

php
header("Content-type: text/xml");
echo "h123ohello2asdddo3";
?>

Note the usage of the header. It is very important to identify the data as xml so that the XmlHttpRequest object can use the data in order to be manipulated with the standart DOM methods.

Just create a new javascript file with the name test.js (or whatever you want to) and write following:

// JavaScript Document
////////////////////////////////////////////////////////////
var test = new EzRemoteScripter();
var div = window.document.getElementById("DataContainer");

//this function is used to find our div element that will
//show all data that is being returned by our wrapper.
function SetDiv() {
if( ! div) {
div = window.document.getElementById("DataContainer");
}
}

////////////////////////////////////////////////////////////
//here we set the OnUninit event with a function.
//this event is fired if the XmlHttpRequest object was not
//initialized when we call the remote php script.
test.OnUninit = function() {
SetDiv();
div.innerHTML = "Uninitialized!!";
}
////////////////////////////////////////////////////////////
//This event is firing while data is being received from our remote script. Notice that when using document.getElement[..] you have to make sure that the element is set to our variable div.
test.OnLoading = function() {
SetDiv();
div.innerHTML = "Loading...";
}
////////////////////////////////////////////////////////////
//Event is fired when data finished loading
test.OnLoaded = function() {
SetDiv();
div.innerHTML = "Loaded...";
}
////////////////////////////////////////////////////////////
//now this event is kinda tricky. Now when finished loading all data resides in the XmlHttpRequest object.
//however when getting the data from the XmlHttpRequest object the OnInteractive event is fired. That means it is the data transfer between the XmlHttpRequest object and your script.
test.OnInteractive = function() {
SetDiv();
div.innerHTML = "Interacting...";
}
////////////////////////////////////////////////////////////
//this event is the most used and most important event.
//it is fired if the data has been loaded with a success.
//
test.OnSuccess = function() {
SetDiv();
div.innerHTML = "";
// here we are getting the data from the XmlHttpRequest object (OnInteractive is being fired)
//alternatively you may get the data as a string using the test.GetResponseText()
var obj = test.GetResponseXML();
var books = obj.getElementsByTagName("book");
for(var i=0; i < books.length; i++) {
var myLI = document.createElement("LI");
myLI.innerHTML = books[i].firstChild.data;
div.appendChild(myLI);
}
}
////////////////////////////////////////////////////////////
test.OnFailure = function() {
SetDiv();
div.innerHTML = "Failed!";
}
////////////////////////////////////////////////////////////
//this is the funciton that actually calls out php script to get the xml data. pretty easy to use.
//However, you HAVE to init and commit on each call otherwise the script won't work. you may also use the POST method to tranfer data instead of get. To do this you would have to do following:
//d.InitXmlHttpRequest("POST", "test.php");
//d.Commit("myVar=hello&mySecondVar=world");
//
//if you don't want to send any data just pass null to the Commit method. If you leave it empty FireFox will complain.
function Go(d) {
d.InitXmlHttpRequest("GET", "test.php");
d.Commit(null);
}

Thursday, January 27, 2011