In this article by Audra Hendrix, Bogdan Brinzarea and Cristian Darie, authors of AJAX and PHP: Building Modern Web Applications 2nd Edition, we will discuss the usage of an AJAX-enabled data grid plugin, jqGrid.

One of the most common ways to render data is in the form of a data grid. Grids are used for a wide range of tasks from displaying address books to controlling inventories and logistics management. Because centralizing data in repositories has multiple advantages for organizations, it wasn’t long before a large number of applications were being built to manage data through the Internet and intranet applications by using data grids. But compared to their desktop cousins, online applications using data grids were less than stellar – they felt cumbersome and time consuming, were not always the easiest things to implement (especially when you had to control varying access levels across multiple servers), and from a usability standpoint, time lags during page reloads, sorts, and edits made online data grids a bit of a pain to use, not to mention the resources that all of this consumed.

As you are a clever reader, you have undoubtedly surmised that you can use AJAX to update the grid content; we are about to show you how to do it! Your grids can update without refreshing the page, cache data for manipulation on the client (rather than asking the server to do it over and over again), and change their looks with just a few keystrokes! Gone forever are the blinking pages of partial data and sessions that time out just before you finish your edits. Enjoy!

In this article, we’re going to use a jQuery data grid plugin named jqGrid. jqGrid is freely available for private and commercial use (although your support is appreciated) and can be found at: http://www.trirand.com/blog/. You may have guessed that we’ll be using PHP on the server side but jqGrid can be used with any of the several server-side technologies. On the client side, the grid is implemented using JavaScript’s jQuery library and JSON. The look and style of the data grid will be controlled via CSS using themes, which make changing the appearance of your grid easy and very fast. Let’s start looking at the plugin and how easily your newly acquired AJAX skills enable you to quickly add functionality to any website.

Our finished grid will look like the one in Figure 9-1:

Let’s take a look at the code for the grid and get started building it.

Implementing the AJAX data grid

The files and folders for this project can be obtained directly from the code download(chap:9) for this article, or can be created by typing them in.

We encourage you to use the code download to save time and for accuracy. If you choose to do so, there are just a few steps you need to follow:

1. Copy the grid folder from the code download to your ajax folder.
2. Connect to your ajax database and execute the product.sql script.
3. Update config.php with the correct database username and password.
4. Load http://localhost/ajax/grid to verify the grid works fine – it should look just like Figure 9-1.
5. You can test the editing feature by clicking on a row, making changes, and hitting the Enter key. Figure 9-2 shows a row in editing mode:

    

Code overview

If you prefer to type the code yourself, you’ll find a complete step-by-step exercise a bit later in this article. Before then, though, let’s quickly review what our grid is made of. We’ll review the code in greater detail at the end of this article.

The editable grid feature is made up of a few components:

• product.sql is the script that creates the grid database
• config.php and error_handler.php are our standard helper scripts
• grid.php and grid.class.php make up the server-side functionality
• index.html contains the client-side part of our project
• The scripts folder contains the jQuery scripts that we use in index.html

The database

Our editable grid displays a fictional database with products. On the server side, we store the data in a table named product, which contains the following fields:

• product_id: A unique number automatically generated by auto-increment in the database and used as the Primary Key
• name: The actual name of the product
• price: The price of the product for sale
• on_promotion: A numeric field that we use to store 0/1 (or true/false) values. In the user interface, the value is expressed via a checkbox

The Primary Key is defined as the product_id, as this will be unique for each product it is a logical choice. This field cannot be empty and is set to auto-increment as entries are added to the database:

CREATE TABLE product
(
 product_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
 name VARCHAR(50) NOT NULL DEFAULT '',
 price DECIMAL(10,2) NOT NULL DEFAULT '0.00',
 on_promotion TINYINT NOT NULL DEFAULT '0',
 PRIMARY KEY (product_id)
);

The other fields are rather self-explanatory—none of the fields may be left empty and each field, with the exception of product_id, has been assigned a default value. The tinyint field will be shown as a checkbox in our grid that the user can simply set on or off. The on-promotion field is set to tinyint, as it will only need to hold a true (1) or false (0) value.

Styles and colors

Leaving the database aside, it’s useful to look at the more pertinent and immediate aspects of the application code so as to get a general overview of what’s going on here. We mentioned earlier that control of the look of the grid is accomplished through CSS. Looking at the index.html file’s head region, we find the following code:

<link rel="stylesheet" type="text/css" 
href="scripts/themes/coffee/grid.css" title="coffee" media="screen" />
<link rel="stylesheet" type="text/css" media="screen" 
 href="themes/jqModal.css" />

Several themes have been included in the themes folder; coffee is the theme being used in the code above. To change the look of the grid, you need only modify the theme name to another theme, green, for example, to modify the color theme for the entire grid. Creating a custom theme is possible by creating your own images for the grid (following the naming convention of images), collecting them in a folder under the themes folder, and changing this line to reflect your new theme name. There is one exception here though, and it affects which buttons will be used. The buttons’ appearance is controlled by imgpath: ‘scripts/themes/green/images‘, found in index.html; you must alter this to reflect the path to the proper theme.

Changing the theme name in two different places is error prone and we should do this carefully. By using jQuery and a nifty trick, we will be able to define the theme as a simple variable. We will be able to dynamically load the CSS file based on the current theme and imgpath will also be composed dynamically.

The nifty trick involves dynamically creating the < link > tag inside head and setting the appropriate href attribute to the chosen theme.

Changing the current theme simply consists of changing the theme JavaScript variable.

JqModal.css controls the style of our pop-up or overlay window and is a part of the jqModal plugin. (Its functionality is controlled by the file jqModal.js found in the scripts/js folder.) You can find the plugin and its associated CSS file at: http://dev.iceburg.net/jquery/jqModal/

In addition, in the head region of index.html, there are several script src declarations for the files used to build the grid (and jqModal.js for the overlay):

<script src="scripts/jquery-1.3.2.js" 
 type="text/javascript"></script>
<script src="scripts/jquery.jqGrid.js" 
 type="text/javascript"></script>
<script src="scripts/js/jqModal.js" 
 type="text/javascript"></script>
<script src="scripts/js/jqDnR.js" 
 type="text/javascript"></script>

There are a number of files that are used to make our grid function and we will talk about these scripts in more detail later.

Looking at the body of our index page, we find the declaration of the table that will house our grid and the code for getting the grid on the page and populated with our product data.

<script type="text/javascript">
var lastSelectedId;
$('#list').jqGrid({
 url:'grid.php', //name of our server side script.
 datatype: 'json',
 mtype: 'POST', //specifies whether using post or get
//define columns grid should expect to use (table columns)
 colNames:['ID','Name', 'Price', 'Promotion'], 
//define data of each column and is data editable? 
colModel:[ 
 {name:'product_id',index:'product_id', 
 width:55,editable:false}, 
//text data that is editable gets defined 
{name:'name',index:'name', width:100,editable:true, 
 edittype:'text',editoptions:{size:30,maxlength:50}},
//editable currency 
 {name:'price',index:'price', width:80, 
 align:'right',formatter:'currency', editable:true},
// T/F checkbox for on_promotion
 {name:'on_promotion',index:'on_promotion', width:80, 
 formatter:'checkbox',editable:true, edittype:'checkbox'} 
 ],
//define how pages are displayed and paged
 rowNum:10,
 rowList:[5,10,20,30],
 imgpath: 'scripts/themes/green/images',
 pager: $('#pager'),
 sortname: 'product_id',//initially sorted on product_id
 viewrecords: true,
 sortorder: "desc",
 caption:"JSON Example",
 width:600,
 height:250, 
//what will we display based on if row is selected
 onSelectRow: function(id){
 if(id && id!==lastSelectedId){
 $('#list').restoreRow(lastSelectedId);
 $('#list').editRow(id,true,null,onSaveSuccess);
 lastSelectedId=id;
 }
 },
//what to call for saving edits 
editurl:'grid.php?action=save'
});
//indicate if/when save was successful
function onSaveSuccess(xhr)
{
 response = xhr.responseText;
 if(response == 1)
 return true;
 return false;
}
</script>