Flexigrid Tutorial

转载地址：http://www.kenthouse.com/blog/2009/07/fun-with-flexigrids/

Here at Kent House we are always looking at new ways to improve the user experience of our sites. I have recently been creating an application for a client and found an excellent jQuery-based component which will give your site the look and feel of a more traditional application if you are having to navigate between large sets of database records.

This is the?Flexigrid?written by Paulo P. Marinas. There isn’t a lot of documentation around for this?jQueryplugin so I decided to create this tutorial which might be of benefit to someone.

As you can see the component is very intuitive and includes features to:

• Search for records matching your supplied criteria (by first clicking the search icon).
• Sort in either ascending or descending order by a selected column.
• Hide and show columns to make optimum use of available space.
• Navigate between pages using the navigation icons or jump straight to a particular page.

HOW TO USE

Adding the Flexigrid to your webpage couldn’t be easier. Just download the code from?www.flexigrid.info?and copy the required files into your site’s directories. You must also have a version of jQuery running on your site for this to work which can be found at?jquery.com.

You will find a flexigrid.js file in the downloaded archive. Include this file in the head section of your site as you would normally do along with the provided CSS file (you will need to copy across the entire contents of the ‘css’ directory including the images).

After creating a table element on your page with an id of ‘flex1′ for this example you can then create and include a javascript file consisting of the following code. The Flexigrid will then be created on page load.

$(function() {
$("#flex1").flexigrid(
{
url: 'staff.php',
dataType: 'json',
colModel : [
{display: 'ID',name : 'id',width : 40,sortable : true,align: 'left'},
{display: 'First Name',name : 'first_name',width : 150,
{display: 'Surname',name : 'surname',
{display: 'Position',name : 'email',width : 250,align: 'left'}
],
buttons : [
{name: 'Edit',bclass: 'edit',onpress : doCommand},
{name: 'Delete',bclass: 'delete',
{separator: true}
],
searchitems : [
{display: 'First Name',name : 'first_name'},isdefault: true},name : 'position'}
],
sortname: "id",
sortorder: "asc",
usepager: true,
title: "Staff",
useRp: true,
rp: 10,
showTableToggleBtn: false,
resizable: false,
width: 700,
height: 370,
singleSelect: true
}
);
});

PROPERTIES

Using the above as a basis for your grid,you can customise it to suit your requirements using several javascript properties. For example,you can specify on which column the results should be sorted initially and whether to sort in ascending or descending order. The most common properties are shown below.

THE SERVER-SIDE SCRIPT

For this example I will be using a PHP script to return the JSON results filtered by the criteria specified within the Flexigrid.

The following data is posted to our script using AJAX and can be found in PHP’s $_POST array.

Now that we have this information we can go ahead and create the script.

<?php
// Connect to MySQL database
mysql_connect('server','username','password');
mysql_select_db('dbname');
$page = 1; // The current page
$sortname = 'id'; // Sort column
$sortorder = 'asc'; // Sort order
$qtype = ''; // Search column
$query = ''; // Search string
// Get posted data
if (isset($_POST['page'])) {
$page = mysql_real_escape_string($_POST['page']);
}
if (isset($_POST['sortname'])) {
$sortname = mysql_real_escape_string($_POST['sortname']);
}
if (isset($_POST['sortorder'])) {
$sortorder = mysql_real_escape_string($_POST['sortorder']);
}
if (isset($_POST['qtype'])) {
$qtype = mysql_real_escape_string($_POST['qtype']);
}
if (isset($_POST['query'])) {
$query = mysql_real_escape_string($_POST['query']);
}
if (isset($_POST['rp'])) {
$rp = mysql_real_escape_string($_POST['rp']);
}
// Setup sort and search SQL using posted data
$sortSql = "order by $sortname $sortorder";
$searchSql = ($qtype != '' && $query != '') ? "where $qtype = '$query'" : '';
// Get total count of records
$sql = "select count(*)
from staff
$searchSql";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
$total = $row[0];
// Setup paging SQL
$pageStart = ($page-1)*$rp;
$limitSql = "limit $pageStart,$rp";
// Return JSON data
$data = array();
$data['page'] = $page;
$data['total'] = $total;
$data['rows'] = array();
$sql = "select id,first_name,surname,position
from staff
$searchSql
$sortSql
$limitSql";
$results = mysql_query($sql);
while ($row = mysql_fetch_assoc($results)) {
$data['rows'][] = array(
'id' => $row['id'],
'cell' => array($row['id'],$row['first_name'],$row['surname'],$row['position'])
);
}
echo json_encode($data);
?>


There’s nothing too complicated here. We simply connect to a MySQL database (substitute the connection variables for the values corresponding to your own database),create the SQL from the data supplied by Flexigrid,get the number of records in the entire result set and return the results in JSON format using the json_encode function which is available to PHP versions 5.2.0 and later.

Flexigrid requires a few parameters to be passed back in the JSON array. These are:

BUTTONS

In the javascript listing above we specified that the doCommand function should be called when either the ‘edit’ or ‘delete’ buttons are clicked on. How does Flexigrid know which button has been clicked on? Two parameters are passed to the function. These are the name of the command which in this case can be either ‘Edit’ or ‘Delete’,and the grid object.

All we are concerned with doing in this tutorial is finding an id for the selected row. When we have this we can easily perform whatever operation we wish on the data for the selected record using e.g. AJAX techniques which are beyond the scope of this tutorial.

function doCommand(com,grid) {
if (com == 'Edit') {
$('.trSelected',grid).each(function() {
var id = $(this).attr('id');
id = id.substring(id.lastIndexOf('row')+3);
alert("Edit row " + id);
});
} else if (com == 'Delete') {
$('.trSelected',grid).each(function() {
var id = $(this).attr('id');
id = id.substring(id.lastIndexOf('row')+3);
alert("Delete row " + id);
});
}
}


As you can see it is just a case of finding the selected row using jQuery. We then extract the numeric id of the record from the id of the HTML ‘tr’ element. You are free from this point on to implement the editing and adding functions however you’d like e.g. using?jQuery UI?dialogs.

CONCLUSION

This tutorial has looked into the use of the freely available Flexigrid plugin for jQuery. We have found documentation to be lacking but hopefully this tutorial has gone some way to helping people understand this handy extension. If you’d like to find out more I recommend that you take a look at the source code (flexigrid.js).

If you find Flexigrid useful I suggest you make a small donation to the creator to help maintain the project atwww.flexigrid.info.