| |
Want to know more about Packt's Article Network? Interested in contributing your article ideas? Please visit our FAQ for more information. See More
| |
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: 
Figure 9-1: AJAX Grid using jQuery Let's take a look at the code for the grid and get started building it. Implementing the AJAX data gridThe files and folders for this project can be obtained directly from the code download 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: - Copy the grid folder from the code download to your ajax folder.
- Connect to your ajax database and execute the product.sql script.
- Update config.php with the correct database username and password.
- Load http://localhost/ajax/grid to verify the grid works fine—it should look just like Figure 9-1.
- 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:
Figure 9-2: Editing a row
Code overviewIf 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
Figure 9-3: The components of the AJAX grid The databaseOur 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 colorsLeaving 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>
The server sideThe code at the server side is made up of grid.php and grid.class.php. The former is a simple script that receives load and save requests from the client. Its structure is something like the following: <?php
... initialization
// load the grid
if($action == 'load')
{
... load the grid here
}
// save the grid data
elseif ($action == 'save')
{
... save the grid here
}
?>
The code that loads and saves the grid is located in grid.class.php, which contains the Grid class. The methods and fields of the Grid class, depicted in Figure 9-4, are quite self-explanatory. 
Figure 9-4: Diagram of the Grid class
 | - Build user friendly Web 2.0 Applications with JavaScript and PHP
- The ultimate AJAX tutorial for building modern Web 2.0 Applications
- Create faster, lighter, better web applications by using the AJAX technologies to their full potential
- Leverage the power of PHP and MySQL to create powerful back-end functionality and make it work in harmony with a responsive AJAX clientWrite better JavaScript code to enable powerful web features
- Understand the complete client-server mechanism by following complete, step-by-step case studies
http://www.packtpub.com/ajax-and-php-2nd-edition/book |
Creating the grid, step by stepIf you prefer to write the code yourself, just follow these steps: - Before we do anything, we'll need some data to work with. Create your products table executing the following SQL code in phpMyAdmin. (For briefness, we included here only a few of the product entries that you can find in the downloadable version.)
USE ajax;
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)
);
INSERT INTO product(name, price, on_promotion)
VALUES('Santa Costume', 14.99, 0);
INSERT INTO product(name, price, on_promotion)
VALUES('Medieval Lady', 49.99, 1);
INSERT INTO product(name, price, on_promotion)
VALUES('Caveman', 12.99, 0);
INSERT INTO product(name, price, on_promotion)
VALUES('Costume Ghoul', 18.99, 0);
INSERT INTO product(name, price, on_promotion)
VALUES('Ninja', 15.99, 0);
INSERT INTO product(name, price, on_promotion)
VALUES('Monk', 13.99, 0);
INSERT INTO product(name, price, on_promotion)
VALUES('Elvis Black Costume', 35.99, 0);
INSERT INTO product(name, price, on_promotion)
VALUES('Robin Hood', 18.99, 0);
INSERT INTO product(name, price, on_promotion)
VALUES('Pierot Clown', 22.99, 1);
INSERT INTO product(name, price, on_promotion)
VALUES('Austin Powers', 49.99, 0);
INSERT INTO product(name, price, on_promotion)
VALUES('Alien Visitor', 35.99, 0);
INSERT INTO product(name, price, on_promotion)
VALUES('Deadly Phantom Costume', 18.99, 1);
INSERT INTO product(name, price, on_promotion)
VALUES('Black Screamer Cape and Mask', 30.99, 0);
- Verify that your table has been correctly created:
Figure 9-5: The Product table in phpMyAdmin - Create a folder named grid in your ajax folder.
- Copy the scripts folder from the code download to your grid folder.
- Create a file named config.php in your grid folder with the following contents:
<?php
// defines database connection data
define('DB_HOST', 'localhost');
define('DB_USER', 'root');
define('DB_PASSWORD', '');
define('DB_DATABASE', 'ajax');
?>
- Create a file named error_handler.php in your grid folder and type the following code in it:
<?php
// set the user error handler method to be error_handler
set_error_handler('error_handler', E_ALL);
// error handler function
function error_handler($errNo, $errStr, $errFile, $errLine)
{
// clear any output that has already been generated
ob_clean();
// output the error message
$error_message = 'ERRNO: ' . $errNo . chr(10) .
'TEXT: ' . $errStr . chr(10) .
'LOCATION: ' . $errFile .
', line ' . $errLine;
echo $error_message;
// prevent processing any more PHP scripts
exit;
}
?>
- Create a file named grid.php and type the following code in it:
<?php
// load error handling script and the Grid class
require_once('error_handler.php');
require_once('grid.class.php');
// the default action is 'load'
$action = 'load';
if(isset($_GET['action']))
$action = $_GET['action'];
// load the grid
if($action == 'load')
{
// get the requested page
$page = $_POST['page'];
// get how many rows we want to have into the grid
$limit = $_POST['rows'];
// get index row - i.e. user click to sort
$sidx = $_POST['sidx'];
// get the direction
$sord = $_POST['sord'];
$grid = new Grid($page, $limit, $sidx, $sord);
$response->page = $page;
$response->total = $grid->getTotalPages();
$response->records = $grid->getTotalItemsCount();
$currentPageItems = $grid->getCurrentPageItems();
for($i=0;$i<count($currentPageItems);$i++) {
$response->rows[$i]['id'] =
$currentPageItems[$i]['product_id'];
$response->rows[$i]['cell']=array(
$currentPageItems[$i]['product_id'],
$currentPageItems[$i]['name'],
$currentPageItems[$i]['price'],
$currentPageItems[$i]['on_promotion']
);
}
echo json_encode($response);
}
// save the grid data
elseif ($action == 'save')
{
$product_id = $_POST['id'];
$name = $_POST['name'];
$price = $_POST['price'];
$on_promotion = ($_POST['on_promotion'] =='Yes') ? 1 : 0;
$grid = new Grid();
echo $grid->updateItem
($product_id, $on_promotion, $price, $name);
}
?>
- Create a file named grid.class.php and type the following code in it:
<?php
// load configuration file
require_once('config.php');
// start session
session_start();
// includes functionality to manipulate the products list
class Grid
{
// grid pages count
private $mTotalPages;
// grid items count
private $mTotalItemsCount;
private $mItemsPerPage;
private $mCurrentPage;
private $mSortColumn;
private $mSortDirection;
// database handler
private $mMysqli;
// class constructor
function __construct($currentPage=1, $itemsPerPage=5,
$sortColumn='product_id', $sortDirection='asc')
{
// create the MySQL connection
$this->mMysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD,
DB_DATABASE);
$this->mCurrentPage = $currentPage;
$this->mItemsPerPage = $itemsPerPage;
$this->mSortColumn = $sortColumn;
$this->mSortDirection = $sortDirection;
// call countAllRecords to get the number of grid records
$this->mTotalItemsCount = $this->countAllItems();
if($this->mTotalItemsCount >0)
$this->mTotalPages =
ceil($this->mTotalItemsCount/$this->mItemsPerPage);
else
$this->mTotalPages=0;
if($this->mCurrentPage > $this->mTotalPages)
$this->mCurrentPage = $this->mTotalPages;
}
// read a page of products and save it to $this->grid
public function getCurrentPageItems()
{
// create the SQL query that returns a page of products
$queryString = 'SELECT * FROM product';
$queryString .= ' ORDER BY ' .
$this->mMysqli->real_escape_string($this->mSortColumn) .
' ' . $this->mMysqli->real_escape_string(
$this->mSortDirection);
// do not put $limit*($page - 1)
$start = $this->mItemsPerPage * $this->mCurrentPage –
$this->mItemsPerPage;
if ($start<0) $start = 0;
$queryString .= ' LIMIT ' . $start . ',' . $this-
>mItemsPerPage;
// execute the query
if ($result = $this->mMysqli->query($queryString))
{
for($i = 0; $items[$i] = $result->fetch_assoc(); $i++) ;
// Delete last empty item
array_pop($items);
// close the results stream and return the results
$result->close();
return $items;
}
}
public function getTotalPages()
{
return $this->mTotalPages;
}
// update a product
public function updateItem($id, $on_promotion, $price, $name)
{
// escape input data for safely using it in SQL statements
$id = $this->mMysqli->real_escape_string($id);
$on_promotion = $this->mMysqli-
>real_escape_string($on_promotion);
$price = $this->mMysqli->real_escape_string($price);
$name = $this->mMysqli->real_escape_string($name);
// build the SQL query that updates a product record
$queryString = 'UPDATE product SET name="' . $name . '", ' .
'price=' . $price . ',' .
'on_promotion=' . $on_promotion .
' WHERE product_id=' . $id;
// execute the SQL command
$this->mMysqli->query($queryString);
return $this->mMysqli->affected_rows;
}
// returns the total number of records for the grid
private function countAllItems()
{
// the query that returns the record count
$count_query = 'SELECT COUNT(*) FROM product';
// execute the query and fetch the result
if ($result = $this->mMysqli->query($count_query))
{
// retrieve the first returned row
$row = $result->fetch_row();
// close the database handle
$result->close();
return $row[0];
}
return 0;
}
public function getTotalItemsCount()
{
return $this->mTotalItemsCount;
}
// end class Grid
}
?>
- Finally, create index.html with the following code:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>jqGrid Demo</title> <link rel="stylesheet" type="text/css" href="scripts/themes/green/grid.css" title="coffee" media="screen" /> <link rel="stylesheet" type="text/css" media="screen" href="themes/jqModal.css" /> <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> </head> <body> <h2>My Grid Data</h2> <table id="list" class="scroll" cellpadding="0" cellspacing="0"> </table> <div id="pager" class="scroll" style="text-align:center;"> </div> <script type="text/javascript"> var lastSelectedId; $('#list').jqGrid({ url:'grid.php', datatype: 'json', mtype: 'POST', colNames:['ID','Name', 'Price', 'Promotion'], colModel:[ {name:'product_id',index:'product_id', width:55,editable:false}, {name:'name',index:'name', width:100,editable:true, edittype:'text',editoptions:{size:30,maxlength:50}}, {name:'price',index:'price', width:80, align:'right',formatter:'currency', editable:true}, {name:'on_promotion',index:'on_promotion', width:80, formatter:'checkbox',editable:true, edittype:'checkbox'} ], rowNum:10, rowList:[5,10,20,30], imgpath: 'scripts/themes/green/images', pager: $('#pager'), sortname: 'product_id', viewrecords: true, sortorder: "desc", caption:"JSON Example", width:600, height:250, onSelectRow: function(id){ if(id && id!==lastSelectedId){ $('#list').restoreRow(lastSelectedId); $('#list').editRow(id,true,null,onSaveSuccess); lastSelectedId=id; } }, editurl:'grid.php?action=save' }); function onSaveSuccess(xhr) { response = xhr.responseText; if(response == 1) return true; return false; } </script> </body> </html>
- Load http://localhost/ajax/grid, and check that your grid works as presented in Figure 9-1 and 9-2.
As you can see, the grid allows you to edit entries in place, sort products, and generally work with the data in a much more responsive and intuitive manner. Because your users aren't waiting for updates to happen in a "batch" type way, their experience is likely to be more productive and even enjoyable! From the developer's perspective, use of existing plugins and CSS allows you rapidly develop solutions that are easily incorporated into new or existing websites, customize their appearance to match existing design criteria, and quickly alter the functionality and appearance as need be. SummaryAs with all endeavors, the more time you spend actually practicing it, the more adept you become—AJAX is no exception. We've endeavored to give you the tools you need to jump right in and begin putting them to good use—either creating sites from scratch or maintaining and updating an existing application. With a solid understanding of the mechanics behind the magic, you are well on your way to success. We're always pleased to hear from our readers and glimpse the projects that they've implemented using our materials—feel free to drop us a note and let us know what you're working on! We hope you have enjoyed learning AJAX with us—it has been our privilege to take the journey with you!
If you have read this article you may be interested to view :
| | - Build user friendly Web 2.0 Applications with JavaScript and PHP
- The ultimate AJAX tutorial for building modern Web 2.0 Applications
- Create faster, lighter, better web applications by using the AJAX technologies to their full potential
- Leverage the power of PHP and MySQL to create powerful back-end functionality and make it work in harmony with a responsive AJAX clientWrite better JavaScript code to enable powerful web features
- Understand the complete client-server mechanism by following complete, step-by-step case studies
http://www.packtpub.com/ajax-and-php-2nd-edition/book |
About the Author(s)Bogdan Brinzarea has a strong background in Computer Science holding a Master and Bachelor Degree at the Automatic Control and Computers Faculty of the Politehnica University of Bucharest, Romania and also an Auditor diploma at the Computer Science department at Ecole Polytechnique, Paris, France. His main interests cover a wide area from embedded programming, distributed and mobile computing and new web technologies. Currently, he is employed as an Alternative Channels Specialist at Banca Romaneasca, Member of National Bank of Greece where he is responsible for the Internet Banking project and coordinates other projects related to security applications and new technologies to be implemented in the banking area. Cristian Darie is a software engineer with experience in a wide range of modern technologies, and the author of numerous books, including his popular AJAX and PHP tutorial by Packt, his ASP.NET E-Commerce tutorial, by APress and his forthcoming SEO tutorial for PHP developers by Wrox Press. Cristian is studying distributed application architectures for his PhD, and is getting involved with various commercial and research projects. When not planning to buy Google, he enjoys his bit of social life. If you want to say "hi", you can reach Cristian through his personal website at http://www.cristiandarie.ro. Audra Hendrix was educated at Northwestern University. She works as a consultant in applied technology and marketing to small and medium-sized businesses. While her client list includes Fortune 500 companies, she prefers the flexibility, agility, and challenges of small to medium-sized businesses. She has consulted both in the United States and France for businesses seeking to better utilize their resources and maximize their gains by reinventing and reapplying back office and Internet applications, data management, cost-effective marketing strategies, staffing requirements, and planning and deployment of new or emerging product lines.
| |
| |
