Learn how to integrate a database with Ajax using PHP
Quite a few new applications out on the web—Backpack, Blinksale, and Gmail, for example—integrate databases with AJAX. The integration can have a very powerful effect on the web application and the user experience by providing the power to communicate with the database without refreshing the browser. This means real-time data transfers while the user continues with other interactions.
This article focuses on how this flow of information happens. Sample code can be downloaded here to help you get started. The sample is a simple note-posting application, with each post containing a title, description, and date, allowing users to add, edit, and delete posts. This is all pretty standard stuff when you’re working with database records, but this application goes a step further. A post can change into an editable form, be saved to or deleted from the database, and be displayed in its new state without refreshing the browser and interrupting the user experience.
In this article, I’m assuming that you have a basic understanding of AJAX, MySQL, and PHP, or a similar server-side language. If you don’t have experience creating the XML HTTPRequest object, you can read my article “How To Use AJAX.” Let’s get started with the database.
Creating the Database
The first thing that you’ll need is a database table to store the data for the posts. I created a MySQL table named informit_ajax that has an ID, title, description, and date field, which will be recurring variables throughout this article. Following 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 with any MySQL query tool or with the language that you decide to use for your application. Once the database is ready, the front-end files that make requests to the PHP back end will need to be created.
Making a Request
The index HTML file is simply a placeholder for the data that will be parsed from the database. It contains the references to the JavaScript and CSS files; an onload handler, which makes the first request; and three div tags:
- layout for centering the content on the page
- loading for the loading message that will be received by the HTTP Request object during the loading of the requested data
- posts for displaying each post after parsing
<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 made when the page loads. This request sends a get query to a PHP class that we will create shortly, but first we’ll need to create the parsing methods for the requested response. The JavaScript request file handles all the basics, such as creating the object, sending the request, and checking the ready state. The JavaScript post file is what I’m using to handle the HTML rendering of the posts when the response from the Requestobject is received. The onResponse method is fairly robust, as it handles the HTML rendering of each post in both the text and form versions and places them in their own custom div tags so we can target them easily during user interactions. This way, we can just toggle between the text and form versions of each post by clicking an edit this post link. Following is the HTML that is written to the page for each post, the entire method can be viewed by downloading the source code here.
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, along with anedit this post link. The form version of each post has three buttons:
- The cancel button simply toggles the state of the post back to the text version.
- The delete this post button sends the ID of the current post to the PHP object to remove it from the database.
- The save this post button allows the user to save the new or edited post to the server.
The core methods that handle the communication for the server-side requests are theonResponse, saveNewPost, deletePost, and getPost methods. There is also a getter and a setter method that store the index of the current post that’s being manipulated. These getter/setter methods provide the core methods with the current index so that the correct post can be updated with the correct information based on that index. Following is a brief description and code sample for each of the core methods (excluding onResponse, since we previously viewed its functionality):
- The saveNewPost method handles saving new posts by gathering and sending the form-input values to the PHP object and setting the getPost method as the callback method for the onreadystatechange:
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 is the callback method that handles updating individual posts when the 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 sends the current index as a request to the PHP object, which ultimately deletes the record from the database and responds with the updated posts:
function deletePost(_id)
{
sendRequest("services/post.php?method=delete"id="+ _id, onResponse);
}
Surprisingly, the most complicated part is over. Let’s move on to the best part, the database interaction.
Interacting with the Database using PHP
To interact with the database, you need to create the methods for retrieving, inserting, replacing, and deleting posts. 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 that’s used to connect to the database. You’ll have to replace the login, password, and database name with your 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 is located in the constructor of the class, along with the name of the database. Your constructor should look similar to the following code:
function Post()
{
require_once(’mysql_connect.php’);
$this->table = "informit_ajax";
}
The dbConnect method handles creating the connection by passing the login information to the database; this method is reused in all of the core methods before querying the database:
function dbConnect()
{
DEFINE (’LINK’, mysql_connect (DB_HOST, DB_USER, DB_PASSWORD));
}
The get method loops through the table, creates an XML string based on the database rows, and returns the XML 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 save method serves two purposes, by handling updating and inserting posts:
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 handles removing a post based on the ID that’s passed as the parameter. Then the get method is called to return the new data to the requesting file:
function delete($id)
{
$this->dbConnect();
$query = "DELETE FROM $this->table WHERE id=’$id’";
$result = @mysql_query($query);
mysql_close();
$this->get();
}
Tying It All Together
To tie it all together, a simple file needs to be created to act as the communication between the XML HTTP request and PHP objects. This page not only creates the PHP object, it also receives queries and passes variables to dynamically generated methods; in this case, get, save, or delete. A sample query would include a $method and optional $id, $title, and $description variables.
require_once("../classes/Post.class.php");
$post = new Post();
$post->$method($id, $title, $description);
Many possibilities with this group of technologies are yet to be discovered. The web seems young and fresh again, and we’re lucky to be part of this new wave of functionality.
