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.id, a.invdate, b.name, a.amount,a.tax,a.total,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;
$i=0;
while($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
    $responce->rows[$i]['id']=$row[id];
    $responce->rows[$i]['cell']=array($row[id],$row[invdate],$row[name],$row[amount],$row[tax],$row[total],$row[note]);
    $i++;
}        
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

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
});

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;
        mysql_close($connect);
}

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");

if(!isset($usingSQL)){
        include dirname(__FILE__).'/countryArray.inc.php';
        if($qtype && $query){
                $query = strtolower(trim($query));
                foreach($rows AS $key => $row){
                        if(strpos(strtolower($row[$qtype]),$query) === false){
                                unset($rows[$key]);
                        }
                }
        }
        //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'],
                'cell'=>array(
                        'name'=>$row['name'],
                        'iso'=>$row['iso'],
                        'printable_name'=>$row['printable_name'],
                        'iso3'=>$row['iso3'],
                        'numcode'=>$row['numcode']
                ),
        );
        $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

HTML & JS

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

PHP

< ?php
   require_once("grid.php");
   $grid = new Grid("orders");
   $grid->load();
   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 http://square-bracket.com/openjs  Over 2000 people already have. (Thanks for all those who support OpenJS Grid)