So I looked for a solution that would create good looking grids with the minimun of fuzz. I found it in http://flexigrid.info/
It is a jquery ui extension that adds easy to create grids to a web site. The only draw back it is not well documented, especially if using xml data instead of the json data. I advice that you start by reading and seeing the examples on the http://flexigrid.info/ site so you can get started. But soon you will need to see some examples, especially with xml. These XML examples helpful to figure out how to use flexigrid with xml data.
First here is the link to a zip file with the source code for the examples.
The first example uses a static xml file. It just creates a grid on a page. Notice that I placed the grid inside a div container. This is the best method to size the height and width of the grid. Also note that I did not specified the width, so the grid will expand to all available width on the division. The height of the grid I control with javacode $("#GridArea").innerHeight()*.80 . This makes the grid 80% of the container leaving space for the navigation bar. You can also choose just to subtract a specific number of pixels instead.
<!DOCTYPE HTML>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Flexigrid Example 1</title>
<link href="/CSS/flexigrid.css" rel="stylesheet" type="text/css" />
<link rel="stylesheet" type="text/css" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.16/themes/ui-lightness/jquery-ui.css" />
<script language="javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.6.4/jquery.min.js"></script>
<script language="javascript" src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.16/jquery-ui.min.js"></script>
<script language="javascript" src="/js/flexigrid.js"></script>
<script type="text/javascript" >
$(function() {
$("#flex1").flexigrid({
url: 'zipcode_small.xml',
dataType: 'xml',
colModel : [
{display: 'City', name : 'City', width : 150, align: 'left'},
{display: 'State', name : 'StateName', width : 150, align: 'left'},
{display: 'Abbreviation', name : 'StateAbb', width : 40, align: 'left'},
{display: 'Zip', name : 'Zip', width : 50, align: 'left'},
{display: 'Latitude', name : 'Latitude', width : 100, align: 'left'},
{display: 'Longitude', name : 'Longitude', width : 100, align: 'left'},
{display: 'Timezone', name : 'Timezone', width : 100, align: 'left'},
{display: 'DST', name : 'DST', width : 50, align: 'left'}
],
rp: 20,
usepager: true,
title: "Zip code list",
useRp: true,
showTableToggleBtn: false,
resizable: true,
height: $("#GridArea").innerHeight()*.80,
singleSelect: true
});
});
</script>
</head>
<body>
<div id="content" style="width:600px; height:400px">
The flexigrid display a static xml file. added height: $("#GridArea").innerHeight()*.80 , to make the grid 80% og the height of the countainer to give espace for the page bar. You can also use height: $("#GridArea").innerHeight()-45, to do the same thing (substractiong 45 pixels to give it space).
<div id="GridArea" style="width:100%; height:100%">
<table id="flex1">
</table>
</div>
</div>
</body>
</html>
zipcode_small.xml page with the data. The xml is has to be formatted in a very specific way.Example:
<rows>
<page>x</page>
<total>xx</total>
<row id="1"><cell></cell><cell></cell></row>
<row id="2"><cell></cell><cell></cell></row>
</rows>
The <page> is the page of the record set the <total> is the number of records, then each row for the table with each cell of data for the table.
Here is this example page 2 on my website
Here is this example page 3 on my website
On example page 4 flexigrid4.php has two functions, have one that fires when a cell is clicked and another changes the color of the cell according to value (if the state is Florida or Georgia).
<!DOCTYPE HTML>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Flexigrid Example 4</title>
<link rel="stylesheet" type="text/css" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.16/themes/ui-lightness/jquery-ui.css" />
<script language="javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.6.4/jquery.min.js"></script>
<script language="javascript" src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.16/jquery-ui.min.js"></script>
<script language="javascript" src="/js/flexigrid.js"></script>
<link href="/CSS/flexigrid.css" rel="stylesheet" type="text/css" />
<script type="text/javascript" >$(function() {
$("#flex1").flexigrid({
url: 'vwzip.php',
dataType: 'xml',
colModel : [
{display: 'City', name : 'City', width : 150, sortable : true, align: 'left',process: procMe},
{display: 'State', name : 'StateName', width : 150, sortable : true, align: 'left',process: procMe},
{display: 'Abbreviation', name : 'StateAbb', width : 40, sortable : true, align: 'left',process: procMe},
{display: 'Zip', name : 'Zip', width : 50, sortable : true, align: 'left',process: procMe},
{display: 'Latitude', name : 'Latitude', width : 100, sortable : true, align: 'left',process: procMe},
{display: 'Longitude', name : 'Longitude', width : 100, sortable : true, align: 'left',process: procMe},
{display: 'Timezone', name : 'Timezone', width : 100, sortable : true, align: 'left',process: procMe},
{display: 'DST', name : 'DST', width : 50, sortable : true, align: 'left',process: procMe}
],
searchitems : [
{display: 'City', name : 'City'},
{display: 'State', name : 'StateName'},
{display: 'Zip', name : 'Zip', isdefault: true}
],
rp: 20,
sortname: "City",
sortorder: "asc",
usepager: true,
title: "Zip code list",
useRp: true,
showTableToggleBtn: false,
resizable: true,
height: $("#GridArea").innerHeight()*.80,
singleSelect: true,
onSuccess: function(){highlight()}
});
});function procMe(celDiv,id) {
$(celDiv).click(
function () {
alert('Row ID:'+(id)+' with cell value of '+celDiv.innerHTML);
}
);
};function highlight() {
var i=0;
for (i=0;i < document.getElementById("flex1").rows.length;i++)
{
var table=document.getElementById("flex1");
// cell numbers start with 0 since the state is the second column the cell number is 1
var State_To_Check=table.rows[i].cells[1].firstChild.innerHTML;
if (State_To_Check=="FLORIDA") {
table.rows[i].cells[1].firstChild.style.backgroundColor="#FF0";
}
if (State_To_Check=="GEORGIA") {
table.rows[i].cells[1].firstChild.style.backgroundColor="#00FF00";
}
}
}</script>
</head><body>
<div id="content" style="width:600px; height:400px">
<div id="GridArea" style="width:100%; height:100%">
<table id="flex1">
</table>
</div>
</div>
</body>
</html>
The code for the xml generating page is important. You must pass the following post variables for the navegation bar to be usefull: rp,page, sortorder,sortname,query, qtype
The variable are use for the following functions:
the rp sets the number of records per page
page is what page you are on.
sortorder = the order of field (asc for ascending, desc for descending)
sortname= the field to sort
query= the value to each for
qtype= the column to search for
in the example bellow you can see how these post variable work to select and order the xml data.
<?php require_once('../Connections/ExampleDB.php'); ?>
<?PHP
//page number of the grid
$pageNum_Recordset1 = 1;
if (isset($_POST['page'])) {
$pageNum_Recordset1 = mysql_real_escape_string($_POST['page']);
}
// sort order of the grid
$Recordset1_Order="";
if (isset($_POST['sortorder']) and isset($_POST['sortname'])) {
$SortBy_Recordset1= mysql_real_escape_string($_POST['sortname']);
$SortDirectionRecordset1 = mysql_real_escape_string($_POST['sortorder']);
$Recordset1_Order="Order by ".$SortBy_Recordset1." ".$SortDirectionRecordset1;
}
// passing the search values from the search box
$Search_column="";
$Search_value="";
$query_Recordset1_Where="";
if (isset($_POST['query']) and isset($_POST['qtype'])) {
$Search_column = trim(mysql_real_escape_string($_POST['qtype']));
$Search_value = trim(mysql_real_escape_string($_POST['query']));
$query_Recordset1_Where=" Where ".$Search_column." like '".$Search_value. "%' ";
}
// number of records on the grid
$maxRows_Recordset1 = 20;
if (isset($_POST['rp'])) {
$maxRows_Recordset1 = mysql_real_escape_string($_POST['rp']);
}
// idField is the firld name to use for row id key and for the cound of records
$idField = "Zip";
// basic query statements
$query_Statement_Recordset1 = "SELECT City,StateName,StateAbb,Zip,Latitude,Longitude,Timezone,DST FROM vwzip ";
$query_Count_Statement_Recordset1 = "SELECT COUNT(Zip) FROM vwzip ";
// start row is the page times the number of records per page
$startRow_Recordset1=0;
$startRow_Recordset1 = ($pageNum_Recordset1-1) * $maxRows_Recordset1;
// add filter (search) criteria
$query_Recordset1 = $query_Statement_Recordset1.$query_Recordset1_Where.$Recordset1_Order;
$query_Count_Recordset1 = $query_Count_Statement_Recordset1.$query_Recordset1_Where;
// limit to the page amount of records
$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
//open connection
mysql_select_db($database_ExampleDB, $ExampleDB);
// recordset with count
$all_Recordset1 = mysql_query($query_Count_Recordset1, $ExampleDB) or die(mysql_error());
$row = mysql_fetch_row($all_Recordset1);
$totalRows_Recordset1 = $row[0];
$totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;
// record set with data
$Recordset1 = mysql_query($query_limit_Recordset1, $ExampleDB) or die(mysql_error());// $row_Recordset1 = mysql_fetch_assoc($Recordset1);
header ("content-type: text/xml");$xml_output = '<?xml version="1.0" encoding="utf-8"?>';
$xml_output .= "<rows>";
$xml_output .= "<page>".$pageNum_Recordset1."</page>";
$xml_output .= "<total>".$totalRows_Recordset1."</total>";
for($x = 0 ; $x < mysql_num_rows($Recordset1) ; $x++){
$row = mysql_fetch_assoc($Recordset1);
$xml_output .= '<row id="'.$row[$idField].'">';
$i=0;
while ($i < mysql_num_fields($Recordset1)) {
$fn=mysql_field_name($Recordset1,$i);
// Escaping illegal characters
if (is_null($row[$fn])) { $fieldval=" ";} else {$fieldval = str_replace("&", "&", $row[$fn]);}
$fieldval = str_replace("<", "<", $fieldval);
$fieldval = str_replace(">", ">", $fieldval);
$fieldval = str_replace('"', """,$fieldval);
$fieldval = str_replace('"', """,$fieldval);
$fieldval = str_replace("'", """,$fieldval);
$xml_output .= "<cell>" .$fieldval . "</cell>";
$i++;
}
$xml_output .= "</row>";
}
$xml_output .= "</rows>";
echo $xml_output;
?>Normally, I do not query the database table directly, but use a view instead. This allows me to format the fields as I want (add numeric format, timezone adjustments, etc) and allows the Sql server to presort the data.
Here is this example page 5 on my website
Here is a example of a grid integrated in my website.
I hope this helps!!!!
