Wednesday, June 15, 2011

OpenJS Grid

Why did I make my own jQuery grid?
So any decent developer will go and look for something before making their own.  So throughout the years I've used several jQuery grid tools.  And these really aren't grids but data tables.  Anyway, I tried jqGrid, flexGrid, and even slickGrid.  We even made our own simple one once upon a time.

Why aren't these good?
It's not that they aren't good.  It's that they aren't easy.  Easy to use, easy to implement, easy to modify.  Here is the code to use jqGrid.

Note: This is copy and pasted from their site for their "simple" example.

Java Scrpt code ... jQuery("#list2").jqGrid({ url:'server.php?q=2', datatype: "json", colNames:['Inv No','Date', 'Client', 'Amount','Tax','Total','Notes'], colModel:[ {name:'id',index:'id', width:55}, {name:'invdate',index:'invdate', width:90}, {name:'name',index:'name asc, invdate', width:100}, {name:'amount',index:'amount', width:80, align:"right"}, {name:'tax',index:'tax', width:80, align:"right"}, {name:'total',index:'total', width:80,align:"right"}, {name:'note',index:'note', width:150, sortable:false} ], rowNum:10, rowList:[10,20,30], pager: '#pager2', sortname: 'id', viewrecords: true, sortorder: "desc", caption:"JSON Example" }); jQuery("#list2").jqGrid('navGrid','#pager2',{edit:false,add:false,del:false});
PHP with MySQL

$page = $_GET['page']; // get the requested page
$limit = $_GET['rows']; // get how many rows we want to have into the grid
$sidx = $_GET['sidx']; // get index row - i.e. user click to sort
$sord = $_GET['sord']; // get the direction
if(!$sidx) $sidx =1;
// connect to the database
$db = mysql_connect($dbhost, $dbuser, $dbpassword)
or die("Connection Error: " . mysql_error());

mysql_select_db($database) or die("Error conecting to db.");
$result = mysql_query("SELECT COUNT(*) AS count FROM invheader a, clients b WHERE a.client_id=b.client_id");
$row = mysql_fetch_array($result,MYSQL_ASSOC);
$count = $row['count'];

if( $count >0 ) {
 $total_pages = ceil($count/$limit);
} else {
 $total_pages = 0;
if ($page > $total_pages) $page=$total_pages;
$start = $limit*$page - $limit; // do not put $limit*($page - 1)
$SQL = "SELECT, a.invdate,, a.amount,,,a.note FROM invheader a, clients b WHERE a.client_id=b.client_id ORDER BY $sidx $sord LIMIT $start , $limit";
$result = mysql_query( $SQL ) or die("Couldn t execute query.".mysql_error());

$responce->page = $page;
$responce->total = $total_pages;
$responce->records = $count;
while($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
echo json_encode($responce);
Now that is a lot of code right?  And that is just their simple example.  This isn't even going into say row editing or anything.

So ok, lets look at flex grid

        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

So much better right? This is much shorter. But really who wants all that javascript config when you just want a table? And... This is just the javascript. Here is the required PHP you need to write everytime.

PHP with Mysql

< ?php
$page = isset($_POST['page']) ? $_POST['page'] : 1;
$rp = isset($_POST['rp']) ? $_POST['rp'] : 10;
$sortname = isset($_POST['sortname']) ? $_POST['sortname'] : 'name';
$sortorder = isset($_POST['sortorder']) ? $_POST['sortorder'] : 'desc';
$query = isset($_POST['query']) ? $_POST['query'] : false;
$qtype = isset($_POST['qtype']) ? $_POST['qtype'] : false;

$usingSQL = true;
function runSQL($rsql) {

        $db['default']['hostname'] = "localhost";
        $db['default']['username'] = '';
        $db['default']['password'] = "";
        $db['default']['database'] = "";

        $db['live']['hostname'] = 'localhost';
        $db['live']['username'] = '';
        $db['live']['password'] = '';
        $db['live']['database'] = '';

        $active_group = 'default';

        $base_url = "http://".$_SERVER['HTTP_HOST'];
        $base_url .= str_replace(basename($_SERVER['SCRIPT_NAME']),"",$_SERVER['SCRIPT_NAME']);

        $connect = mysql_connect($db[$active_group]['hostname'],$db[$active_group]['username'],$db[$active_group]['password']) or die ("Error: could not connect to database");
        $db = mysql_select_db($db[$active_group]['database']);

        $result = mysql_query($rsql) or die ($rsql);
        return $result;

function countRec($fname,$tname) {
        $sql = "SELECT count($fname) FROM $tname ";
        $result = runSQL($sql);
        while ($row = mysql_fetch_array($result)) {
                return $row[0];

$sort = "ORDER BY $sortname $sortorder";
$start = (($page-1) * $rp);

$limit = "LIMIT $start, $rp";

$where = "";
if ($query) $where = " WHERE $qtype LIKE '%".mysql_real_escape_string($query)."%' ";

$sql = "SELECT iso,name,printable_name,iso3,numcode FROM country $where $sort $limit";
$result = runSQL($sql);

$total = countRec("iso","country $where");

        include dirname(__FILE__).'/';
        if($qtype && $query){
                $query = strtolower(trim($query));
                foreach($rows AS $key => $row){
                        if(strpos(strtolower($row[$qtype]),$query) === false){
        //Make PHP handle the sorting
        $sortArray = array();
        foreach($rows AS $key => $row){
                $sortArray[$key] = $row[$sortname];
        $sortMethod = SORT_ASC;
        if($sortorder == 'desc'){
                $sortMethod = SORT_DESC;
        array_multisort($sortArray, $sortMethod, $rows);
        $total = count($rows);
        $rows = array_slice($rows,($page-1)*$rp,$rp);
header("Content-type: application/json");
$jsonData = array('page'=>$page,'total'=>$total,'rows'=>array());
foreach($rows AS $row){
        //If cell's elements have named keys, they must match column names
        //Only cell's with named keys and matching columns are order independent.
        $entry = array('id'=>$row['iso'],
        $jsonData['rows'][] = $entry;
echo json_encode($jsonData);

My Solution

Now Flex grid seems just as bad as jqGrid.
Before, I get too far ahead. YES you could wrap this all in some file and replace all the variables, and do a database config, and then god forbid you want searching. Oy.  And I know it looks messy here, but it looks just as messy in their source.

So now you should understand why these grid solutions are not ideal for most people.  Here is my solution

<!DOCTYPE html>
  <link rel="stylesheet" type="text/css" href="grid.css">
  <script src="jquery.js"></script>
  <script src="grid.js"></script>
   $(function() {
  <table class="grid" title="Grid" action="ajax.php">
    <th col="feature">Feature</th>
    <th col="version">Version</th>

< ?php
   $grid = new Grid("orders");
   echo json_encode($grid->data);
? >

And to note - This is nearly the FULL page code, not just a chunk like I pulled for the other guys.

How am I able to do this?
Classes and the power of object oriented programming.  Seriously, all I did was make 2 classes. 1 to handle all the mysql + database stuff.  And 1 in jQuery to handle all the client side behavior.  Thats all.  This code is all open source, spaced perfected, well commented, and free to use and play with.

Feel free to download it from  Over 2000 people already have. (Thanks for all those who support OpenJS Grid)


  1. Hi Sean, I am Jay Mackley (Allen's dad). I've needed a grid plugin for a project and I've used DataTables grid before but Allen recommended your OpenJSGrid. I tried it and really like it!! It's a very nice piece of work. It really is, like you say, easy to implement and modify.

    I've made a couple of minor improvements I thought I would share, if you are interested.

    1. The pager control only paged whole pages, not partially filled pages, so I changed the code below in grid.js (starting at row 1442):

    // if the previous page is gonna be 1, disabled the button
    if(this.currentPage - 1 <= 0) {
    } else {
    // if the next page is beyond the last page with any rows, disable the button
    if (nRows%showing == 0) {
    if(this.currentPage + 1 > nPages) {
    } else {
    if(this.currentPage > nPages) {

    2. The rowNumbers counted only the rows in the current page, so on line 941, I set i to the total row count of the beginning of the current page. This yields a row count that corresponds with the pager control count.

    //var = 0;
    var i = ((this.pager.currentPage - 1) * this.opts.nRowsShowing)

  2. Well, live and learn. That code at line 1442 doesn't handle the first page correctly. Should be:

    // if the previous page is gonna be 1, disabled the button
    if(this.currentPage <= 1) {
    // if the next page is beyond the last page with rows, disabled the button
    if (nRows%showing == 0) {
    if(this.currentPage + 1 > nPages) {
    } else {
    if(this.currentPage > nPages) {