Flexigrid Tutorial

20 July 2009 by Andrew Irvine  
Filed under News and views

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 jQuery plugin so I decided to create this tutorial which might be of benefit to someone.

flexigrid

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, sortable : true, align: 'left'},
{display: 'Surname', name : 'surname', width : 150, sortable : true, align: 'left'},
{display: 'Position', name : 'email', width : 250, sortable : true, align: 'left'}
],
buttons : [
{name: 'Edit', bclass: 'edit', onpress : doCommand},
{name: 'Delete', bclass: 'delete', onpress : doCommand},
{separator: true}
],
searchitems : [
{display: 'First Name', name : 'first_name'},
{display: 'Surname', name : 'surname', isdefault: true},
{display: 'Position', 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.

Property Description
url This is a URL of the server-side script which provides a JSON or XML representation of the results to display in the grid using AJAX.
dataType You can choose to have your server-side script return either JSON or XML data.
colModel This is an array containing a list of columns to use. You can set the display name, the width, whether the column can be sorted, and text alignment.
buttons It is possible using this array to add buttons along the top of the Flexigrid specifying a callback function, e.g. you may want to create buttons to add, edit, and delete records. The bClass property is the CSS class used to set the background image for the button, etc.
searchItems Using this you can specify which columns to use for searching the results using the ‘quick search’ area. You simply specify a display name for the search option, the column name, and whether the column is the default search option.
sortname This property is used to specify the initial column to sort on.
sortorder Specifying either ‘asc’ or ‘desc’ for this property will set the initial sort order.
usepager The page navigation buttons can be turned on or off using this property.
title This is the title which will appear at the top of the Flexigrid.
useRp Whether to allow the user to specify the number of results per page.
rp The initial number of results per page.
showTableToggleBtn This will enable/disable minimisation of the Flexigrid with an icon in the top right corner.
width The width of the Flexigrid.
height The height of the Flexigrid.
singleSelect This undocumented property is used to indicate that only one row can be selected at a time. This is useful if you would like to create buttons such as edit or delete which apply only to a single row.

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.

Parameter Description
page Current page number.
sortname The name of the column to sort by.
sortorder The order to sort by – ‘asc’ or ‘desc’.
qtype The column selected during ‘quick search’.
query The text used within a search.
rp The number of records to be returned.

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:

Parameter Description
page The current page number.
total The total number of records in the result set. Used by Flexigrid to calculate the number of pages.
rows This is an array containing the data for the rows. Each row needs a unique id (used within the id for the HTML ‘tr’ element) and an array of column data.

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 at www.flexigrid.info.

Comments

92 Responses to “Flexigrid Tutorial”
  1. Blue says:

    Hi ,

    Can we make the columns width and height to occupy the left out space in the grid area automatically? Is that possible ?

    Thanks,
    Blue

  2. Anderson de Lima says:

    Thanks, man.

    Great tutorial!

  3. Ricky says:

    Great, very helpful, thank you.

  4. Christiano says:

    How to insert two lines in the thead?

    Ex.:
    head
    subhead

  5. Ponleu says:

    Hi everyone, I can’t add data into the grid. I follow the instruction but i could not work. please anyone help me. It just appears something like this on the bottom of the grid:

    {”rows”:[{"id":1,"cell":[1,"Noel Gallagher","Morning Glory","Dont look back in anger"]}]}

  6. how can i pass additional parameters to the JSON URL. I mean i want to provide my json certain parameters through flexigrid and based on those params show the output. I have been trying a lot on these for a while.

  7. sairio says:

    Caramba man estuve buscando un tutorial como este, me ha servido de mucho..thank you

  8. Joe says:

    How do I get rid of the drop down for hiding columns?

  9. Praveen says:

    HI need to integrate this flexigrid into zend frame work …. can any one help in this for me……

  10. Amar says:

    Hello do anyone have example for HTML page ?
    where i can insert the Flexgrid.
    Thank you in advance
    Amar

  11. Marcus says:

    Alguem sabe como faço para passar parametros para a função que está sendo chamada no onpress?

  12. Abhimanyu Singh says:

    Hi,
    I want to use radion button for each row in the grid. Is it possible. Please reply it’s urgent.

  13. ewr says:

    how to place a checkbox in flexigrid in php

  14. Dale says:

    Could not get this working. How does the function get triggered? I put this on my page , along with the little script you’ve provided, and I’m getting no action. I know my set up is correct, because I have it working by first creating a table with all the data, then putting this $(’.flexme1′).flexigrid(); in there, but I wanted to have it filled using JSON to make paging easier.

  15. emansj.net says:

    Can grid export the whole data into excel file ….?

  16. Ravi says:

    Hi,
    I am using following code but not able to get neither current page number nor rp value.

    $(”#flex1″).flexigrid({
    url: ‘XML/Customer.asp?Q=’,
    dataType: ‘xml’,
    colModel : [
    {display: 'ID', name : '0', width : 110, sortable : true, align: 'center'},
    {display: 'NAME', name : '1', width : 170, sortable : true, align: 'left'},
    {display: 'AGENT ID', name : '2', width : 80, sortable : true, align: 'center'},
    {display: 'AGENT', name : '3', width : 150, sortable : true, align: 'left'},
    {display: 'Joining Date', name : '4', width : 80, sortable : true, align: 'right'}
    ],
    sortname: “0″,
    sortorder: “asc”,
    usepager: true,
    title: ‘Customer List’,
    useRp: true,
    rp: 10,
    showTableToggleBtn: false,
    singleSelect: true,
    width: 655,
    height: 380
    });

    Plese help me to fix this.Thanks, Ravi

  17. Rohit says:

    The url parameter in the flexigrid example 3 using json points to server side implementation to get the json data results, if am not wrong(pls correct if).

    My question is can we pass the javascript variable holding the results ?? If so can some one quote an example ?? Further am using struts frame work, IE7(Buggy) but cant help as the user’s us it.

    Pls post an example if you ever tried this ??

  18. tomexsans says:

    I tried all your post and it did not work, no error, no nothing, Is this really working

  19. tomexsans says:

    http://pastebin.com/aPAYxAFa, here is My code could you please have a look what am i doing wrong thankyou

  20. adi says:

    i have a little example about to open form on add button clicked using jquery UI dialog.

    take a look here :
    http://adiresmawan.blogspot.com/2012/03/flexigrid-with-add-and-edit-button.html

  21. Anon says:

    Please post CREATE TABLE for the STAFF table. We could build it of course, but seeing as how you wanted to create a tutorial for flexigrid, I would think that you should also post the specifics of the STAFF table.

    Also, you should have a working version, forgive me if you have already posted that, but I am not seeing it.

    Lastly, you should have a zipped version of your demo.

    Thanks

  22. Kevin Holdridge says:

    Dear Anon (10 Mar). Thanks for your advice. I guess you could have created a 4 column db table in less time than it would have taken to post your comment. Happy coding. Kevin

  23. diocio says:

    Please help me. it’s not working. the flexigrid did not rendered the data.
    look at my code below.

    <html >

    <meta http-equiv="Content-Type" content="text/html; charset=”>

    <base href="”>

    $(document).ready(function() {
    $(function(){
    function doAction() {}

    $(”#grid1″).flexigrid({
    url: ‘barang.php’,
    dataType: ‘json’,
    colModel : [
    {display: 'Kode', name : 'kode_barang', width : 80, sortable : true, align: 'center'},
    {display: 'Nama Barang', name : 'nama_barang', width : 400, sortable : true, align: 'left'}
    ],
    buttons : [
    {name: 'Add', bclass: 'add', onpress : doAction},
    {separator: true},
    {name: 'Delete', bclass: 'delete', onpress : doAction},
    {separator: true}
    ],
    searchitems : [
    {display: 'Kode', name : 'kode_barang'},
    {display: 'Nama Barang', name : 'nama_barang', isdefault: true}
    ],
    sortname: “kode_barang”,
    sortorder: “asc”,
    usepager: true,
    title: ‘Table Barang’,
    useRp: true,
    rp: 15,
    showTableToggleBtn: false,
    width: 700,
    height: 300,
    singleSelect: true
    });

    })

    })

    ;

    ‘CODE1′,
    ‘cell’ => array(’CODE1′, ‘BARANG1′)
    );

    $data['rows'][] = array(
    ‘kode_barang’ => ‘CODE2′,
    ‘cell’ => array(’CODE2′, ‘BARANG2′)
    );

    $data['rows'][] = array(
    ‘kode_barang’ => ‘CODE3′,
    ‘cell’ => array(’CODE3′, ‘BARANG3′)
    );

    /*
    $sql = “SELECT * FROM tb_barang “;
    $barang_query = tep_db_query($sql);
    while ($row = tep_db_fetch_array($barang_query)) {
    $data['rows'][] = array(
    ‘kode_barang’ => $row['kode_barang'],
    ‘cell’ => array($row['kode_barang'], $row['nama_barang'])
    );
    }

    */
    echo json_encode($data);

    ?>

  24. Stephen says:

    I also cannot get this to work. It would be great if the author of the plugin would provide some documentation. Unbelievable.

  25. Hannah says:

    How can i disable row selection in flexigrid while an ajax call is ON and re-enable it back on success?

  26. sanjeev says:

    Hey, can anyone tell me how can remove the resizeable column functionality from my grid.???
    Thanks

  27. govy.b says:

    Hi,everyone. How to enable the buttons ?

  28. Philemon says:

    hai… Is there any settings so that i can freeze a column?? Thanks…

  29. saibharath says:

    great work man
    fantastic

  30. saibharath says:

    @Anon : http://phpentertainer.wordpress.com/2009/07/20/flexigrid-implementation-in-codeigniter/
    if u read through this u can know how to make it work

  31. Thank for the tutorial. I’ve been working with flexigrid for a while. I need an inline editor within flexigrid. Is it possible?

    Thanks in advance.

  32. thang says:

    Hi Everyone,

    How to formater data of a column?

    Like that:
    {display: ‘Joining Date’, name : ‘4′, width : 80, sortable : true, align: ‘right’,
    formatter:function(value){
    return converttodate(value);
    }
    }

    Thanks

  33. Samir Shah says:

    Caramba man estuve buscando un tutorial como este, me ha servido de mucho..thank you

  34. alcocraig says:

    I had trouble with the example.

    I got it working by swapping the following:

    mysql_real_escape_string
    for
    mysql_escape_string

    Not sure if it has an impact on security but it worked.

  35. saurabh rai says:

    its realy helpful thanks a lot

  36. 1050356 says:

    Hi!

    I want to know if there is a way to add a row in flexigrid to show there the total of a sum(Colunm) .

    How can i do that…

    Thanks

  37. Federico says:

    Hola. No me funciona el quicksearch. Escribo le doy enter y no me ubica en el registro. Alguien sabe como hacerlo funcionar? Gracias!
    .
    Hello. Quicksearch not working. I write, I press enter and can not find the record. Anyone know how to make it work? Thank you!

  38. Jeyaprakash says:

    hi friend,
    i am using this grid in asp.net when i am sorting it in ‘desc’ it only sort the current page ie) the first page reverse from 10 to 1 and the second page is reverse from 20 to 11 etc.It is possible to sort the data full ie) 50 to 1

  39. Jeyaprakash says:

    yes i got the answer i have to place the sorting method before fetch the data to the cell

  40. mahesh says:

    Thanks man. great work!!! helped me a lot….

Trackbacks

Check out what others are saying about this post...
  1. [...] Grazie per le indicazioni bumm. Ho risolto il problema applicando le indicazioni che ho trovato su QUESTO TUTORIAL modificando il mio codice in questo modo: [...]



Speak Your Mind

Tell us what you're thinking...
and oh, if you want a pic to show with your comment, go get a gravatar!