Thursday, December 22, 2011

Using Flexigrid with PHP and MySQL


Flexigrid is a lightweight but rich data grid with resizable columns and a scrolling data to match the headers, plus an ability to connect to an xml based data source using Ajax to load the content.
Features :

  • Resizable columns
  • Resizable height and width
  • Sortable column headers
  • Cool theme
  • Can convert an ordinary table
  • Ability to connect to an ajax data source (XML and JSON[new])
  • Paging
  • Show/hide columns
  • Toolbar (new)
  • Search (new)
  • Accessible API

There are two formats you can get your data return to your page, in JSON or XML.

Follow this link, you can find a sample source code for returning in XML format, and for returning in JSON, follow here.

How it  works:
First, you have to these three lines to your html page

<link rel="stylesheet" type="text/css" href="flexigrid/css/flexigrid.css"> 
<script type="text/javascript" src="jquery.js"></script>
<script type="text/javascript" src="flexigrid/js/flexigrid.js"></script> 

Please make sure that you add  <script type="text/javascript" src="jquery.js"></script>  before <script type="text/javascript" src="flexigrid/js/flexigrid.js"></script> since it is using jquery to populate its grid.

then you can add the table definition which you want to show in your web page.
In this following example, the data will display in "flex1" table. 
And the data will be processing in post2.php file and return to your html page in JSON format.

<table id="flex1" style="display:none"></table> 
<script type="text/javascript"> 
$("#flex1").flexigrid({ 
        url: 'post2.php', 
        dataType: 'json', 
        colModel : [ 
                {display: 'ISO', name : 'iso', width : 40, sortable : true, align: 'center'}, 
                {display: 'Name', name : 'name', width : 180, sortable : true, align: 'left'}, 
                {display: 'Printable Name', name : 'printable_name', width : 120, sortable : true, align: 'left'}, 
                {display: 'ISO3', name : 'iso3', width : 130, sortable : true, align: 'left', hide: true}, 
                {display: 'Number Code', name : 'numcode', width : 80, sortable : true, align: 'right'} 
                ], 
        searchitems : [ 
                {display: 'ISO', name : 'iso'}, 
                {display: 'Name', name : 'name', isdefault: true} 
                ], 
        sortname: "iso", 
        sortorder: "asc", 
        usepager: true, 
        title: 'Countries', 
        useRp: true, 
        rp: 15, 
        showTableToggleBtn: true, 
        width: 700, 
        onSubmit: addFormData, 
        height: 200 
}); 

To get to the point here, please follow below steps to use Flexigrid in your webpage (in php or any languages) with MySQL or any databases.
Let's assume that you already had jquery.js in your root directory.
Step 1:
<link rel="stylesheet" type="text/css" href="flexigrid/css/flexigrid.css"> 
<script type="text/javascript" src="jquery.js"></script>
<script type="text/javascript" src="flexigrid/js/flexigrid.js"></script> 

Step 2:
<table id="flex1" style="display:none"></table> 
<script type="text/javascript"> 
$("#flex1").flexigrid({ 
        url: 'post2.php', 
        dataType: 'json', 
        colModel : [ 
                {display: 'ISO', name : 'iso', width : 40, sortable : true, align: 'center'}, 
                {display: 'Name', name : 'name', width : 180, sortable : true, align: 'left'}, 
                {display: 'Printable Name', name : 'printable_name', width : 120, sortable : true, align: 'left'}, 
                {display: 'ISO3', name : 'iso3', width : 130, sortable : true, align: 'left', hide: true}, 
                {display: 'Number Code', name : 'numcode', width : 80, sortable : true, align: 'right'} 
                ], 
        searchitems : [ 
                {display: 'ISO', name : 'iso'}, 
                {display: 'Name', name : 'name', isdefault: true} 
                ], 
        sortname: "iso", 
        sortorder: "asc", 
        usepager: true, 
        title: 'Countries', 
        useRp: true, 
        rp: 15, 
        showTableToggleBtn: true, 
        width: 700, 
        onSubmit: addFormData, 
        height: 200 
}); 

**if you want to use xml, just replace 
dataType: 'json' with datatype:'xml'.

Steps 3: Create the processing page in php or any language you like.
Here is the sample pages in php format. please find json and xml

You can use $("#flex1").flexReload(); to reload or refresh your flexigrid. But you have to put it in right location in order to use it effectively.

And you can find full sample source codes for flexigrid at https://code.google.com/p/flexigrid/source/browse/#svn%2Ftrunk%2Fdemo

Download flexigrid directly here. or go to http://flexigrid.info for more detail.

Be remind yourself not to forget ; semi-colon and cases since both javascript and php are case-sensitive scripting engines.

All the best!!! Enjoy!!!

5 comments:

Anonymous said...

In step 2,where do you exactly insert the JavaScript code in a html file..Between what tags?
here is sample code from my html page

LNK said...

You can put just like I put in this sample, just after table. You can also put between head tags.

Anonymous said...

i've followed all the instructions precisely in calling all necessary files but even after placing the javascript file i still cant see the j-querry table..Please help

Anonymous said...

step 1 and step 2 looks like same..can you please tell me the difference between them.

LNK said...

hi there.. mr/ms anonymous.
step 1 is calling library files to use
step 2 is using those libraries to produce the grid. thanks a lot for the comment :)