Class: jqGrid

Source Location: /jqGrid.php

Class jqGrid

Class Overview

Located in /jqGrid.php [line 53]

Author(s):

Tony Tomov, (tony@trirand.com)

API Tags:

Abstract:

A PHP class to work with jqGrid jQuery plugin. The main purpose of this class is to provide the data from database to jqGrid, simple subgrid and export to excel. Also can be used for search provided from toolbarFiletr and searchGrid methods

How to use

Note that the jqGrid object should be created in the html/php page with jsonReader set as jsonReader:{repeatitems:false}. or xmlReader set as xmlReader:{repeatitems:false}. Optional in the reader a id row can be set, but we prefer to set it in colModel using the parameter key:true

Using table

require_once 'jqGridPdo.php'; $dsn = "mysql:host=localhost;dbname=griddemo"; $db = new PDO($dsn, 'username', 'password');

$mygrid = new jqGrid($db); $mygrid->table = "invoices"; $mygrid->queryGrid();

Using custom SQL

$mygrid = new jqGrid($db); $mygrid->SelectCommand ="SELECT * FROM invoices"; $mygrid->queryGrid();

Using xml file where the sql commands are stored

$mygrid = new jqGrid($db); $mygrid->readFromXML = true $mygrid->SelectCommand ="xmlfile.getInvoiceTable"; $mygrid->queryGrid();

Using summary fields. Note that in this case in jqGrid footerrow and userDataOnFooter should be set

$mygrid = new jqGrid($db); $mygrid->table = "invoices"; $mygrid->queryGrid(array("amount"=>"amount");

Information Tags:

Version:	3.8.0.1
Copyright:	TriRand Ltd

Properties

Methods

[ Top ]

Descendants

Child Class	Description
jqGridEdit

[ Top ]

Property Summary

static mixed	$queryLog
boolean	$cacheCount
mixed	$customClass	Custom call can be used again with custom function customFunc. We can call
function	$customFunc	Custom function which can be called to modify the grid output. Parameters
string	$dataType	The output format for the grid. Can be json or xml
array	$datearray	Store the names which are dates. The name should correspond of the name
string	$dbdateformat	Date format accepted in the database. See getDbDate
string	$dbtimeformat	Datetime format accepted in the database. See getDbTime
string	$dbtype	This is detected automatically from the passed connection. Used to
boolean	$debug	Enable disable debuging
string	$encoding	Default enconding passed to the browser
string	$ExportCommand	Set the sql command for excel export. If not set a _setSQL
array	$GridParams	Holds the parameters that are send from the grid to the connector.
integer	$gSQLMaxRows	Maximum number of rows to be exported for the excel export
string	$I	Used to perform case insensitive search in PostgreSQL. The variable is
boolean	$jsonencode	If set to true uses the PHP json_encode if available. If this is set to
boolean	$logtofile	Determines if the log should be written to file or echoed.
boolean	$optimizeSearch	Optimizes the search SQL when used in MySQL with big data sets.
resourse	$pdo	Stores the connection passed to the constructor
boolean	$readFromXML	Obtain the SQL qurery from XML file.
string	$select	Holds the modified select command used into grid
string	$SelectCommand	In case if no table is set, this holds the sql command for
string	$SubgridCommand	Set a sql command used for the simple subgrid
string	$table	set a table to display a data to the grid
	$userdata	Used to store the additional userdata which will be transported
string	$userdateformat	The date format used by the user when a search is performed and CRUD operation
string	$usertimeformat	The datetime format used by the user when a search is performed and CRUD operation
string	$version	Get te current version
boolean	$xmlCDATA	Defines if the xml otput should be enclosed in CDATA when xml output is enabled

[ Top ]

Method Summary

jqGrid	__construct()	Constructor
void	addUserData()	Add a custom data to the grid footer row if it is enabled.
void	debugout()	Prints all executed SQL queries to file or console
boolean	execute()	Executes a prepared sql statement. Also if limit is set to true is used
string	exportToExcel()	Export the recordset to excel xml file.
string	getDbDate()	Return the current date format used in the undelayed database
string	getDbTime()	Return the current datetime format used in the undelayed database
array	getGridParams()	Return the associative array which contain the parameters that are sended from the grid to request, search, update delete data.
mixed	getSqlElement()	Read a xml file and the SelectCommand and return the sql string Return string if the query is found false if not.
string	getUserDate()	Return the current date format used from the client
string	getUserTime()	Return the current datetime format used from the client
void	logQuery()	Log query
string	parseSql()	Prepares a $sqlElement and binds a parameters $params
object	queryForObject()	Return the object from the query
mixed	queryGrid()	Return the result of the query to jqGrid. Support searching
void	querySubGrid()	Return the result of the query for the simple subgrid
string	rs2excel()	From a given recordset returns excel xml file. If the summary array is defined add summary formula at last row.
object	selectLimit()	Will select, getting rows from $offset (1-based), for $nrows.
void	setDbDate()	Set a new database date format using PHP convensions
void	setDbTime()	Set a new database datetime format using PHP convensions
void	setGridParams()	Set a grid parameters to identify the action from the grid Note that these should be set in the grid - i.e the parameters from the grid should equal to the GridParams.
void	setUserDate()	Set a new user date format using PHP convensions
void	setUserTime()	Set a new user datetime format using PHP convensions
array	_buildSearch()	Builds the search where clause when the user perform a search Return arrray the first element is a strinng with the where clause, the second element is array containing the value parameters passed to the sql.
object	_getcount()	Returns object which holds the total records in the query and optionally
void	_gridResponse()	Check in which format data should be returned to the grid based on dataType property
mixed	_setSQL()	Bulid the sql based on $readFromXML, $SelectCommand and $table variables

[ Top ]

Properties

static mixed $queryLog = array() [line 119]

API Tags:

Access:

protected

[ Top ]

boolean $cacheCount = false [line 315]

API Tags:

Access:

public

[ Top ]

mixed $customClass = false [line 299]

Custom call 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

API Tags:

Access:

public

[ Top ]

function $customFunc = null [line 291]

Custom function which can be called to modify the grid output. Parameters

passed to this function are the response object and the db connection

API Tags:

Access:

public

[ Top ]

string $dataType = "xml" [line 206]

The output format for the grid. Can be json or xml

API Tags:

Access:

public

[ Top ]

array $datearray = array() [line 225]

Store the names which are dates. The name should correspond of the name

in colModel. Used to perform the conversion from userdate and dbdate

API Tags:

Access:

public

[ Top ]

string $dbdateformat = 'Y-m-d' [line 92]

Date format accepted in the database. See getDbDate

and setDbDate and datearray. Also this format is used to automatically convert the date for CRUD and search operations

API Tags:

Access:

protected

[ Top ]

string $dbtimeformat = 'Y-m-d H:i:s' [line 100]

Datetime format accepted in the database. See getDbTime

and setDbTime and datearray. Also this format is used to automatically convert the date for CRUD and search operations

API Tags:

Access:

protected

[ Top ]

string $dbtype [line 78]

This is detected automatically from the passed connection. Used to

construct the appropriate pagging for the database and in case of PostgreSQL to set case insensitive search

API Tags:

Access:

protected

[ Top ]

boolean $debug = false [line 143]

Enable disable debuging

API Tags:

Access:

public

[ Top ]

string $encoding = "utf-8" [line 211]

Default enconding passed to the browser

API Tags:

Access:

public

[ Top ]

string $ExportCommand = "" [line 239]

Set the sql command for excel export. If not set a _setSQL

function is used to set a sql for export

API Tags:

See:	jqGrid::_setSQL()
Access:	public

[ Top ]

array $GridParams = array(
"page" => "page",
"rows" => "rows",
"sort" => "sidx",
"order" => "sord",
"search" => "_search",
"nd" => "nd",
"id" => "id",
"filter" => "filters",
"searchField" => "searchField",
"searchOper" => "searchOper",
"searchString" => "searchString",
"oper" => "oper",
"query" => "grid",
"addoper" => "add",
"editoper" => "edit",
"deloper" => "del",
"excel" => "excel",
"subgrid"=>"subgrid",
"totalrows" => "totalrows",
"autocomplete"=>"autocmpl"
) [line 180]

Holds the parameters that are send from the grid to the connector.

Correspond to the prmNames in jqGrid Java Script lib

API Tags:

Access:

protected

Information Tags:

Todo:

these parameters should be changed according to the jqGrid js

[ Top ]

integer $gSQLMaxRows = 1000 [line 244]

Maximum number of rows to be exported for the excel export

API Tags:

Access:

public

[ Top ]

string $I = '' [line 71]

Used to perform case insensitive search in PostgreSQL. The variable is

detected automatically depending on the griver from jqGrid{driver}.php

API Tags:

Access:

protected

[ Top ]

boolean $jsonencode = true [line 219]

If set to true uses the PHP json_encode if available. If this is set to

false a custom encode function in jqGrid is used. Also use this to false if the encoding of your database is not utf-8

API Tags:

Deprecated:	this not needed anymore also the related option is $encoding
Access:	public

[ Top ]

boolean $logtofile = true [line 149]

Determines if the log should be written to file or echoed.

Ih set to created is a file jqGrid.log in the directory where the script is

API Tags:

Access:

public

[ Top ]

boolean $optimizeSearch = false [line 310]

Optimizes the search SQL when used in MySQL with big data sets.

Use this option carefully on complex SQL

API Tags:

Access:

public

[ Top ]

resourse $pdo [line 65]

Stores the connection passed to the constructor

API Tags:

Access:

protected

[ Top ]

boolean $readFromXML = false [line 279]

Obtain the SQL qurery from XML file.

In this case the 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. The simple xml file can look like this < ?xml version="1.0" encoding="UTF-8"?> <queries> <sql Id="getUserById"> Select * From users Where Id = ? </sql> <sql Id="validateUser"> Select Count(Id) From users Where Email = ? AND Password = ? </sql> </queries> Important note: The class first look for readFromXML, then for selectCommand and last for a table.

API Tags:

Access:

public

[ Top ]

string $select = "" [line 84]

Holds the modified select command used into grid

API Tags:

Access:

protected

[ Top ]

string $SelectCommand = "" [line 231]

In case if no table is set, this holds the sql command for

retrieving the data from the db to the grid

API Tags:

Access:

public

[ Top ]

string $SubgridCommand = "" [line 249]

Set a sql command used for the simple subgrid

API Tags:

Access:

public

[ Top ]

string $table = "" [line 254]

set a table to display a data to the grid

API Tags:

Access:

public

[ Top ]

$userdata = null [line 285]

Used to store the additional userdata which will be transported

to the grid when the request is made. Used in addRowData method

API Tags:

Access:

protected

[ Top ]

string $userdateformat = 'd/m/Y' [line 107]

The date format used by the user when a search is performed and CRUD operation

See setUserDate and getUserDate. Also this format is used to automatically convert the date passed from grid to database. Used in CRUD operations and search

API Tags:

Access:

protected

[ Top ]

string $usertimeformat = 'd/m/Y H:i:s' [line 115]

The datetime format used by the user when a search is performed and CRUD operation

See setUserTime and getUserTime. Also this format is used to automatically convert the datetime passed from grid to database. Used in CRUD operations and search

API Tags:

Access:

protected

[ Top ]

string $version = '3.8.0.1' [line 59]

Get te current version

API Tags:

Access:

public

[ Top ]

boolean $xmlCDATA = false [line 304]

Defines if the xml otput should be enclosed in CDATA when xml output is enabled

API Tags:

Access:

public

[ Top ]

Methods

Constructor __construct [line 321]

jqGrid __construct(
[resource
$db = null] 
)

Constructor

Parameters:

resource

$db:

- $db the database connection passed to the constructor

[ Top ]

addUserData [line 1140]

void addUserData(
array
$adata 
)

Add a custom data to the grid footer row if it is enabled.

Also can be used to transport additional data in userdata array to be used later at client side. The syntax is $grid->addUserData(array("Name1"=>"Data1",...)); The method is executed after the sumarry rows are executed, so it can overwrite some summary data which is palced on the footer.

Parameters:

array

$adata:

API Tags:

Access:

public

[ Top ]

debugout [line 154]

void debugout(
)

Prints all executed SQL queries to file or console

API Tags:

See:	jqGrid::$logtofile
Access:	public

[ Top ]

execute [line 362]

boolean execute(
string
$sqlId, array
$params, 
&$sql, [boolean
$limit = false], [integer
$nrows = -1], [integer
$offset = -1], [
$order = ''], [
$sort = ''], resource
$sql 
)

Executes a prepared sql statement. Also if limit is set to true is used

to return limited set of records Return true on success

Parameters:

string	$sqlId:	- sql to pe executed
array	$params:	- array of values which are passed as parameters to the sql
resource	$sql:	- pointer to the constructed sql
boolean	$limit:	- if set to true we use a pagging mechanizm
integer	$nrows:	- number of rows to return
integer	$offset:	- the offset from which the nrows should be returned
	&$sql:
	$order:
	$sort:

API Tags:

Access:

protected

[ Top ]

exportToExcel [line 839]

string exportToExcel(
[
$summary = null], [
$params = null], [
$colmodel = null], [boolean
$echo = true], [string
$filename = 'exportdata.xml'] 
)

Export the recordset to excel xml file.

Can use the ExportCommand. If this command is not set uses _setSQL to set the query. The number of rows exported is limited from gSQLMaxRows variable

Parameters:

array	$summary:	- set which columns should be sumarized in order to be displayed to the grid By default this parameter uses SQL SUM function: array("colmodelname"=>"sqlname"); It can be set to use the other one this way array("colmodelname"=>array("sqlname"=>"AVG")); By default the first field correspond to the name of colModel the second to the database name
array	$params:	parameter values passed to the sql array(value)
array	$colmodel:	- different description for the headers - see rs2excel
boolean	$echo:	determines if the result should be returned or echoed
string	$filename:	the filename to which the sheet can be saved in case if $echo is true

API Tags:

See:	jqGrid::_setSQL()
Access:	public

[ Top ]

getDbDate [line 596]

string getDbDate(
)

Return the current date format used in the undelayed database

API Tags:

Access:

public

[ Top ]

getDbTime [line 612]

string getDbTime(
)

Return the current datetime format used in the undelayed database

API Tags:

Access:

public

[ Top ]

getGridParams [line 630]

array getGridParams(
)

Return the associative array which contain the parameters that are sended from the grid to request, search, update delete data.

API Tags:

Access:

public

[ Top ]

getSqlElement [line 382]

mixed getSqlElement(
string
$sqlId 
)

Read a xml file and the SelectCommand and return the sql string Return string if the query is found false if not.

Parameters:

string

$sqlId:

the string of type xmlfile.sqlId

API Tags:

Access:

protected

[ Top ]

getUserDate [line 564]

string getUserDate(
)

Return the current date format used from the client

API Tags:

Access:

public

[ Top ]

getUserTime [line 580]

string getUserTime(
)

Return the current datetime format used from the client

API Tags:

Access:

public

[ Top ]

logQuery [line 127]

void logQuery(
string
$sql, [array
$data = null], [array
$types = null], [
$input = null], [
$fld = null], [
$primary = ''] 
)

Log query

Parameters:

string	$sql:
array	$data:
array	$types:
	$input:
	$fld:
	$primary:

API Tags:

Access:

public

[ Top ]

parseSql [line 344]

string parseSql(
string
$sqlElement, array
$params, [
$bind = true] 
)

Prepares a $sqlElement and binds a parameters $params

Return prepared sql statement

Parameters:

string	$sqlElement:	sql to be prepared
array	$params:	- parameters passed to the sql
	$bind:

API Tags:

Access:

protected

[ Top ]

queryForObject [line 450]

object queryForObject(
string
$sqlId, array
$params, [boolean
$fetchAll = false] 
)

Return the object from the query

Parameters:

string	$sqlId:	the sql to be queried
array	$params:	- parameter values passed to the sql
boolean	$fetchAll:	- if set to true fetch all records

API Tags:

Access:

protected

[ Top ]

queryGrid [line 687]

mixed queryGrid(
[
$summary = null], [
$params = null], [boolen
$echo = true] 
)

Return the result of the query to jqGrid. Support searching

Parameters:

array	$summary:	- set which columns should be sumarized in order to be displayed to the grid By default this parameter uses SQL SUM function: array("colmodelname"=>"sqlname"); It can be set to use the other one this way array("colmodelname"=>array("sqlname"=>"AVG")); By default the first field correspond to the name of colModel the second to the database name
array	$params:	- parameter values passed to the sql
boolen	$echo:	if set to false return the records as object, otherwiese json encoded or xml string depending on the dataType variable

API Tags:

Access:

public

[ Top ]

querySubGrid [line 878]

void querySubGrid(
array
$params, [boolean
$echo = true] 
)

Return the result of the query for the simple subgrid

The format depend of dataType variable

Parameters:

array	$params:	parameters passed to the query
boolean	$echo:	if set to false return object containing the data

API Tags:

Access:

public

[ Top ]

rs2excel [line 950]

string rs2excel(
pdo
$rs, [array
$colmodel = false], [boolean
$echo = true], [string
$filename = 'exportdata.xls'], [array
$summary = false] 
)

From a given recordset returns excel xml file. If the summary array is defined add summary formula at last row.

Return well formated xml excel string

Parameters:

array	$colmodel:	diffrent descriptions for the headars, width, hidden cols This array is actually a colModel array in jqGrid. The array can look like Array( [0]=>Array("label"=>"Some label", "width"=>100, "hidden"=>true, "name"=>"client_id", "formatter"=>"select", editoptions=>...), [1]=>Array("label"=>"Other label", "width"=>80, "hidden"=>false, "name"=>"date",... ), ... )
boolean	$echo:	determines if the result should be send to browser or returned as string
string	$filename:	filename to which file can be saved
array	$summary:	- set which columns should be sumarized in order to be displayed to the grid By default this parameter uses SQL SUM function: array("colmodelname"=>"sqlname"); It can be set to use the other one this way array("colmodelname"=>array("sqlname"=>"AVG")); By default the first field correspond to the name of colModel the second to the database name
pdo	$rs:	recordset $rs recordset from pdo execute command

API Tags:

Access:

protected

[ Top ]

selectLimit [line 661]

object selectLimit(
[string
$limsql = ''], [integer
$nrows = -1], [integer
$offset = -1], [
$params = null], [
$order = ''], [
$sort = ''] 
)

Will select, getting rows from $offset (1-based), for $nrows.

This simulates the MySQL "select * from table limit $offset,$nrows" , and the PostgreSQL "select * from table limit $nrows offset $offset". Note that MySQL and PostgreSQL parameter ordering is the opposite of the other. eg. Also supports Microsoft SQL Server SelectLimit('select * from table',3); will return rows 1 to 3 (1-based) SelectLimit('select * from table',3,2); will return rows 3 to 5 (1-based) Return object containing the limited record set

Parameters:

string	$limsql:	- optional sql clause
integer	$nrows:	is the number of rows to get
integer	$offset:	is the row to start calculations from (1-based)
array	$params:	array of bind variables
	$order:
	$sort:

API Tags:

Access:

public

[ Top ]

setDbDate [line 604]

void setDbDate(
string
$newformat 
)

Set a new database date format using PHP convensions

Parameters:

string

$newformat:

- the new database format

API Tags:

Access:

public

[ Top ]

setDbTime [line 620]

void setDbTime(
string
$newformat 
)

Set a new database datetime format using PHP convensions

Parameters:

string

$newformat:

- the new database format

API Tags:

Access:

public

[ Top ]

setGridParams [line 640]

void setGridParams(
array
$_aparams 
)

Set a grid parameters to identify the action from the grid Note that these should be set in the grid - i.e the parameters from the grid should equal to the GridParams.

Parameters:

array

$_aparams:

set new parameter.

API Tags:

Access:

public

[ Top ]

setUserDate [line 572]

void setUserDate(
string
$newformat 
)

Set a new user date format using PHP convensions

Parameters:

string

$newformat:

- the new format

API Tags:

Access:

public

[ Top ]

setUserTime [line 588]

void setUserTime(
string
$newformat 
)

Set a new user datetime format using PHP convensions

Parameters:

string

$newformat:

- the new format

API Tags:

Access:

public

[ Top ]

_buildSearch [line 470]

array _buildSearch(
string
$sqlEx, [
$prm = null] 
)

Builds the search where clause when the user perform a search Return arrray the first element is a strinng with the where clause, the second element is array containing the value parameters passed to the sql.

Parameters:

string	$sqlEx:	- the sql on which a serch should be applied
array	$prm:	- parameters passed to the sql

API Tags:

Access:

protected

[ Top ]

_getcount [line 407]

object _getcount(
string
$sql, [
$params = null], [
$sumcols = null] 
)

Returns object which holds the total records in the query and optionally

the sum of the records determined in sumcols

Parameters:

string	$sql:	- string to be parsed
array	$params:	- parameters passed to the sql query
array	$sumcols:	- array which holds the sum of the setted rows. The array should be associative where the index corresponds to the names of colModel in the grid, and the value correspond to the actual name in the query

API Tags:

Access:

protected

[ Top ]

_gridResponse [line 894]

void _gridResponse(
string
$response 
)

Check in which format data should be returned to the grid based on dataType property

Add the appropriate headers and echo the result

Parameters:

string

$response:

can be xml or json

API Tags:

Access:

protected

[ Top ]

_setSQL [line 548]

mixed _setSQL(
)

Bulid the sql based on $readFromXML, $SelectCommand and $table variables

The logic is: first we look if readFromXML is set to true, then we look for SelectCommand and at end if none of these we use the table varable Return string or false if the sql found

API Tags:

Access:

protected

[ Top ]

Documentation generated on Thu, 16 Sep 2010 11:18:55 +0300 by phpDocumentor 1.4.3

	jqGrid

[ class tree: jqGrid ] [ index: jqGrid ] [ all elements ]