How To Integrate a Database with AJAX
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 HTTP Request 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.