Using PHP PDO with jQuery Flexigrid Plugin

Flexigrid by Paulo Mariñas in my opinion is the best data grid plugin for jQuery. It’s professional-looking, easy to implement, and straightforward. But one of the drawbacks that I encounter when implementing it in some of my projects is its use of the native PHP mysql functions. So I decided to create a PDO class for it to enhance its portability and ease of use. In this tutorial, I’m using version 1.4.2 1.2.3.

First of all, I needed to edit the main javascript plugin file itself: flexigrid.js. I added colsNameArray as seen below in line 19 of the javascript file:

p = $.extend({
colNamesArray: [],
height: 200, //default height
width: 'auto', //auto width
striped: true, //apply odd even stripes
novstripe: false,

And on line 607, so that we can easily pass the column names (‘colnames’) without needing to set it again in our server side script. Just make sure that you specify the correct column names:

var param = [
{ name : 'page', value : p.newp }
,{ name : 'rp', value : p.rp }
,{ name : 'sortname', value : p.sortname}
,{ name : 'sortorder', value : p.sortorder }
,{ name : 'query', value : p.query}
,{ name : 'qtype', value : p.qtype}
,{ name : 'colnames', value : p.colNamesArray} //this is the column names variable
];

So we’re done with flexigrid.js. Next will be the html file where we desire to display our cool flexigrid plugin.
Not much work here though, we’ll just need to make sure that all the parameters we need are there. Let’s focus on
its javascript section where we initialize our data grid. Please refer to the code comments:

$("#flex1").flexigrid
(
{
url: 'post3.php',
dataType: 'json',
colModel : [ //all of the table column names that we set here will be automatically detected without having to set them in our server-side script again
{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", //make sure that this is set, this will determine the sortname
sortorder: "asc",
usepager: true,
title: 'Countries',
useRp: true,
rp: 15,
showTableToggleBtn: true,
width: 700,
onSubmit: addFormData,
height: 200
}
);

And on post3.php:

include_once("FlexiPDO.php");
//change me -------
$host = "localhost";
$db = "test";
$user = "root";
$pass = "";
//-----------------

try {
    $dbh = new PDO("mysql:host=$host;dbname=$db", $user, $pass);   
}catch(PDOException $e) {
    echo $e->getMessage();
}

$table = "country";

$flxPDO = new FlexiPDO();

//only 2 parameters are required
//sortname and sortorder are both initialized in the flexigrid javascript call
//implement($table, $dbh, $sortname=null, $sortorder=null, $id=null)

$flxPDO->implement($table, $dbh);

As you can see above, we’ll only need to specify the table name and set the PDO parameters needed for the database connection.
The FlexiPDO.php class takes care of business, I know some sections of it need more cleanup, but hey it’s version 1.0.

Download:

SQL file for the sample:

See Demo – nothing different from the original. Just wanted to show you that it works. :p

Leave a comment ?

13 Comments.

  1. Hi,

    very nice function but why cannot find “hide/show” column?

    Regards,
    Terry

  2. Hi Terry, I believe I missed to include that one. I’ll update the file soon. Thanks!

    [WORDPRESS HASHCASH] The poster sent us ’0 which is not a hashcash value.

  3. the function not work with jQuery 1.4.2…

  4. @terry: thanks for your feedback. i’ll work on this as soon as i can. :smile:

  5. @terry: weird coz flexigrid itself doesn’t work with jQuery 1.4.2. so i suggest u can use 1.2.3 in this case.

  6. hi,

    very nice tutorial, thanks to author

    i need help, i want to take coulumn information dynamicaly on run time becuase i cann’t hard code, it

    how i can do this,(i will need loop inside jquery function).

    waiting for reply

  7. hi hakeem,
    sorry for the super late reply. been very busy with work lately. you can use colNamesArray from flexigrid.js to get the column names. i hope this helps.

    [WORDPRESS HASHCASH] The poster sent us ’0 which is not a hashcash value.

  8. Are you still working on improving this code ?????

  9. hi shelley, i haven’t worked on this lately. but yes, i’m planning to improve this. i’d be glad if you could help. ;-)

    [WORDPRESS HASHCASH] The poster sent us ’0 which is not a hashcash value.

  10. This is just what i need! Thanks!

    [WORDPRESS HASHCASH] The poster sent us ’0 which is not a hashcash value.

  11. thanks for this work

    [WORDPRESS HASHCASH] The poster sent us ’994879936 which is not a hashcash value.

  12. This has helped a lot

    [WORDPRESS HASHCASH] The poster sent us ’857444736 which is not a hashcash value.

Leave a Comment


× four = 8


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Trackbacks and Pingbacks: