Introduction
Here is another one of my Very Basic AJAX examples. Earlier I published the following two code samples:
Very basic AJAX sample for loading picture descriptions from PHP
Very basic AJAX and PHP example for interactively loading select html elements and drop down boxes with XML content
This time am a MySQL database table as the data store for car sales data. The web page for this application renders a web form for entering the first and last name of a sales rep of a imaginary car dealer ship.
Figure 1: Car sales reps looking up their car sales
When the user hits the submit button, an AJAX request gets fired off and runs a simple query against a MySQL database.
_thumb_1.png "WindowClipping (50)")
Figure 2: AJAX request fetches data from MySQL
The PHP script returns the results as XML file.
Figure 3: Result as XML file
Video
The following YouTube video shows the AJAX application in action
Source Code
HTML and JavaScript
<!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>
<title>Lookup Car Sales</title>
<style type="text/css" media=screen>
/* "Winter Blues" CSS theme for CSS Table Gallery (http://icant.co.uk/csstablegallery/) by Gunta Klavina (http://www.klavina.com) */
table {font: 85% "Lucida Grande", "Lucida Sans Unicode", "Trebuchet MS", sans-serif;padding: 0; margin: 0; border-collapse: collapse; color: #333; background: #F3F5F7;}
table a {color: #3A4856; text-decoration: none; border-bottom: 1px solid #C6C8CB;}
table a:visited {color: #777;}
table a:hover {color: #000;}
table caption {text-align: left; text-transform: uppercase; padding-bottom: 10px; font: 200% "Lucida Grande", "Lucida Sans Unicode", "Trebuchet MS", sans-serif;}
table thead th {background: #3A4856; padding: 15px 10px; color: #fff; text-align: left; font-weight: normal;}
table tbody, table thead {border-left: 1px solid #EAECEE; border-right: 1px solid #EAECEE;}
table tbody {border-bottom: 1px solid #EAECEE;}
table tbody td, table tbody th {padding: 10px; text-align: left;}
table tbody tr {background: #F3F5F7;}
table tbody tr.odd {background: #F0F2F4;}
table tbody tr:hover {background: #EAECEE; color: #111;}
table tfoot td, table tfoot th, table tfoot tr {text-align: left; font: 120% "Lucida Grande", "Lucida Sans Unicode", "Trebuchet MS", sans-serif; text-transform: uppercase; background: #fff; padding: 10px;}
</style>
<script type="text/javascript">
function getCarSalesData(dataSource, divID)
{
var first_name = document.getElementById('first_name').value
var last_name = document.getElementById('last_name').value
var data = first_name + "|" + last_name;
getXML(dataSource, divID, data);
return false;
}
function displayXML(XML, divID)
{
var logonXMLElements = XML.getElementsByTagName("logon");
var logonResult = logonXMLElements[0].firstChild.data;
var resultDiv = document.getElementById(divID);
if(logonResult.toLowerCase() == "success")
{
carsalesXMLElements = XML.getElementsByTagName("carsales");
var length = carsalesXMLElements.length;
var resultHtml = "<table>";
resultHtml += "<thead>";
resultHtml += "<tr>";
resultHtml += "<th>" + "Car" + "</th>";
resultHtml += "<th>" + "Date" + "</th>";
resultHtml += "<th>" + "Price" + "</th>";
resultHtml += "</tr>";
resultHtml += "</thead>";
var i = 0;
for(i=0;i < length; i++)
{
var carsales = carsalesXMLElements[i];
price = carsales.firstChild.data;
carsalesAttributes = carsales.attributes;
date = carsalesAttributes.getNamedItem("date").nodeValue;
car = carsalesAttributes.getNamedItem("car").nodeValue;
resultHtml += '<tr>';
resultHtml += '<td>' + car + '</td>';
resultHtml += '<td>' + date + '</td>';
resultHtml += '<td>' + price + '</td>';
resultHtml += '</tr>';
}
resultHtml += "</table>\n";
resultDiv.innerHTML = resultHtml;
//alert("Transaction");
}
else if(logonResult.toLowerCase() == "error")
{
messageXMLElements = XML.getElementsByTagName("message");
var messageXML = messageXMLElements[0];
var message = messageXML.firstChild.data;
var resultHtml = '<br>' + message + '<br>';
resultDiv.innerHTML = resultHtml;
}
else
{
resultDiv.innerHTML = '<h1 style="color: red;">Login Failed! Please try again or call the help desk!</h1>';
}
}
function getXML(dataSource, divID, data)
{
var XMLHttpRequestObject = false;
if (window.XMLHttpRequest)
{
XMLHttpRequestObject = new XMLHttpRequest();
XMLHttpRequestObject.overrideMimeType("text/xml");
}
else if (window.ActiveXObject)
{
XMLHttpRequestObject = new ActiveXObject("Microsoft.XMLHTTP");
}
if(XMLHttpRequestObject)
{
var obj = document.getElementById(divID);
obj.innerHTML = '<img src="wait30trans.gif" alt="Waiting..." />';
XMLHttpRequestObject.open("POST", dataSource);
XMLHttpRequestObject.setRequestHeader('Content-Type',
'application/x-www-form-urlencoded');
XMLHttpRequestObject.onreadystatechange = function()
{
if (XMLHttpRequestObject.readyState == 4 &&
XMLHttpRequestObject.status == 200)
{
var myXML = XMLHttpRequestObject.responseXML;
displayXML(myXML, divID);
}
}
XMLHttpRequestObject.send("data=" + data);
//alert("Data sent!");
}
return false;
}
</script>
</head>
<body>
<div id="logonDiv">
<h1>Car Sales Lookup</h1>
<h2>Look up the car sales for the sales rep below:</h2>
<form action="get_car_sales.html" name="logonForm">
<table>
<tr>
<td>
First Name:
</td>
<td>
<input id="first_name" name="first_name" type="text" />
</td>
</tr>
<tr>
<td>
Last Name:
</td>
<td>
<input id="last_name" name="last_name" type="text" />
</td>
</tr>
<tr>
<td>
</td>
<td>
<br /><input id="logon" type="submit" value="Submit" onclick="return getCarSalesData('get_car_sales_data.php', 'carSalesInfoDiv')" />
</td>
</tr>
</table>
</form>
</div>
<div id="carSalesInfoDiv">
</div>
</body>
</html>
PHP
<?php
header("Content-type: text/xml");
$data = $_POST['data'];
#echo "You entered >$data<";
$DANGER = array();
$OK = array();
$SQL = array();
$DANGER = explode('|', $data);
if (ctype_alpha($DANGER[0]))
{
$OK['first_name'] = $DANGER[0];
}
if (ctype_alpha($DANGER[1]))
{
$OK['last_name'] = $DANGER[1];
}
$db = new mysqli('localhost', 'salesman', 'audi777', 'salesdb');
if (mysqli_connect_errno()) {
echo 'Error: Could not connect to database. Please try again later.';
exit;
}
$fullname = $OK['first_name'] . " " . $OK['last_name'];
$SQL['fullname'] = $db->real_escape_string($fullname);
/* create a prepared statement */
if ($SQLStmt = $db->prepare("SELECT car, date, price FROM car_sales WHERE rep_name=? ORDER BY date"))
{
/* bind parameters for markers */
$SQLStmt->bind_param("s", $SQL['fullname']);
/* execute query */
$SQLStmt->execute();
/* bind result variables */
$SQLStmt->bind_result($car, $date, $price);
/* loads result set into memory for num_rows to work; does not make sense with large sets */
$SQLStmt->store_result();
if($SQLStmt->num_rows > 0)
{
echo '<?xml version = "1.0" ?>';
echo '<mydata>';
echo '<logon>Success</logon>';
/* fetch value */
while ($SQLStmt->fetch())
{
echo '<carsales date="' . $date . '" car="' . $car . '">' . $price . '</carsales>';
}
echo '</mydata>';
}
else
{
echo '<?xml version = "1.0" ?>';
echo '<mydata>';
echo '<logon>Error</logon>';
echo '<message>'. $fullname . ' not found!</message>';
echo '</mydata>';
}
/* close statement */
$SQLStmt->close();
}
else
{
echo '<?xml version = "1.0" ?>';
echo '<myapp>';
echo '<logon>Error</logon>';
echo '<message>No records found. Try again later!</message>';
echo '</myapp>';
}
$db->close();
?>
MySQL import SQL script
-- phpMyAdmin SQL Dump
-- version 3.3.7
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Nov 05, 2010 at 02:29 PM
-- Server version: 5.1.38
-- PHP Version: 5.2.14
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Database: `cim225`
--
-- --------------------------------------------------------
--
-- Table structure for table `car_sales`
--
CREATE TABLE IF NOT EXISTS `car_sales` (
`sales_id` int(11) NOT NULL AUTO_INCREMENT,
`rep_name` char(64) NOT NULL,
`car` char(64) NOT NULL,
`date` date NOT NULL,
`price` decimal(7,2) NOT NULL,
PRIMARY KEY (`sales_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;
--
-- Dumping data for table `car_sales`
--
INSERT INTO `car_sales` (`sales_id`, `rep_name`, `car`, `date`, `price`) VALUES
(1, 'Klaus Graefensteiner', 'Audi A4', '2010-05-11', 20000.00),
(2, 'Klaus Graefensteiner', 'Audi A8', '2010-02-08', 85000.00),
(3, 'Scott Done', 'Audi A6', '2010-04-12', 45000.00),
(4, 'Klaus Graefensteiner', 'Audi A5', '2010-07-08', 34000.00),
(5, 'Klaus Graefensteiner', 'Audi A8', '2010-05-23', 64000.00),
(6, 'Klaus Graefensteiner', 'Audi A3', '2010-02-12', 39000.00),
(7, 'Klaus Graefensteiner', 'Audi A4', '2010-01-08', 30000.00),
(8, 'Scott Done', 'Audi A6', '2010-05-12', 45000.00),
(9, 'Scott Done', 'Audi A8', '2010-07-12', 85000.00),
(10, 'Scott Done', 'Audi A3', '2010-01-12', 30000.00),
(11, 'Scott Done', 'Audi A3', '2010-09-12', 28000.00),
(12, 'Scott Done', 'Audi A4', '2010-04-12', 29000.00);
Download
The complete application can be downloaded here:
Ausblick
Subscribe
