Skip to content

jqGrid class in deep

Overview

As mentioned before in this guide jqGrid class is responsible for:

  • Fetch and send data from the server to the grid in different formats - json and xml
  • Sorting
  • Paging
  • Searching
  • Working with dates
  • Simple Subgrid
  • Logging

This class is the kernel of the component and provide base features of the grid. All other classes inherits this class in order to provide extended functionality. This class does not create automatically the grid, but acts as gateway between the already created jqGrid with JavaScript code and the database.

Important

Using this class requires some JavaScript knowledge, since the grid should be created in an existing html or php page with JavaScript declaration. In other words, the jqGrid class will only take care of the server side operations, while all other events and operations at the client side can be defined manually by the developer. More information on jqGrid javascript documentation can be found here

Important

All the functionalities in the examples for the jqGrid base class can be achieved with less programming efforts using the Render class (skipping creation of JavaScript code) . Here we demonstrate these features in case you will to use this class and have more control into the code.

In order to illustrate how the jgGrid class can be used, please take a look at the following example:

Note

all examples used in the documentation use the Northwind database, which is included in the installation (download) package as SQL dump.

Basic example

Create an html file with following content (note, that you will need to declare the grid with javascript if you are using the jqGrid class):

<!DOCTYPE html>
<html lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>PHP jqGrid Class Example</title> 

<link rel="stylesheet" type="text/css" media="screen" href="path_to_dist_dir/css/ext/jquery-ui.css" />
<link rel="stylesheet" type="text/css" media="screen" href="path_to_dist_dir/css/ui.jqgrid.css" />

<script src="path_to_dist_dir/js/ext/jquery.min.js" type="text/javascript"></script>
<script src="path_to_dist_dir/js/i18n/grid.locale-en.js" type="text/javascript"></script>
<script src="path_to_dist_dir/js/jquery.jqGrid.min.js" type="text/javascript"></script>
<script type="text/javascript">
jQuery(document).ready(function(){
// Craeate the grid manually
jQuery("#grid").jqGrid({
"colModel":[
     {"name":"OrderID","index":"OrderID","label":"ID","width":60, "key":true},
     {"name":"OrderDate","index":"OrderDate"},
     {"name":"CustomerID","index":"CustomerID"},
     {"name":"Freight","index":"Freight"},
     {"name":"ShipName","index":"ShipName"}
],
// point to the grid.php file
"url":"grid.php",
"datatype":"json",
"jsonReader":{repeatitems:false},
"pager":"#pager"
});
// Set navigator with search enabled.
jQuery("#grid").jqGrid('navGrid','#pager',{add:false,edit:false,del:false});
});
</script>

</head>
<body>
<table id="grid"></table>
<div id="pager"></div>
</body>
</html>

The grid.php file have the following code:

<?php
require_once 'path_to_your_project_vendor_dir/autoload.php';

use Guriddo\Utils\Utils;

// Connection to the server
$conn = new PDO('mysql:host=localhost;dbname=northwind','user','password');
// Tell the db that we use utf-8
$conn->query("SET NAMES utf8");

// Create the jqGrid instance
$grid = new Guriddo\jqGrid\Render\Render($conn, 'pdo');

// Write the SQL Query
$grid->SelectCommand = 'SELECT OrderID, OrderDate, CustomerID, Freight, ShipName FROM orders';
$grid->dataType = "json";
$grid->queryGrid();

Notice that after creation of the grid the sorting, paging and searching are working without additional programming.

The link between the grid php code and the JavaScript grid is the url option of the JavaScript grid, which points to the grid.php file.

Variables and methods

Because the grid is dynamic component with active development, the description of all variables and methods are documented in our api documentation and we do not provide it in this guide. You can get the needed information from here

We will notice here the main method of the class queryGrid .

The method has the following parameters:

public function queryGrid( array $summary=null, array $params=null, $echo=true)

The first parameter $summary is array which describe the summary fields in the grid; The second parameter $params is array which pass the parameter values defined in the SQL command (SelectCommand). The third parameter $echo describes if the result from the query should be echoed or returned as string. See below more detailed explanation of these parameters.

Summary fields and parameters

Summary fields

Summary fields is useful feature of the grid. The purpose of this feature is to display different statistical information in the footer row of the grid. The default summary operation is the SUM (summary) SQL function, but you can use any other database supported function like AVG (average), MIN, MAX, etc. There is no special variable in the grid that controls this behavior. The summary array should be passed directly to the methods listed below with a parameter named $summary

The structure of the array is:

array("colname"=>"databaseexp")

or

array("colname"=>array("databaseexp"=>"operation"));

where

  • colname is the name corresponding to the name in colModel
  • databaseexp is either database field or database expresion like field1+field2
  • operation is the database statistical function like SUM, AVG, MIN, MAX, COUNT...

If "operation" is not explicitly used, we assume that SUM function is used.

For example, if your SQL statement looks like this:

$grid->SelectCommand = "SELECT a, b , c+d AS e FROM table";

and you want the summary of c and d field to be displayed in the grid, the array should look like this:

$mysumarray = array('e'=>array("c+d"=>"SUM"));

Let's illustrate this using our Basic example.

Add the following two new options in the grid javascript definition

jQuery("#grid").jqGrid({
...
"footerrow":true,
"userDataOnFooter": true
});

and modify the grid.php file like this

<?php
require_once 'path_to_your_project_vendor_dir/autoload.php';

use Guriddo\Utils\Utils;

// Connection to the server
$conn = new PDO('mysql:host=localhost;dbname=northwind','user','password');
// Tell the db that we use utf-8
$conn->query("SET NAMES utf8");

// Create the jqGrid instance
$grid = new Guriddo\jqGrid\Render\Render($conn, 'pdo');

// Write the SQL Query
$grid->SelectCommand = 'SELECT OrderID, OrderDate, CustomerID, Freight, ShipName FROM orders';
$grid->dataType = "json";
// define the average of Freight field
$avg = array('Freight'=>array('Freight'=>'AVG'));
$grid->queryGrid($avg);

Note the two additional settings in the grid definition - footerrow and userDataOnFooter. The first setting enables the footer row placed above the pager and the second parameter instruct grid to put the calculated value at the footer Freight row.

Parameters

In most cases the SQL should obtain dynamically parameters from other variables. This mean that the query should be parametrized. As can be seen this can be done in the same query, but we highly recommend to use the jqGrid feature to pass the parameter values to the method and not to build your own query.

The reason for this is that the query is prepared before it is executed and the parameters are bound to the query for performance and security reasons (e.g in order to prevent SQL Injection attacks)

To use the parameters usually is needed to use the ? placeholder after the field prefixed with a logical operator.

Using our Basic example let say that we want to display rows with OrderID bigger than a certain number (session variable) which should be set before executing the script. The script can look like this:

<?php
require_once 'path_to_your_project_vendor_dir/autoload.php';

use Guriddo\Utils\Utils;

// Connection to the server
$conn = new PDO('mysql:host=localhost;dbname=northwind','user','password');
// Tell the db that we use utf-8
$conn->query("SET NAMES utf8");

// Create the jqGrid instance
$grid = new Guriddo\jqGrid\Render\Render($conn, 'pdo');

// get the variable
$param1 = $_SESSION['ordernum'];

// Write the SQL Query
$grid->SelectCommand = 'SELECT OrderID, OrderDate, CustomerID, Freight, ShipName FROM orders WHERE OrderID > ? ';
$grid->dataType = "json";

$rarameters = array($param1);
// pass the parameter
$grid->queryGrid(null, $parameters);

All the parameters should be passed within an array and the number of the placeholders ? should equal the length of the array. You can pass as many parameters as you want.

Note that the array is passed as second argument in the queryGrid method.

Queries

It is possible to write the SQL query in three different type depending on the conditions. The are as follow

  • reading query from XML file
  • using SelectCommand property
  • using table property

These are achieved with the following public varaiables: $readFromXML, $SelectCommand, $table

The logic in which the reading query is performed is:

  1. First we look if the $readFromXML variable if is set to true. If the variable is true we read the query from the XML file (see below).
  2. If the $readFromXML is false we look at $SelectCommand.
  3. If the $SelectCommand is empty at end we look for $table

Read query from XML

In order to read the query from XML we should use two variables - $readFromXML and $SelectCommand. The $readFromXML should be set to true and the $SelectCommand should meet some conditions as described below:

SelectCommand should be set as xmlfile.sqlid. The xmlfile is the name of xml file where we store the sql commands, sqlid is the id of the required sql.

Note

​ The xmlfile set in the SelectCommand should not end with xml, but the same file should have xml extension

The xml file have the following structure

 <?xml version="1.0" encoding="UTF-8"?>
 <queries>
     <sql Id="selecid1" table="table1" primary="primary1">
        Select_command1
    </sql>
     ...
     <sql Id="selecidN" table="tableN" primary="primaryN">
        Select_commandN
    </sql>
</queries>

Where the Id is the identifier of the sql command, table (not required) is the table element used in editing, primary (not required) is the primary key of the table or a unique key to be set as grid row id.

Let suppose that we want not to display the Freight field using our Basic example. Create a file groupright.xml which should look like this:

< ?xml version="1.0" encoding="UTF-8"?>
 <queries>
     <sql Id="allfields">
        SELECT OrderID, OrderDate, CustomerID, Freight, ShipName FROM orders
    </sql>
    <sql Id="limitedfields">
        SELECT OrderID, OrderDate, CustomerID, ShipName FROM orders
    </sql>
     ...
 </queries>

Save the file in certain location and write the following PHP script

<?php
require_once 'path_to_your_project_vendor_dir/autoload.php';

use Guriddo\Utils\Utils;

// Connection to the server
$conn = new PDO('mysql:host=localhost;dbname=northwind','user','password');
// Tell the db that we use utf-8
$conn->query("SET NAMES utf8");

// Create the jqGrid instance
$grid = new Guriddo\jqGrid\Render\Render($conn, 'pdo');

$group = $_SESSION['usergroup'];

// Enable reading from XML
$grid->readFromXML = true;
// Set the query based on the condition 
if($group <= 3)
    $grid->SelectCommand = 'pathtothefile/groupright.allfields';
else 
    $grid->SelectCommand = 'pathtothefile/groupright.limitedfields';

$grid->dataType = "json";
$grid->queryGrid();

$SelectCommand and $table properties are strings describing the SQL command or SQL table.

Working with charsets

By default Guriddo jqGrid is configured to work with utf-8 charset, but in some cases this is not applicable for existing legacy applications. In order to work with char set different than utf-8, you need to change is the $encoding variable.

However, the are a few steps that you need to do in addition to that to make sure everything works as expected:

  1. In the html or php file where the grid is constructed, the appropriate meta tag should be set.
...
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1251" />
...
</head>
...
  1. The connection command to the database should be set in order characters to be displayed properly

For MySQL and PostgreSQL this is a

SET NAMES 'charset'

This command should be executed before interacting with the grid.

For Microsoft SQL Server the connection info string should contain the charset setting as well:

<?php
// Example connection
$connectionInfo = array("UID" => 'user', "PWD" => 'password',"Database"=>"test", "CharacterSet" => "windows-1251", "ReturnDatesAsStrings" => true);
$serverName = "localhost\SQLEXPRESS";
$conn = sqlsrv_connect( $serverName, $connectionInfo);
...
// include the jqGrid Class
require_once "php/jqGrid.php";
// include the SQL Server driver class
require_once "php/jqGridSqlsrv.php";
...
?>
  1. And at end we should set $encoding variable in the grid. When this command is set the grid executes a PHP header function to send the appropriate header to the browser and more importantly - in case of 'json' output uses his own encode (when charset is <> utf-8). This is need because the built-in PHP functions for json encoding and decoding are utf-8 compatible only.
<?php
    ...
    // Set the encoding
    $grid->encoding = "windows-1251";
    ...
    $grid->queryGrid();

Working with dates

jqGrid provides an elegant way to work with dates. The reason for this special handling is that the date format that the underlaying database accepts in most cases differs from those presented on the client side.

We consider two types of dates - Date and DateTime.
The default format of Date and DateTime types set in the class on the server-side is Y-m-d for date and Y-m-d H:i:s for datetime.

The default format of Date and DateTime types on the client-side(javascript) is d/m/Y and d/m/Y H:i:s

For more information on date formatting in PHP, please refer to here

You can change these formats using the following methods:
setDbDate
setDbTime
for the underlying database and

setUserDate
setUserTime

for the client side representation.

In order to synchronize date formats between the client and server, a datearray variable should be used.

This is an array which tells the script which fields are dates. The value(s) in the array should correspond to the name in colModel This conversion is used when a search or CRUD (create, read, update, delete) operations are performed

In order to ilistrate this we again will refere to rhis example.

Let's suppose that the date format accepted from the server is Y-m-d and at client the format should be m/d/Y.

In order to display and search correctly the html file should be changed this way.

<!DOCTYPE html>
<html lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>PHP jqGrid Class Example</title> 

<link rel="stylesheet" type="text/css" media="screen" href="path_to_dist_dir/css/ext/jquery-ui.css" />
<link rel="stylesheet" type="text/css" media="screen" href="path_to_dist_dir/css/ui.jqgrid.css" />

<script src="path_to_dist_dir/js/ext/jquery.min.js" type="text/javascript"></script>
<script src="path_to_dist_dir/js/i18n/grid.locale-en.js" type="text/javascript"></script>
<script src="path_to_dist_dir/js/jquery.jqGrid.min.js" type="text/javascript"></script>
<script type="text/javascript">
jQuery(document).ready(function(){
// Craeate the grid manually
jQuery("#grid").jqGrid({
"colModel":[
     {"name":"OrderID","index":"OrderID","label":"ID","width":60, "key":true},
     {"name":"OrderDate","index":"OrderDate" "formatter":"date", "formatoptions":{"srcformat":"Y-m-d", "newformat":"m/d/Y"}},
     {"name":"CustomerID","index":"CustomerID"},
     {"name":"Freight","index":"Freight"},
     {"name":"ShipName","index":"ShipName"}
],
// point to the grid.php file
"url":"grid.php",
"datatype":"json",
"jsonReader":{repeatitems:false},
"pager":"#pager"
});
// Set navigator with search enabled.
jQuery("#grid").jqGrid('navGrid','#pager',{add:false,edit:false,del:false});
});
</script>

</head>
<body>
<table id="grid"></table>
<div id="pager"></div>
</body>
</html>

The PHP file 

<?php
require_once 'path_to_your_project_vendor_dir/autoload.php';

use Guriddo\Utils\Utils;

// Connection to the server
$conn = new PDO('mysql:host=localhost;dbname=northwind','user','password');
// Tell the db that we use utf-8
$conn->query("SET NAMES utf8");

// Create the jqGrid instance
$grid = new Guriddo\jqGrid\Render\Render($conn, 'pdo');

// Write the SQL Query
$grid->SelectCommand = 'SELECT OrderID, OrderDate, CustomerID, Freight, ShipName FROM orders';
$grid->dataType = "json";

// set the user date format to m/d/Y
$grid->setUserDate('m/d/Y');
// tell the grid which field is date
$grid->datearray = array('OrderDate');

$grid->queryGrid();

Related methods to manipulate dates

getDbDate
getDbTime
setDbDate
setDbTime
getUsedDate
getUserTime
setUserDate
setUserTime

Related variables

datearray

Post Processing

It is possible to modify or/and add new data after it is obtained from the database and before it is send to the browser (client).

This processing should be used with care since it add additional loop of all the fetched data and can cause a delay.

To use this two variables can be used - customFunc and customClass

customFunc - function which can be called to modify the grid output. Parameters passed to this function are the response object and the db connection

customClass - can be used again with custom function customFunc. We can call this using static defined functions in class customClass::customFunc - i.e $grid->customClass = Custom, $grid->customFunc = myfunc or $grid->customClass = new Custom(), $grid->customFunc = myfunc

The example below color the Freight field in read when it is less than 1 and and format it with php number function.

<?php
require_once 'path_to_your_project_vendor_dir/autoload.php';

use Guriddo\Utils\Utils;

// Connection to the server
$conn = new PDO('mysql:host=localhost;dbname=northwind','user','password');
// Tell the db that we use utf-8
$conn->query("SET NAMES utf8");

// Create the jqGrid instance
$grid = new Guriddo\jqGrid\Render\Render($conn, 'pdo');

// Write the SQL Query
$grid->SelectCommand = 'SELECT OrderID, OrderDate, CustomerID, Freight, ShipName FROM orders';
$grid->dataType = "json";

// set the user date format to m/d/Y
$grid->setUserDate('m/d/Y');
// tell the grid which field is date
$grid->datearray = array('OrderDate');
//Define the custom function
$grid->customFunc = "FrightUpdate";

$grid->queryGrid();

function FrightUpdate($gdata, $conn)
{
    // $data contain the following properties
    // $gdatata->records = total records;
    // $gdata->page = current page;
    // $gdata->total = total pages from the query
    // $gdata->rows contain the grid data rows for that page portion
    // $gdata->userdata - user specific data summary and etc. to be put usually on footer row
    foreach($gdata->rows as $row ) 
    {
    // format using "," as decimal separator
        if($row->Freight < 1 ) {
        //color in red if the value is less than 1
            $row->Freight = "<font color='red'>".number_format($row->Freight, 2, ',', ' ')."</font>";
        } else {
            $row->Freight = number_format($row->Freight, 2, ',', ' ');
        }
    }
    // return the modified
    return $gdata;
}