URL:http://www.trirand.com/blog/?page_id=4
1. Data returned from the server can be in XML or JSON
XML Syntax:
<?xml version ="1.0" encoding="utf-8"?>
<rows>
<page> </page>
<total> </total>
<records> </records> (new tag)
<row id = “unique_rowid”>
<cell> cellcontent </cell>
<cell> <![CDATA[<font color=”red”>cell</font> content]]> </cell>
…
</row>
…
</rows>
JSON Syntax:
{ total: xxx, page: yyy, records: zzz, rows: [
{id:”1″,cell:[”Row 1:1″,”Row 1:2″,”Row 1:3″,”Row 1:4″]},
{id:”2″,cell:[”Row 2:1″,”Row 2:2″,”Row 2:3″,”Row 2:4″]},
{id:”3″,cell:[”Row 3:1″,”Row 3:2″,”Row 3:3″,”Row 3:4″]},
…
]}
In page tag server must return the number of the requested page.
In total tag server must return the total pages of the query.
In records tag server can return the total records from the query.
In cell tag is the actual data. Note that CDATA can be used. This way we can add images, links and check boxes.
Actually the cell data in the grid is inserted as html and not as text.
In PHP script use the following code for page header (when using xml):
<?php
if ( stristr($_SERVER["HTTP_ACCEPT"],"application/xhtml+xml") ) {
header("Content-type: application/xhtml+xml"); } else {
header("Content-type: text/xml");
}
echo("<?xml version=/"1.0/" encoding=/"utf-8/"?>/n");
?>
2. The selector in the html document must be a table element of class “scroll”. Example:
<table id= "table_id" class="scroll" cellpadding="0" cellspacing="0">
</table>
3. Parameters passed to the server url (you must use a get method to obtain the values) are:
page - the requested page;
rows - the number of the requested rows that server must return;
sidx - the name (or number) of the column in the ORDER BY clause;
sord - way in which the column must be sorted - asc (ascending) or desc (descending);
4. Parameters used when constucting the grid. Example:
…
var mygrid = $(”#table_id”).jqGrid( {
url: “myurl.php”,
height: 150,
page: 1,
colNames: [”colname1″,”colname2″],
colModel: [
{index : ‘col1′, width : 80, sortable : true, align: ‘left’},
{index : ‘col’, width : 80, sortable : true, align: ‘left’}
],
rowNum: 20,
rowList: [20,30,40,50],
pager: $(”#page_selector_id”),
sortorder: “asc”,
sortname: “”,
altRows: true,
sortascimg : “sort_asc.gif”,
sortdescimg : “sort_desc.gif”,
firstimg: “first.gif”,
previmg: “prev.gif”,
nextimg: “next.gif”,
lastimg: “last.gif”,
onSelectRow: function (iRow) { …},
onSortCol: function (idx,iCol){ …},
ondblClickRow: function (iRow) { … }
});
…
Description of the parameters:
url: (mandatory) - the url where the XML file resides or is constructed with SQL.
height: (optional) - the heght of the grid in pixels. Default 150.
page: (optional) - the requested page number. Default 1.
colNames: (mandatory) - array which describes the column names in the grid
colModel: (mandatory) - array which describes the parameters of the columns. The parameters are:
- name: (optional) - the index name which is used when sorting the data. Alias name can be used - i.e mytable.mycol. If this parameter is not set a unique number is assign in the order in which apper in the array - i.e 1,2… .
- width: (mandatory) - the initial width of the column in pixels. Note that the width of the grid is sum of all columns width + 20 pixels for the scrolling.
- sortable: (optional) describes if the column can be sorted. Valid values are true or false. Default is true.
- align: (optional) - This attribute specifies the horizontal alignment of the element. Posible values are: left, center, right. Default is left.
- sorttype: (optional) - This attribute describes the type of the field. This is only used if datatype option is set to “local”, or when load at once method is used. The possible values are: int, float, date, text.
rowNum: (optional) the number of rows that must be returned from the server (query). Default is 20.
rowList: (optional) if set this parameter construct a select box element in the pager in wich the user can change the number of the visible rows. Default is empty array.
pager: (optional) this parameter describes the pager. This must be a valid html element. Example: $(”#mypager_id”). If the element is of class “scroll”, then the width is equal to the grid.
sortorder: (optional) sets the sorting order. Default asc (ascending)
sortname: (optional) sets the initial sorting column. Can be a name or number. Default is empty string.
altRows: (optional) set a zebra like table. Default true
sortascimg : and sortdescimg : (optional) these parameters are liks to image url which are used when the user sort a column.
firstimg: , previmg: , nextimg: , lastimg: (optional) these parameters are liks to image url which are used in the pager.
onSelectRow: (optional) raised immideatly after row was clicked. The input parameter of the function is the row_id. Default null.
onSortCol: (optional) raised immideatly after sortable column was clicked and before sorting the data. The input parameters of the function are the index name and column index. Default null.
ondblClickRow: (optional) raised immideatly after row was double clicked. The input parameter of the function is the row_id. Default null.
datatype: (optional). Default “xml”. Possible values “xml” or “json” or “local”. Set this option according to data type returned from the server. If set to “local” the grid expected to load data local via array.
width: (optional) Default: none. If this option is not set, the width of the grid is a sum of the width’s of the columns defined in the column model + 20 pixels. If this option is set the width of each column is scaled according to the defined width. Example: if we define two columns with a width of 80 and 120 pixels, but want the grid to have a 300 pixels - then the columns are recalculated as follow: 1- column = 300(new width)/200(sum of all width)*80(column width) = 120 and 2 column = 300/200*120 = 180.
rowheight: (optional) Default: null. This option defines the height of the single row ( must make some experiments here). If is set the height of the grid is changed according to the number of the returned rows from the server and the scroll bar does not appear. Important note here - if you want this to work you should not change the property overflow hidden and white-space nowrap in the CSS
viewrecords: (optional) Default: false. If you want to view the total records from the query in the pager bar you should set option to true. The related tag is: records in xml or json definitions.
recordtext: (optional) This displays a text after the viewed records. The default value is in English and is: Record(s).
imgpath: (optional) Default: empty string. Whit this option you can define the path to the images that are used from the grid. Define this without a “/” at the end.
loadtext: (optional) Default: “Loading…”. With this we can change the text which apper when requesting and sorting data.
loadonce: (optional) Default: false. If this flag is set to true, the grid load only once the data from the server. All other manipulations are done at client side. Note that in this case the pager is disabled.
multiselect: (optional) Default: false. If this flag is set to true a multi selection of rows is enabled. A new column at left side is added. This can be used with server and local data.
subGrid: (optional) Default: false. Is set to true this enables using of sub grid.
subGridUrl: (optional) Default: empty string. This option has effect only if subGrid option is set to true. This describes the url for the subgrid data. Additinally to this url is added parameter “id” which is the id of the row. If using PHP we can use $id = $_GET[’id’]; to obtain the needed information of the sub grid data.
subGridModel:(optional) Default: empty array. This option has effect only if subGrid option is set to true. This option describes the model of the subgrid. The syntax is as follow: subGridModel:[{ name : [’n1′,’n2′,..,’nN’],width : [w1,w2,…,wN] }] - where n1..nN are the names which appears at the header of the sub grid and w1..wN are the widths of the columns. See example page for using a sub grid.
5. Methods
After constructing the grid we can use the following methods.
getUrl - return the current url
getSortName - return the current sortname
getSortOrder - return the current sorting order
getSelectedRow - return the current selected row
getPage - return the curent page number
getRowNum - return the current number of requested rows
setUrl(’newurl’) - set a new url
setSortName(’newsortname’) set a new sort name
setSortOrder(’newsortorder’) set a new sort order
setPage(newpage) set a new page number
setRowNum (newrownum) set a new number of requested rows.
.trigger( ‘reloadGrid’) method reloads a data with the current settings.
New Methods
getMultiRow return a one dimensional array with the selected rows id’s. Can be used if the flag multiselect is set to true.
getDataType return the current data type. Possible values are : xml, json, local.
getRecords return the number of records in grid.
setDataType(newdatatype) Set a new data type. Possible values are : xml, json, local.
getRowData(rowid) Return associative array ([name:value,..])with the values of the requested rowid. If the row can not be found the method returns empty array.
delRowData(rowid) Deletes row with the id - rowid. Return true is the operation success, otherwise false.
setRowData(rowid, data) Updates the values (using data array) in the row with the given rowid. The syntax of data array is: {name1:value1,name2: value2…} where the name is the name of the column as described in the colModel and the value is the new value. Return true on success.
addRowData(rowid,data) Insert new row with id rowid at the end of the grid using data array.The syntax of data array is: {name1:value1,name2: value2…} where the name is the name of the column as described in the colModel and the value is the value. Return true on success.
toXmlData(encoding) Return a XML string with a current data in the grid. The default encoding is utf-8.
toJSONData() Return a string in JSON format with the current data in the grid.
If you want to set new parameters a reloadGrid method load a data with a new setting.
Example:
mygrid.setPage(5);
mygrid.trigger(’reloadGrid’);
where mygrid = $(”table_id”).jqGrid(…);
loads a Page 5 from the server
A simple PHP MySQL generation of the XML File can be found here