1. Introduction
Today, there are quite a few web applications, such as Backpack, Blinksale and Gmail, that integrate database technology with AJAX. This integration has a huge impact on web applications and user experience by providing a powerful technology for communicating with the database without refreshing the browser - meaning that data can be transferred in real time while the user continues other interactions.
This article will focus on the above technology integration mechanism. Complete reference source code is also provided. This example is a simple job logging application where each job title contains a title, description and date - allowing users to add, edit and delete job titles. All of these are the basic operations you do when working with database record data, but this application goes a step further. A job title can be changed into an editable form - it will be saved or deleted from the database and displayed in its new state without refreshing the browser and interrupting the user's operation.
In this article, I assume that you have a basic understanding of AJAX, MySQL, and PHP, or a similar server-side language. If you haven't created an XML HTTP Request object yet, you can refer to my article "How to use AJAX" first. Next, let us first discuss the database issue.
2. Create the Database
The first thing you need to do is create database tables to store data for these positions. I created a MySQL table called informit_ajax - it has ID, title, description and date fields - these are all variables that will appear repeatedly in this article. Here is the code to create the table:
CREATE TABLE ′informit_ajax′ (
′id′ int(11) NOT NULL auto_increment,
'date' datetime NOT NULL default '0000-00-00 00:00:00',
'description' longtext NOT NULL,
′title′ varchar(100) NOT NULL default '',
PRIMARY KEY (′id′)
) TYPE=MyISAM;
You can execute this code using any MySQL query tool or language used to develop the application. Once the database is prepared, the next step is to create the front-end files that make requests to the PHP backend.
3. Make a request
The index HTML file here is a simple data placeholder - it will be analyzed from the database. This file contains references to JavaScript and CSS files; it also contains an onload handler that makes the first request and three div tags:
Layout - used to center the page content
loading - loads messages during the requested data load, which Will be received by the HTTPRequest object
· posts-used to display each analyzed job data
<head>
＜title＞How to Integrate a Database with AJAX＜/title＞
＜link href="css/layout.css" rel="stylesheet" type="text/css" /＞
＜script src="js/request.js"＞＜/script＞
＜script src="js/post.js"＞＜/script＞
＜/head＞
<body onload="javascript:makeRequest('services/post.php?method=get');">
<div id="layout" align="center">
＜div id="posts"＞＜/div＞
＜p＞＜input type="button" value="add a post" onmousedown="javascript:makeRequest('services/post.php?method=save');" /＞＜/p＞
＜p＞＜div id="loading"＞＜/div＞＜/p＞
＜/div＞
＜/body＞
The first request is generated when the page is loaded. This request sends a get query to a PHP class that we will create later; but first we need to create analysis methods for the request's response. The JavaScript request file handles all the basic work, such as creating objects, sending requests, and checking readiness status. When a response is received from the Request object, I use this JavaScript jobs file to handle the HTML generation of these jobs. The onResponse method is quite powerful because it handles the HTML page generation for each job title in both text and form versions, and places them into their own custom div tags; this way we can easily generate them during user interaction. Position them. This way we can switch between text and form versions of each post - this can be accomplished by clicking an "edit this post" link. The following is the code for the HTML page created for each position. You can see the complete method implementation in the corresponding download source file of this article.
var html = "<div class='post' id='post_"+ i +"' "+ postDisplay +">"
+ "＜div class='title' id='title_"+ i +"'＞"+ _title +"＜/div＞"
+ "＜div class='description' id='description_"+ i +"'＞"+ _description +"＜/div＞"
+ "＜div class='date' id='date_"+ i +"'＞"+ _date +"＜/div＞"
+ "＜a href="javascript:toggle('"+ i +"');"＞edit this post＜/a＞＜br/＞"
+ "＜/div＞"
+ "<div class='post' id='formPost_"+ i +"' "+ formPostDisplay +"＞"
+ "＜div class='title'＞＜input type='text' name='title' id='formTitle_"+ i +"' size='60' value='"+ _title +"'＞＜/div ＞"
+ "＜div class='description'＞＜textarea type='text' id='formDescription_"+ i +"' wrap='virtual' cols='60' rows='15'＞"+ _description +"＜/ textarea＞＜/div＞"
+ "＜div class='date'＞"+ _date +"＜/div＞"
+ "＜input type='button' name='cancel' value='cancel' onclick="javascript:toggle('"+ i +"');"＞"
+ "＜input type='button' name='delete' value='delete this post' onclick="javascript:deletePost("+ _id +");"＞"
+ "<input type='button' name='submit' value='save this post' onclick="javascript:saveNewPost("+ _id +","+ i +");">"
+ "＜/div＞"
+ "＜p＞"nbsp;＜/p＞";
The text version of each post simply displays the title, description and date and an "edit this post" link. The form version of each post has three buttons:
·"cancel " button - simply switches the status of the post back to the text version.
· "delete this post" button - sends the ID of the current post to the PHP object to delete it from the database.
· "save this post" button - allows the user to The new or edited job is saved to the server.
The core methods for handling server-side request communication are the onResponse, saveNewPost, deletePost and getPost methods; there are also getter and setter methods that store the currently operating job index. method provides the current index value to these core methods so that the correct position can be updated with the correct information based on that index. Here is a short summary for each core method (excluding onResponse, since we have looked at its functionality before). Description and code example:
· The following saveNewPost method saves the new post by collecting and sending the form input values to a PHP object and sets the getPost method as the onreadystatechange callback method:
function saveNewPost(_id, _index){
var newDescription = document.getElementById("formDescription_"+ _index).value;
var newTitle = document.getElementById("formTitle_"+ _index).value;
setIndex(_index);
sendRequest("services/post.php?method=save"id="+ _id +""title="+ newTitle +""description="+ newDescription, getPost);
}
· The getPost method below is a callback method - it is responsible for updating a single post when a response is received from the PHP object:
function getPost(){
if(checkReadyState(request)) {
var response = request.responseXML.documentElement;
var _title = response.getElementsByTagName('title')[getIndex()].firstChild.data;
var _description = response.getElementsByTagName('description')[getIndex()].firstChild.data;
var _date = response.getElementsByTagName('date')[getIndex()].firstChild.data;
document.getElementById("title_"+ getIndex()).innerHTML = _title;
document.getElementById("description_"+ getIndex()).innerHTML = _description;
document.getElementById("date_"+ getIndex()).innerHTML = _date;
toggle(getIndex());
}
}
· The deletePost method below sends the current index as a request to the PHP object, which will ultimately delete the record in the database and respond with an updated position:
function deletePost(_id){
sendRequest("services/post.php?method=delete"id="+ _id, onResponse);
}
Surprisingly, the most complicated part is over. Let's analyze the most critical part - database interaction.
4. Interact with the database
In order to interact with the database, you need to create methods for retrieving, inserting, replacing and deleting positions. I chose to create a post class with get, save and delete methods to handle these interactions. This class also has a reference to the database connection file (used to connect to the database). You must replace the login, password, and database name with your own database information.
DEFINE ('DB_USER', 'USERNAME');
DEFINE ('DB_PASSWORD', 'PASSWORD');
DEFINE ('DB_HOST', 'localhost');
DEFINE ('DB_NAME', 'DATABASE');
$dbc = @mysql_connect (DB_HOST, DB_USER, DB_PASSWORD) OR die ('Could not connect to MySQL: ' . mysql_error() );
The reference to the connection file and the name of the database are in the constructor of this class. Your constructor should look similar to the following code:
function Post(){
require_once('mysql_connect.php');
$this->table = "informit_ajax";
}
The following dbConnect method is responsible for creating the connection - by passing login information to the database; this method is reused in all core methods before querying the database:
function dbConnect(){
DEFINE ('LINK', mysql_connect (DB_HOST, DB_USER, DB_PASSWORD));
}
The following get method loops through the database table, creates an XML string based on the database row, and returns the string to the requester:
function get(){
$this->dbConnect();
$query = "SELECT * FROM $this->table ORDER BY id";
$result = mysql_db_query (DB_NAME, $query, LINK);
$xml = "＜?xml version="1.0" encoding="ISO-8859-1" ?＞n";
$xml .= "＜posts＞n";
while($row = mysql_fetch_array($result)) {
$xml .= "＜post＞n";
$xml .= "＜id＞" . $row['id'] . "＜/id＞n";
$xml .= "＜date＞" . $row['date'] . "＜/date＞n";
$xml .= "＜title＞＜![CDATA[" . $row['title'] . "]]＞＜/title＞n";
$xml .= "＜description＞＜![CDATA[" . $row['description'] . "]]＞＜/description＞n";
$xml .= "＜/post＞n";
}
$xml .= "＜/posts＞";
mysql_close();
header("Content-Type: application/xml; charset=UTF-8");
echo $xml;
}
The following save method achieves two purposes by handling update and insertion positions:
function save($id, $title, $description){
$this->dbConnect();
$query = "SELECT * FROM $this->table WHERE id='$id'";
$result = @mysql_query ($query);
if (mysql_num_rows($result) > 0)
{
$query = "UPDATE $this->table SET title='$title', description='$description', date=NOW() WHERE id='$id'";
$result = @mysql_query($query);
}
else
{
$query = "INSERT INTO $this->table (title, description, date) VALUES ('$title', '$description', NOW())";
$result = @mysql_query($query);
}
mysql_close();
$this->get();
}
The delete method below is responsible for deleting a location based on the ID passed as a parameter. Then call the get method to return the new data to the request file:
function delete($id){
$this->dbConnect();
$query = "DELETE FROM $this->table WHERE id='$id'";
$result = @mysql_query($query);
mysql_close();
$this->get();
}
5. Comprehensive application
In order to integrate the above parts together, a simple file needs to be created to serve as a communication bridge between XML HTTP requests and PHP objects. The page at this point not only creates PHP objects, but also receives queries and passes variables to dynamically generated methods - in this case, get, save, or delete. An example query below includes a $method and the reliable $id, $title and $description variables.
require_once("../classes/Post.class.php");
$post = new Post();
$post->$method($id, $title, $description);
We will discuss these techniques further in the future. Today's web development looks young and dynamic again, and we are lucky to be part of this new technological era.