Thursday, October 20, 2011

Flexigrid examples using xml

Lately I have been using the jquery ui to improve the look and style on the web site I am developing for a company. But as good as jquery ui is what messing a big piece, grids!

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.

Here is this example page on my website and here is the code:

<!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>

The grid open the 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("&", "&amp;", $row[$fn]);}
$fieldval = str_replace("<", "&lt;", $fieldval);
$fieldval = str_replace(">", "&gt;", $fieldval);
$fieldval = str_replace('"', "&quot;",$fieldval);
$fieldval = str_replace('"', "&quot;",$fieldval);
$fieldval = str_replace("'", "&quot;",$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!!!!
Posted by at
Labels: , , ,

39 comments:

  1. AnonymousJanuary 30, 2012 at 10:28 AM

    Thanks this helped get my xml data source working yay!

    ReplyDelete
  2. AnonymousApril 5, 2013 at 9:03 PM

    Valuable info. Fortunate me I found your website by accident, and I'm shocked why this coincidence didn't came
    about earlier! I bookmarked it.

    Also visit my homepage :: Juegos Online Gratis

    ReplyDelete
  3. AnonymousApril 7, 2013 at 7:02 AM

    I don't even know how I ended up right here, but I thought this post was good. I do not realize who you're but
    certainly you're going to a famous blogger should you aren't already.
    Cheers!

    Take a look at my homepage ... bancuri psihologi

    ReplyDelete
  4. AnonymousApril 16, 2013 at 9:19 PM

    I really love your website.. Pleasant colors & theme.
    Did you build this amazing site yourself? Please reply back as I'm wanting to create my very own blog and would like to learn where you got this from or what the theme is called. Thank you!

    Look at my web-site sex scandal video

    ReplyDelete
  5. AnonymousMay 13, 2013 at 12:03 PM

    Any low strength consumption appliance may be powered a concern .

    solar panel system.

    my web blog - szambo betonowe

    ReplyDelete
  6. AnonymousMay 14, 2013 at 7:03 PM

    Rubber plantation was basically brought into United states of america by the Dutch
    from Indonesia.

    Here is my blog; imprezy integracyjne

    ReplyDelete
  7. AnonymousMay 15, 2013 at 12:18 AM

    Come to Kauai vacation home rental and thus rediscover yourself a lap of nature.



    Review my homepage ... imprezy integracyjne

    ReplyDelete
  8. AnonymousMay 15, 2013 at 1:30 AM

    Start a flower file (if wish already have one). Yes they played with stones 1 but
    that already been earlier than whom.

    My webpage - Organizacja Imprez Firmowych

    ReplyDelete
  9. AnonymousMay 16, 2013 at 6:10 AM

    It is the case that there continue to be definite differences between installing the couple of systems.


    Also visit my site: agencja detektywistyczna warszawa

    ReplyDelete
  10. AnonymousMay 16, 2013 at 7:02 AM

    It provides two in order to deploy the software package.
    The message popularity is high and consequently
    lacs of communiques can be sent out within minutes.

    my website :: agencja detektywistyczna

    ReplyDelete
  11. AnonymousMay 16, 2013 at 3:01 PM

    Go through the various sites that provide packages for Kerala plantation tours.
    The climate including Kerala was basically , ideal for your production of rubber.


    Also visit my blog post agencje ochrony

    ReplyDelete
  12. AnonymousMay 18, 2013 at 9:22 PM

    At those same time, that clean-up can also be irksome.
    An exercise-free day leaves your company feeling exhausted that includes sore
    (polluted) muscle mass tissue.

    my website - adwokat warszawa

    ReplyDelete
  13. AnonymousMay 19, 2013 at 7:36 PM

    We should always consider a few things when it comes down to installing all of.
    Any concentrated heat might be used as any kind of heat source
    to buy a conventional power verger.

    My website - organizacja wczasów

    ReplyDelete
  14. AnonymousMay 21, 2013 at 8:13 AM

    In most years, diamond sawing could get right down to 135 microns thin or possibly very much
    less.

    My web page :: tłumaczenia czeski Katowice

    ReplyDelete
  15. AnonymousMay 21, 2013 at 5:53 PM

    Hi there! Do you know if they make any plugins to safeguard

    against hackers? I'm kinda paranoid about losing everything I've
    worked hard on. Any recommendations?

    My weblog ... Mouse Click The Next Site

    ReplyDelete
  16. AnonymousMay 21, 2013 at 9:06 PM

    Thank you, I've just been searching for information about this

    subject for ages and yours is the greatest I have discovered so far. But, what about the

    conclusion? Are you sure about the source?

    Visit my webpage :: cristal park benidorm costa blanca spain

    ReplyDelete
  17. AnonymousMay 21, 2013 at 9:23 PM

    of course like your web site however you have to test the spelling on quite a few of your posts.
    Several of them are rife with

    spelling problems and I find it very troublesome to tell the

    truth then again I’ll definitely come

    again again.

    Also visit my web site: travel news hastings

    ReplyDelete
  18. AnonymousMay 21, 2013 at 10:06 PM

    There is noticeably a lot to

    realize about this. I believe you made certain good points in

    features also.

    Here is my web blog; http://Wiki.Dbjr.de/index.Php?Title=Benutzer:OteliaGray78

    ReplyDelete
  19. AnonymousMay 22, 2013 at 4:15 AM

    Excellent goods from you, man. I've understand your stuff previous to and

    you are just too fantastic. I really like what

    you've acquired here, certainly like what you are stating and the
    way in which you

    say it. You make it enjoyable and you still care for to keep it wise.
    I

    cant wait to read much more from you. This is really a great
    website.

    Also visit my website - gva property search

    ReplyDelete
  20. AnonymousMay 22, 2013 at 5:13 AM

    you may have a great weblog right

    here! would you wish to make some invite posts on my weblog?



    Also visit my blog post: http://www.armandomancini.com

    ReplyDelete
  21. AnonymousMay 22, 2013 at 7:18 PM

    Compare several designers and check out and about their quotations
    but written proposals well. It makes it possible to to perform
    considerably more with what you already possess.

    my blog post - agencja detektywistyczna

    ReplyDelete
  22. AnonymousMay 23, 2013 at 3:02 AM

    Walking gives they the ability, as well as , that a quite thing for our business all.

    In this way this particular immune system has always been improved and it's actually functions are elevated.

    Review my web-site: agencja detektywistyczna warszawa

    ReplyDelete
  23. AnonymousMay 23, 2013 at 5:53 AM

    Each day billions of children log onto the online world.
    Then human beings are transformed in instruments to musicians, mystic musicians.



    Also visit my webpage :: agencja detektywistyczna warszawa

    ReplyDelete
  24. AnonymousMay 23, 2013 at 4:04 PM

    You will feel virtually any little bit along with a sting.
    Continually be cognizant of when and how to make the plants with your organic garden
    a sufficient amount water.

    Also visit my web blog - biuro detektywistyczne

    ReplyDelete
  25. AnonymousMay 24, 2013 at 5:51 AM

    Have you ever thing to consider about printing across
    T-shirt and on what it all set out? Subsequently, you necessitate making a decision on a develop.


    My site :: biuro detektywistyczne

    ReplyDelete
  26. AnonymousMay 24, 2013 at 1:17 PM

    It requires off you fatigue and make you'll taste the financial success. We see here how IT people and software development are inter-linked.

    Also visit my web blog :: usługi detektywistyczne

    ReplyDelete
  27. AnonymousMay 25, 2013 at 7:19 PM

    You can bring into play a heat gun to soften caulk if it will water based.

    Abandon overnight, remove by using a solid spatula and also wipe with paper towels.


    Here is my website: detektywi warszawa

    ReplyDelete
  28. AnonymousMay 27, 2013 at 10:13 PM

    Additionally is where activities can start to
    obtain complicated. One type of permission is an
    unrestricted use of sounds for any you specific use.


    my web page: pobierowo

    ReplyDelete
  29. AnonymousMay 29, 2013 at 11:53 PM

    I was known to keep my new iphone 4 running for 4
    days during programs are due to.

    Here is my webpage - zespół muzyczny Poznań

    ReplyDelete
  30. AnonymousJune 3, 2013 at 1:03 PM

    "Actually I had planned on staying, sadly the fumes at the hands of all the pin hold in the oil tanks... Leave several size of liner for settling and attainable adjustments.

    my site ... ochrona przeciwpożarowa

    ReplyDelete
  31. AnonymousJune 5, 2013 at 8:29 AM

    The performance halted with a effervescent wine shower courtesy
    an Palms Pool little girls.

    Here is my web-site: candida

    ReplyDelete
  32. AnonymousJune 7, 2013 at 12:37 AM

    CeMAP training offered so that you can students would contain
    3 parts exam. They are proved to be unskilled and uneducated laborers working
    the actual planet labor industry.

    Feel free to visit my web-site ... borelioza

    ReplyDelete
  33. AnonymousJune 7, 2013 at 7:08 AM

    Them was the amount owed we used when we developed their universal roulette programme.


    Here is my homepage ... historia piwa

    ReplyDelete
  34. AnonymousJune 7, 2013 at 9:45 AM

    Simplicity, clarity and in-depth competence are the mainstays of sound assembly.


    Also visit my web-site; wycieczki last minute

    ReplyDelete
  35. AnonymousJune 8, 2013 at 11:27 AM

    The text tuition will focus on the student repeating all the phrases.
    Discovered entire chapters over at one time for continuity.


    Also visit my webpage: organizacja-wczasów.pl

    ReplyDelete
  36. AnonymousJune 10, 2013 at 1:48 PM

    Many a real estate provides swimming pool, hot water bath and internet storage facility too.
    Rates get started off at $555.00 pertaining to each night.

    My web blog :: www.biorezonans-warszawa.pl

    ReplyDelete
  37. AnonymousJune 11, 2013 at 11:22 PM

    There are several of lawyer stores in the globe offering their unique services.


    My blog - agencja detektywistyczna warszawa

    ReplyDelete
  38. AnonymousNovember 7, 2013 at 4:11 AM

    This is an awesome page that i landed on. Saved my day!

    ReplyDelete
  39. Mateusz DomańskiJanuary 21, 2020 at 2:05 PM

    Ciekawie opisane. Czekam na jeszcze więcej.

    ReplyDelete

Subscribe to: Post Comments (Atom)