AJAX Project Overview

AJAX Advanced Customer Order System
By: Matt Farley

AJAX Application Demo:

Objective Statement and Narrative

Up until a few years ago, most applications which involved an interactive user front-end were written using standalone platform specific technologies. For example, an inventory tracking application might have been written using Visual Basic 6.0 for use on the Microsoft Windows platform. These types of applications have been around for quite some time, yet they carry some inherent disadvantages. For one, the users are tied to the specific platform for which the application is written. Secondly, the users are restricted to using the application on the physical machine on which the application is installed. Upgrading the application to a new version involves installations on each workstation, which can become cumbersome for large organizations.

Web applications on the other hand have traditionally suffered from the stateless nature of the HTTP protocol. This means that web applications do not keep a constant connection with the server and must resort to full page refreshes for each subsequent communication with the server. This is not only an annoyance to the user, but can be a burden on the servers. The relative infancy of web applications, combined with these disadvantages have thus been a deterrent to developing web-based applications.

The Solution
Developers have strived to overcome the disadvantages listed above with both web and standalone applications. The solution is a web based application that is platform independent, resides on a central server, and still provides the same interactivity and back-end communication as does a traditional standalone application. The recent strides made in such technology have been labeled by the marketing world using two prominent buzzwords: Web 2.0 and AJAX. This application, Advanced Customer Order System, stands as an example of the new age of web applications development, making use of both Web 2.0 ideals and AJAX techniques.

Web 2.0
Traditionally web sites have been self-contained entities making no use of external data, and not sharing any data of their own. For example, the only way to get the weather, was to go to the source: a site such as weather.com. The main idea behind Web 2.0 is the sharing and mixing (buzzword: mashup) of data. Companies such as Yahoo, Google, and Amazon are leading the way by opening the doors to their data, allowing external developers to tap in via APIs. So now if I want the weather on my website, instead of posting a link to Weather.com, I can use Weather.com's API to write my own code to pull data from their database and display it on my website.

Asynchronous JavaScript And XML (AJAX)
Microsoft first introduced the XMLHTTP scripting object with the release of Internet Explorer 5. This object's main use was in Microsoft's Outlook Web Access application, but didn't catch on much otherwise. In years following other browsers implemented native versions in their own scripting engines. The XMLHTTP object allows a web page to send and receive data from a server without the need for reloading the entire page. This helps to overcome the disadvantage of the HTTP protocol being stateless and allows for richly interactive applications which can rival traditional standalone applications in both features and functionality.

With the recent arrival of applications such as Google Maps (which makes use of the XMLHTTP object and offers an API) both users and developers are beginning to see the potential of what lies ahead. Despite marketing departments going bonkers with buzzwords, the reality is that 'AJAX' and 'Web 2.0' are not new technologies, but rather the new ways in which they're being used, and the ideas which they represent, are reshaping the face of web applications.

AJAX Project Proposal

Advanced Customer Order System - A web database application for the purpose of fulfilling customer orders. More specifically, the orders will be gifts: a cake, cookies, flowers, or a teddy bear. The application is used from the standpoint of an in-house operator taking calls over the phone, and sending out the gifts for delivery.

Customers will provide their address, contact information, and which gift they'd like to order. The operator uses this information to fill out the order form which will create a new order with a geographic map of the customer's home that drivers will use for delivery.

Managers will be able to see reports and summaries of customers by their geographic location (city or zip code), what they ordered, or view an interactive map of all customer locations.

Administrators will be able to view and edit the database tables in an advanced web front-end making use of Asynchronous JavaScript And XML requests (AJAX).

Functionality and Focus

This program functions as an order processing system, complete with reports and data modification. The 'Input Orders' view allows one to create a customer order while interactively viewing suggestions from the database of previous customers, as well as view previous orders. The view reports shows a geographical break down of customer orders, along with a brief break down of order percentages. The Database Admin section allows for live editing of customers, customer locations, and customer orders.

The focus of this application is to demonstrate the ability of web applications to compete with stand-alone applications, using the latest in web programming techniques. All three portions of this application make heavy use of AJAX (described above), and thus serve as examples of such techniques.

Conceptual Database Relation Model

ER Diagram

Internal Database View

Creating the tables:

DROP TABLE IF EXISTS request;
DROP TABLE IF EXISTS customer;
DROP TABLE IF EXISTS location;

CREATE TABLE customer (
  id int NOT NULL auto_increment,
  first varchar(20) NOT NULL,
  last varchar(20) NOT NULL,
  phone1 char(10) NOT NULL,
  phone2 char(10),
  INDEX (first),
  INDEX (last),
  INDEX (phone1),
  INDEX (phone2),
  PRIMARY KEY (id)
) Type=InnoDB;

CREATE TABLE location (
  id int NOT NULL auto_increment,
  latitude float(14,8),
  longitude float(14,8),
  address varchar(50) NOT NULL,
  city varchar(25) NOT NULL,
  zip char(5) NOT NULL,
  INDEX (address),
  PRIMARY KEY (id)
) Type=InnoDB;

CREATE TABLE request (
  id int NOT NULL auto_increment,
  type varchar(20) NOT NULL,
  record_date datetime NOT NULL,
  notes text,
  when_home varchar(40) NOT NULL,
  location_id int NOT NULL,
  customer_id int NOT NULL,
  PRIMARY KEY (id),
  INDEX (type),
  INDEX (record_date),
  INDEX (location_id),
  INDEX (customer_id),
  FOREIGN KEY (location_id) REFERENCES location(id),
  FOREIGN KEY (customer_id) REFERENCES customer(id)
) Type=InnoDB;

InnoDB was chosen for its foreign key referential constraints.

Code and Demo

AJAX Code snippets:

An Asynchronous JavaScript call - Here we see an XMLHTTP Object making an asynchronous HTTP request based on user input. This will use client-side variables to call a server-side PHP script, which processes the request, queries the database, and returns the results in XML format.

var firstName = document.getElementById('firstName').value;
var lastName = document.getElementById('lastName').value;
var address = document.getElementById('address').value;
var url = "getRequests.php?firstName=" + firstName + '&lastName=' + lastName + '&address=' + address + '&';
requestHttp.open("GET", url, true);
requestHttp.onreadystatechange = requestsResponse;
requestBusy = true;
requestHttp.send(null);

PHP Processing - The PHP script uses the variables passed in the querystring to query the database for records matching the criteria. The results are then returned in a generic XML format.

$sql = "
	SELECT r.id, c.first, c.last, r.record_date, l.address 
	FROM customer c, location l, request r   
	WHERE c.id = r.customer_id
	AND l.id = r.location_id
	AND c.first LIKE '$first%' 
	AND c.last LIKE '$last%' 
	AND l.address LIKE '$address%' 
	ORDER BY r.record_date DESC LIMIT 11
";
$rs = mysql_query($sql);

echo "<orders>";
while ($record = mysql_fetch_assoc($rs))
{
	echo "<order>";
		echo "<serial>".$record['id']."</serial>";
		echo "<first>".$record['first']."</first>";
		echo "<last>".$record['last']."</last>";
		echo "<desc>".$record['record_date']."</desc>";
		echo "<address>".$record['address']."</address>";
	echo "</order>";
}
echo "</orders>";

XML Result - Here is the XML result that's returned when we use the following querystring: getRequests.php?firstName=matt&lastName=ma

<orders>
	<order>
		<serial>6097</serial>
		<first>MATTHEW</first>
		<last>MAGEE</last>
		<desc>09/08/05</desc>
		<address>10331 Newton Way</address>
	</order>
	<order>
		<serial>8168</serial>
		<first>MATTHEW</first>
		<last>MARTINO</last>
		<desc>10/18/04</desc>
		<address>3646 BRIDGE ST</address>
	</order>
</orders>

JavaScript Parsing - The returned XML document is then parsed by client-side JavaScript, and the desired processing is coded in.

// Asssign returned XML document to a variable: xmlServiceRs
xmlServiceRs = requestHttp.responseXML.documentElement;						

// Loop through each record and its fields
for (var i=0; i < xmlServiceRs.childNodes.length; i++)
{
	thisSerial = xmlServiceRs.getElementsByTagName('serial').item(i).firstChild.data;
	thisFirst = xmlServiceRs.getElementsByTagName('first').item(i).firstChild.data;
	thisLast = xmlServiceRs.getElementsByTagName('last').item(i).firstChild.data;
	thisDesc = xmlServiceRs.getElementsByTagName('desc').item(i).firstChild.data;
	thisAddress = xmlServiceRs.getElementsByTagName('address').item(i).firstChild.data;
	
	// do something with results, process, etc.....
}

The full source code for this project is available for download: [ Full Source Code ]

Setup Instructions:

Unzip files into destination folder on webserver
Run /create_project.sql on your target MySQL database
Edit /includes/setup.php to reflect your setup
Voila!

Note: This application has been tested with MySQL 4.x and 5.x, on PHP 5.x.

Application Demo: