My First Grid

~~DISCUSSION~~ ====== 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 : - A database with some sample data, - A HTML page to show the data, and - 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) {{:wiki: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 [[wiki:how_to_install | installation]] section, we have the following code:







My First Grid

==== 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 tags in the document. The definition of the grid is done via the HTML tag , as shown here:

…

…

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 inside of the 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

tag, 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

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 [[:wiki:options | API Methods]] and [[:wiki:colmodel_options | 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.| |pager|Defines 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.| |rowNum|Sets 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| | rowList|An 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| |sortname|Sets the initial sorting column. Can be a name or number. This parameter is added to the URL for use by the server routine| |viewrecords|Defines whether we want to display the number of total records from the query in the pager bar| |caption|Sets 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:




   
   
   
    
       cellcontent 
       cell content]]> 
        …
    
    
       cellcontent 
       cell content]]> 
        …
    
      …

The tags used in this example are explained in the following table. ^Tag^ Description ^ |rows|the root tag for the grid| |page|the number of the requested page| |total|the total pages of the query| |records|the total records from the query| |row|a particular row in the grid| |cell|the 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 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.


 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 = "";
$s .=  "";
$s .= "".$page."";
$s .= "".$total_pages."";
$s .= "".$count."";

// be sure to put text data in CDATA
while($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
    $s .= "";            
    $s .= "". $row['invid']."";
    $s .= "". $row['invdate']."";
    $s .= "". $row['amount']."";
    $s .= "". $row['tax']."";
    $s .= "". $row['total']."";
    $s .= "";
    $s .= "";
}
$s .= ""; 

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 [[http://www.trirand.com/blog/?page_id=393/help/|Help Forum]]