My First Grid

For this tutorial, and as an example to refer to throughout this documentation, we’re going to create a grid with invoice information.

You need the following three things in order to use jqGrid:

  1. A database with some sample data,
  2. A HTML page to show the data, and
  3. A server-side component to act as an interface between your web page and the database.

Our example will present the following information:

  • Invid – the invoice number,
  • Invdate – the date of the invoice,
  • Amount,
  • Tax,
  • Total (including tax), and
  • Note – additional text information about the invoice.

Data

We'll need a table with the following format. This example is based on MySQL; please create yours however you would normally do it.

CREATE TABLE invheader (                                                     
  invid int(11) NOT NULL AUTO_INCREMENT,                                             
  invdate date NOT NULL,                                                          
  client_id int(11) NOT NULL,                                                     
  amount decimal(10,2) NOT NULL DEFAULT '0.00',                                   
  tax decimal(10,2) NOT NULL DEFAULT '0.00',                                      
  total decimal(10,2) NOT NULL DEFAULT '0.00',                                    
  note char(100) DEFAULT NULL,                                 
  PRIMARY KEY  (invid) 
);

If you're using a MySQL administration program, many times there is an area to type in and execute SQL commands. If that's the case, simply copy and paste the code above, then hit the execute button to create the database. Alternatively, you can manually create the database and set up the columns one at a time.

Then, put some values into it.

(Here's a spreadsheet with 644 sample records. Feel free to use this or create your own data. Note that column “A” is blank because it should be an auto-increment field in your database) dataupload.xls

HTML File

The HTML page fulfills three purposes: It loads all the files required for jqGrid to work, it contains the grid placement, and it contains the grid configuration instructions.

Using the file myfirstgrid.html as described in the installation section, we have the following code:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<title>My First Grid</title>
 
<link rel="stylesheet" type="text/css" media="screen" href="css/ui-lightness/jquery-ui-1.8.2.custom.css" />
<link rel="stylesheet" type="text/css" media="screen" href="css/ui.jqgrid.css" />
 
<style type="text/css">
html, body {
    margin: 0;
    padding: 0;
    font-size: 75%;
}
</style>
 
<script src="js/jquery-1.7.2.min.js" type="text/javascript"></script>
<script src="js/i18n/grid.locale-en.js" type="text/javascript"></script>
<script src="js/jquery.jqGrid.min.js" type="text/javascript"></script>
 
<script type="text/javascript">
$(function () {
    $("#list").jqGrid({
        url: "example.php",
        datatype: "xml",
        mtype: "GET",
        colNames: ["Inv No", "Date", "Amount", "Tax", "Total", "Notes"],
        colModel: [
            { name: "invid", width: 55 },
            { name: "invdate", width: 90 },
            { name: "amount", width: 80, align: "right" },
            { name: "tax", width: 80, align: "right" },
            { name: "total", width: 80, align: "right" },
            { name: "note", width: 150, sortable: false }
        ],
        pager: "#pager",
        rowNum: 10,
        rowList: [10, 20, 30],
        sortname: "invid",
        sortorder: "desc",
        viewrecords: true,
        gridview: true,
        autoencode: true,
        caption: "My first grid"
    }); 
}); 
</script>
 
</head>
<body>
    <table id="list"><tr><td></td></tr></table> 
    <div id="pager"></div> 
</body>
</html>

Explanation of Code

We use the jQuery $ function to run our script at the appropriate time. For more information on this, refer to the jQuery documentation.

Required Files: An explanation of the .js and .css files required for jqGrid is provided in the Installation section.

Grid Placement: The grid can be placed anywhere between the <body> tags in the document. The definition of the grid is done via the HTML tag <table>, as shown here:

<body><table id="list"><tr><td></td></tr></table> 
<div id="pager"></div></body>

The table should have an ID that is unique in the HTML document. In the example above, it is ”#list”. This ID is important because you'll need it for grid functions. The elements <tr><td></td></tr> inside of the <table> element are needed only to make the document the valid XHTML 1.0 Strict document. The elements will be removed by jqGrid during the jqGrid initialization

In many examples throughout this documentation, you'll see a hash (#) sign before ID names. jqGrid works with or without the hash sign, but it's considered good practice to use the hash.

Cellspacing, cellpadding and border attributes are added by jqGrid and should not be included in the definition of your table.

For many grids, you'll want to have the ability to navigate and page up and down through your data. Within jqGrid, this is known as the Navigation layer. This feature is enabled by adding the commonly-used <div> tag<del>, identified by the ”#pager” ID in the example above. It's also important to use a unique ID for the navigation layer as well.

The Navigation layer can also be placed anywhere within the HTML document. Normally, and in this case, it is under the <table> tags.

The settings and options used in the code are described here. A full list of all possible settings and options can be found in API Methods and colModel API.

Property Description
url Tells us where to get the data. Typically this is a server-side function with a connection to a database which returns the appropriate information to be filled into the Body layer in the grid
datatype This tells jqGrid the type of information being returned so it can construct the grid. In this case, we tell the grid that we expect XML data to be returned from the server, but other formats are possible. For a list of all available datatypes refer to API Methods
mtype Tells us how to make the Ajax call: either 'GET' or 'POST'. In this case, we will use the GET method to retrieve data from the server
colNames An array in which we place the names of the columns. This is the text that appears in the head of the grid (Header layer). The names are separated with commas
colModel An array that describes the model of the columns. This is the most important part of the grid. Here I explain only the options used above. For the complete list of options see colModel API
name: The name of the column. This name does not have to be the name from the database table, but later we will see how we can use this when we have different data formats.
index: The name passed to the server on which to sort the data (note that we could pass column numbers instead). Typically this is the name (or names) from the database – this is server-side sorting, so what you pass depends on what your server expects to receive.
width: The width of the column, in pixels.
align: The alignment of the column.
sortable: Specifies if the data in the grid can be sorted on this column; if false, clicking on the header has no effect.
pagerDefines that we want to use a pager bar to navigate through the records. This must be a valid HTML element; in our example we gave the div the id of “pager”, but any name is acceptable. Note that the Navigation layer (the “pager” div) can be positioned anywhere you want, determined by your HTML; in our example we specified that the pager will appear after the Body layer.
rowNumSets how many records we want to view in the grid. This parameter is passed to the URL for use by the server routine retrieving the data
rowListAn array to construct a select box element in the pager in which we can change the number of the visible rows. When changed during the execution, this parameter replaces the rowNum parameter that is passed to the url
sortnameSets the initial sorting column. Can be a name or number. This parameter is added to the URL for use by the server routine
viewrecordsDefines whether we want to display the number of total records from the query in the pager bar
captionSets the caption for the grid. If this parameter is not set the Caption layer will be not visible

Having done this, we have now done half the work. The next step is to construct the server-side manipulation – which is done in the file pointed to by the “url” parameter in the grid.

Behind the Scenes: Grid Data

When using jqGrid, it will run a file (identified by the URL setting explained above) that will request data from the server (unless you're using static data). The server will return the data to jqGrid in a format it understands.

JqGrid can construct a grid using data from a number of formats, but the default is XML data with the structure in the example below. Later in the documentation, we'll see how to use XML data in other structures and data in other formats.

This data interchange happens behind the scenes; and the user only sees the completed grid, and not the raw data itself.

Default XML Data Structure:

<?xml version ="1.0" encoding="utf-8"?>
<rows>
  <page> </page>
  <total> </total>
  <records> </records>
    <row id = 'unique_rowid'>
      <cell> cellcontent </cell>
      <cell> <![CDATA[<font color='red'>cell</font> content]]> </cell></row>
    <row id = 'unique_rowid'>
      <cell> cellcontent </cell>
      <cell> <![CDATA[<font color='red'>cell</font> content]]> </cell></row></rows>

The tags used in this example are explained in the following table.

Tag Description
rowsthe root tag for the grid
pagethe number of the requested page
totalthe total pages of the query
recordsthe total records from the query
rowa particular row in the grid
cellthe actual data. Note that CDATA can be used. This way we can add images, links and check boxes.

The number of cell tags in each row must equal the number of cells defined in the colModel. In our example, we defined six columns, so the number of cell tags in each row tag should be six.

Note the id attribute in the <row> tags. While this attribute can be omitted, it is a good practice to have a unique id for every row. If this attribute is omitted, jqGrid has two ways of dealing with need for unique ids: if the property key in the colModel is set to true for a particular column, then jqGrid will assign the value of this column to be the id of the row; otherwise, jqGrid sets the row id based on the order of the row.

If you are using a content-free primary key to identify your data rows, then do not include this value in the grid as a visible cell; instead, include it in the query and pass it as the row id attribute. It will always be available for jqGrid and even jQuery operations but not be visible on the page.

Now it's time to construct the server side file that will facilitate the requests for data from jqGrid.

PHP and MySQL example file

The PHP file below is called when jqGrid requests data from the server. The file queries the MySQL database and returns the data to jqGrid in the XML format explained above.

Note that this file is set up to only read data from the server. If you want to write data to the server, that requires a separate file that would be called by the “EditURL” property, as explained on the Options page.

<?php 
//include the information needed for the connection to MySQL data base server. 
// we store here username, database and password 
include("dbconfig.php");
 
// to the url parameter are added 4 parameters as described in colModel
// we should get these parameters to construct the needed query
// Since we specify in the options of the grid that we will use a GET method 
// we should use the appropriate command to obtain the parameters. 
// In our case this is $_GET. If we specify that we want to use post 
// we should use $_POST. Maybe the better way is to use $_REQUEST, which
// contain both the GET and POST variables. For more information refer to php documentation.
// Get the requested page. By default grid sets this to 1. 
$page = $_GET['page']; 
 
// get how many rows we want to have into the grid - rowNum parameter in the grid 
$limit = $_GET['rows']; 
 
// get index row - i.e. user click to sort. At first time sortname parameter -
// after that the index from colModel 
$sidx = $_GET['sidx']; 
 
// sorting order - at first time sortorder 
$sord = $_GET['sord']; 
 
// if we not pass at first time index use the first column for the index or what you want
if(!$sidx) $sidx =1; 
 
// connect to the MySQL database server 
$db = mysql_connect($dbhost, $dbuser, $dbpassword) or die("Connection Error: " . mysql_error()); 
 
// select the database 
mysql_select_db($database) or die("Error connecting to db."); 
 
// calculate the number of rows for the query. We need this for paging the result 
$result = mysql_query("SELECT COUNT(*) AS count FROM invheader"); 
$row = mysql_fetch_array($result,MYSQL_ASSOC); 
$count = $row['count']; 
 
// calculate the total pages for the query 
if( $count > 0 && $limit > 0) { 
              $total_pages = ceil($count/$limit); 
} else { 
              $total_pages = 0; 
} 
 
// if for some reasons the requested page is greater than the total 
// set the requested page to total page 
if ($page > $total_pages) $page=$total_pages;
 
// calculate the starting position of the rows 
$start = $limit*$page - $limit;
 
// if for some reasons start position is negative set it to 0 
// typical case is that the user type 0 for the requested page 
if($start <0) $start = 0; 
 
// the actual query for the grid data 
$SQL = "SELECT invid, invdate, amount, tax,total, note FROM invheader ORDER BY $sidx $sord LIMIT $start , $limit"; 
$result = mysql_query( $SQL ) or die("Couldn't execute query.".mysql_error()); 
 
// we should set the appropriate header information. Do not forget this.
header("Content-type: text/xml;charset=utf-8");
 
$s = "<?xml version='1.0' encoding='utf-8'?>";
$s .=  "<rows>";
$s .= "<page>".$page."</page>";
$s .= "<total>".$total_pages."</total>";
$s .= "<records>".$count."</records>";
 
// be sure to put text data in CDATA
while($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
    $s .= "<row id='". $row['invid']."'>";            
    $s .= "<cell>". $row['invid']."</cell>";
    $s .= "<cell>". $row['invdate']."</cell>";
    $s .= "<cell>". $row['amount']."</cell>";
    $s .= "<cell>". $row['tax']."</cell>";
    $s .= "<cell>". $row['total']."</cell>";
    $s .= "<cell><![CDATA[". $row['note']."]]></cell>";
    $s .= "</row>";
}
$s .= "</rows>"; 
 
echo $s;
?>

Save this file with name example.php and place it in myproject directory. You're now ready to create your first grid!

ATTENTION: PLEASE DO NOT POST TECHNICAL PROBLEMS IN THE DISCUSSION AREA BELOW. The discussion area should be used for discussing the documentation on this page only. For technical support, please visit the Help Forum

Discussion

Bill Hogsett, 2009/09/19 05:50

Help please!

I cannot get the sort and pager to work.

I have the sort working in my first grid, but not in my own grid.

Here is the page:

http://billhogsett.com/LEGHS_grid.html

The pager doesn't seem to work on either page.

Thanks

Bill

Bill Hogsett, 2009/09/19 14:53

I solved my problem. Beginner error having nothing to do with JGrid.

El fertass, 2009/10/05 20:19

Hello, First of all, thanks for this work, I executed all the steps as shown in the tutorial, but it not works on my WAMP (Apache 2.2.11, php 5.2.8/5.2.9/5.3.0 MySql 5.1.36)

but it works very well on Aptana Studio 1.5, so I think that I have problem in extensions and modules in Apache or php?

can you help me to the set my environment well?

/excuse my English.

Khau Tinh, 2009/10/22 01:00

I am not familiar with PHP. Please any one helps me to convert the PHP and MySQL example file to ASPX and SQL. I am appreciated the helps a lot.

happy developer.

Vladimir, 2009/11/20 12:46

Hello! Excuse me for my English, I use a translator Thank you for your work! I always get an error after installation Here's a screenshot I can not understand what “ui.multiselect”

Tony Tomov, 2009/11/22 23:52

Hello, The problem is fixed. Just wait for 3.6.1 release which will be published after some days. Tony

Vladimir, 2009/11/23 18:02

Thank you, waiting impatiently!:-)

wanggang, 2009/11/25 08:17

Helllo,if the data is ArrayData,it can not pager.

philip, 2009/12/21 21:04

I did the installation exactly as described using jqGrid version 3.6.2. I created the example files exactly as described. I am running under WAMP.

The result is that I can see the header for the table and the control buttons, but there is no data in the table. I placed sample data in the database table, but it is not displayed.

How can I fix this?

princess, 2010/01/07 06:10

Please help me… i have a problem in pagination in jqgrid..

the calculation of total rows of all page… my problem is the total rows count only per page????

Example: i have 13 rows of data

my rowlist is [10,20,30]

when i choose in dropdown list of rows 10

in page one i have 10 rows the total rows show in the right hand shows like this view 1-10 of 10

and when i next it to page 2

it shows….

view 11-13 of 3

it seems like it count the number of rows per page…

i want it to show the total numbers of rows or items in all pages..

example:

in the given data i show to you..

i like it to be shown..

in page 1 view 1-10 of 13

and in page 2 view 11-13 of 13

here’s my code..

please help me..thanks

-cess-^^

      $page = $this->input->post('page');
      $limit = $this->input->post('rows'); // get how many rows we want to have into the grid
      $sidx = $this->input->post('sidx'); // get index row - i.e. user click to sort
      $sord = $this->input->post('sord'); // get the direction
      
      $query = $this->input->post('query');
      $qtype = $this->input->post('qtype');
      
      if (!$sidx) $sidx = 1;
      if (!$sord) $sord = 'asc';
              
      if (!$page) $page = 1;
      if (!$limit) $limit = 25;
  
      $this->db->start_cache();
      
      if ($qtype == 'role_code' && $query) $this->db->like('role_code', $query);
      if ($qtype == 'role_code' && $query) $this->db->like('role_desc', $query);
      
      // calculate the number of rows for the query. We need this for paging the result
      $this->db->from('sec_role');
      $this->db->where('sec_role.is_deleted','0');
      $num = $this->db->count_all_results();
  
      // calculate the total pages for the query
      if( $num > 0 && $limit > 0) {
            $num = ceil($num/$limit);
      } else {
            $num = 0;
      }
      
      // if for some reasons the requested page is greater than the total
      // set the requested page to total page
      if ($page > $num) $page=$num;
      
      // calculate the starting position of the rows
      $start = $limit * $page - $limit; // do not put $limit*($page - 1)
      
      // if for some reasons start position is negative set it to 0
      // typical case is that the user type 0 for the requested page
      if($start <0) $start = 0;
      
      $this->db->select("role_id as pkey,role_id, role_code, role_desc");
      $this->db->order_by($sidx,$sord);
      $this->db->limit($limit, $start);
      $query = $this->db->get("sec_role");
      
      $this->db->flush_cache();
      
      $data['db'] = $query;
      $data['page'] = $page;
      $data['num'] = $num;
      return $data;
Paul Reichow, 2010/04/07 05:06

ATTENTION: PLEASE DON'T POST TECHNICAL PROBLEMS IN THIS DISCUSSION AREA. This discussion area should be used for improving the documentation on this page only.

For technical support, please visit theHelp Forum and you'll get a much faster response because that's the central area where all the questions are being asked. All the experts know to check that Forum for questions.

devin, 2010/09/29 23:30

here's an example of some JSON I've had success with:

 {
  "page": "1",
  "records": "10",
  "total": "2",
  "rows": [
      {
          "id": 3,
          "cell": [
              3,
              "cell 1",
              "2010-09-29T19:05:32",
              "2010-09-29T20:15:56",
              "hurrf",
              0 
          ] 
      },
      {
          "id": 1,
          "cell": [
              1,
              "teaasdfasdf",
              "2010-09-28T21:49:21",
              "2010-09-28T21:49:21",
              "aefasdfsadf",
              1 
          ] 
      } 
  ]
}

The general Perl data structure to generate such through JSON::XS looks like this:

  $VAR1 = {
        'page' => '1',
        'records' => '10',
        'total' => 2,
        'rows' => [
                    {
                      'id' => 3,
                      'cell' => [
                                  3,
                                  'b;lah',
                                  '2010-09-29T19:05:32',
                                  '2010-09-29T20:15:56',
                                  'asdlkfjl23;k4fjlq;3kf4jlqkj',
                                  0
                                ]
                    },
                    {
                      'id' => 1,
                      'cell' => [
                                  1,
                                  'teaasdfasdf',
                                  '2010-09-28T21:49:21',
                                  '2010-09-28T21:49:21',
                                  'aefasdfsadf',
                                  1
                                ]
                    }
                  ]
      };
xgretsch, 2011/01/17 17:23

Following these instructions (adapted for my own database) worked just fine. But don't try doing this on a live site without protecting it from SQL injection. Using parameters from a URL and copying them straight into a SQL query is an open invitation to a hacker to send you a URL like:

http://yourserver.com/example.php?sidx=name;grant all on thedatabase to 'hacker'@'101.102.103.104';

I'm not a good enough hacker to tell you if my example would actually work, but you get the idea. You protect against this by a PHP test along the lines of:

$sidx=$_GET['sidx'];
if(!in_array($sidx,array('invid','invdate','amount','tax','total','note')){
  echo 'Bad index: go away, you nasty hacker';
  exit;
}

…and similar for the other URL parameters.

theory, 2011/03/15 16:48

Hello. Please forgive the noob question but can these tables be generated from the XML document (without any server side code)? I am using static data for now. Thanks.

Nathan Feger, 2011/07/08 16:26

I would like to point out that: $SQL = “SELECT invid, invdate, amount, tax,total, note FROM invheader ORDER BY $sidx $sord LIMIT $start , $limit”

Is the canonnical example of how to facilitate sqlinjection attacks into your site. You should never blindly append any string given to you by a third party into your sql!!!

alexei, 2012/07/15 06:45

simple example with localdata

$(document).ready(function(){
 $('#grid_id').jqGrid({
      datatype:"clientSide",
      data:[  {'summary':'2','date':'10-jul'},
              {'summary':'1','date':'12-jul'}
              ],
      colModel:[
              {name:'summary'},
              {name:'date'}
              ]
  });
});
phonebook_, 2012/09/24 18:40

thx for this. here is an example of documentation done right. http://api.highcharts.com/highcharts

Sunay Vatansever, 2012/08/14 17:43

Where is dbconfig.php?

Sreevathsan Ravichandran, 2013/02/15 15:33

Hello,

I am new to JqGrid and Thanks for this wonderful thing made openSource. My backend datbase is IBm DB2 and hence I cam not use the sql query with LIMIT keyword. I managed to write a subquery and bring the same fucntionality that LIMIT does. My problem is that paging is not working. I am fetching almost 2000 rows from my database, The rows that are initially loaded are displayed and when I try navigate through paging, I do not see any change in the data at Grid. Looking forward towards your Kind help. Please let me know if any more details needed. I am using the latest Version of JqGrid and PHP 5.2.2 and my application runs on IIS7 Server.


Personal Tools
;
;