Skip to content

Instantly share code, notes, and snippets.

@pepebe
Last active Feb 3, 2022
Embed
What would you like to do?
Data Grid Tutorial by Susann Ottwell

Backup of a an article write by Susan Ottwell in February 2008

The original source seems to be lost but there is still a copy a archive.org

Disclaimer: I didn't check the code for potential problems with current versions of MODX. If you find something that breaks the code, post it in the comments below.

Data Grid Part I

I found myself unexpectedly dropped into the deep end of ext development when I needed to update a three-year-old site I had built with the MODx CMS/CMF precursor Etomite. Since this site needed features not available in a stock Etomite, I used the then-extension mods to Etomite known as MODx2. I also used a modification of the default Etomite site template, which was table-based. The site had since been updated to MODx 0.9.2, but the design stayed the same.

This time around, the site needed a whole new look, so after updating to the latest version of MODx I created a fairly simple CSS based template that would not break the existing content and menu structure. That part, this being MODx, was easy. Just copy/paste the HTML source from a page I already had that I liked, add the MODx tags, and tweak the CSS a bit.

The Application

The interesting part comes from a custom application I had built for the site's clients to be able to log in and view data pertinent to them from the database. It's easy enough to use the basic WebLogin snippet script to send the user to the page containing the data grid using its login page option. Then the user was given a menu bar, with "Browse", "Search", and "Logout" buttons. This was a simple PHP application that would use the user's login name to select that user's data from the table and display it in a basic table. No sorting, no paging, and the whole page refreshing on every submission.

I decided to rewrite the whole thing using an extjx sortable paging grid.

The Template

I created a new template to use for this page, since it would need a load of script tags to include all of the library and css files, plus I wanted a full-page layout for the data grid, as well as a minimal masthead; basically just a nice background image for the form that contains the logout button and the user's name.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>sottwell archive » Data Grid</title>
<meta http-equiv="content-type" content="text/html; charset=UTF-8" />
<base href="http://sottwell.pogwatch.com/"></base>
<link rel="stylesheet" type="text/css" href="assets/templates/trans/client.css" />
<link rel="shortcut icon" href="favicon.ico" />
<script type="text/javascript" src="assets/js/extjs/adapter/ext/ext-base.js"></script>
<script type="text/javascript" src="assets/js/extjs/ext-all.js"></script>
<link rel="stylesheet" type="text/css" href="assets/js/extjs/resources/css/ext-all.css" />
<script type="text/javascript" src="assets/js/grid-paging.js"></script>
</head>
<body>
<div id="maindiv">
Content goes here
</div>
</body>
</html>

The Document

The business end of the application is contained in the Dataview document. So I edited the document I was originally using.

<form id="clientForm" name="clientform" action="datagrid-1.html" method="post">
<fieldset id="clientSet">
<label id="clientLogoutLabel" for="clientLogout">
<input id="clientLogout" class="button" type='submit' name='logout' value='' />
<span id="clientName"></span>
</label>
</fieldset>
</form>
<div id="grid-paging" style="margin:20px auto; border:1px solid #99bbe8; overflow:hidden; width:980px; height:450px;"></div>

The Snippet

The snippet is critical to the function of the application, since we need to be able to specify the user whose data we want to retrieve for the grid. Also since this is a multilanguage site, and this page is used for all users, it needs to know which language's home page to send the user back to and which language file to include.

For the snippet itself, I included an external .php file.

define("IN_DATABASE", "true");
if(is_file("assets/snippets/database/data.php")) {
include "assets/snippets/database/data.php";
}
return '';

And the actual work is done in the external .php file.

<?php
// make sure nobody tries to access this directly
if(IN_DATABASE !="true") die('<h1 style="color:red;">Illegal File Access</h1>');
// get the user's language from the cookie
$lang = is_numeric($_COOKIE['Language']) ? $_COOKIE['Language'] : $modx->config['site_start'];
// load the language page
include "assets/snippets/database/langs/$lang.php";
// set up the label for the logout button
$modx->setPlaceholder('logout', $_client['logout']);
// set the client name for display and for the AJAX query string
$modx->setPlaceholder('clientname', $_SESSION['webShortname']);
// process logout
if(isset($_POST['logout'])) {
// redirect to home page
$url = $modx->makeURL($lang);
$modx->sendRedirect($url);
}
?>

A Client Update Part II

In the previous article, I described the setting up of the page for the extjs data grid. Now it's time to get into working with the ext libraries.

First, I downloaded ext and put it in my /assets/js/ folder.

Using the Data Grid Object

I'll show the entire .js file to begin with, then go over it part by part.

Ext.onReady(function(){

    Ext.BLANK_IMAGE_URL = 'assets/js/ext2/resources/images/default/s.gif';

    var store = new Ext.data.Store({
        proxy: new Ext.data.HttpProxy({
            url: 'assets/snippets/database/grid-paging-data.php'
        }),

        reader: new Ext.data.JsonReader({
            root: 'results',
            totalProperty: 'total',
            id: 'term',
            fields: [
                {name: 'tb_id', type: 'int'}, 
                'german', 
                'english', 
                'italian', 
                'french'
            ]
        }),

        remoteSort: true
    });
 
    store.setDefaultSort('english', 'asc');
    
    var cm = new Ext.grid.ColumnModel([{
           header: "Id",
           dataIndex: 'tb_id',
           width: 65
        },
        {
           header: "German",
           dataIndex: 'german',
           width: 225
        },
        {
           header: "English",
           dataIndex: 'english',
           width: 225
        },
        {
           header: "Italian",
           dataIndex: 'italian',
           width: 225
        },
        {
           header: "French",
           dataIndex: 'french',
           width: 225
        }
    ]);

    cm.defaultSortable = true;

    var grid = new Ext.grid.GridPanel({
        el:'topic-grid',
        width:985,
        height:500,
        title:'View Termbase',
        store: store,
        cm: cm,
        trackMouseOver:false,
        loadMask: true,
        enableHdMenu: false,
        bbar: new Ext.PagingToolbar({
            pageSize: 25,
            store: store,
            displayInfo: true,
            displayMsg: 'Displaying rows {0} - {1} of {2}',
            emptyMsg: "No data to display",
            items:[
                '-', 
                {text: 'Search', handler: 
                    function(){
                        grid.store.load({params:{start: 0, limit: 25}});                    
                    }
                },
                {xtype: 'field', id: 'filter', name: 'filter', autoCreate: {tag: "input", type: "text", size: "20", autocomplete: "off"}}            
            ]
        })
    });
    
    grid.render();
    
    store.on('beforeload', function() {
        store.baseParams = {
            client:document.getElementById('clientName').innerHTML,
            filter:document.getElementById('filter').value
        };
    }); 

    store.load({params:{start:0, limit:25}});

});

To start with, you can see that the whole thing is wrapped in one big function.

Ext.onReady(function(){

This means that as soon as the page is loaded into the browser, the script will run. In this case, the data grid is immediately loaded with the first set of paged data.

Next you'll see a line specifying the URL (relative in this case) to a 1-pixel transparent image that the grid needs for its proper layout. The default URL for this image is the extjs home site, so you'll want to make sure to set this to wherever you put your library files.

    Ext.BLANK_IMAGE_URL = 'assets/js/ext2/resources/images/default/s.gif';

The Data Store Object

This object defines the type of data you're using. It establishes where the data is coming from, and in what form it will be coming.

    var store = new Ext.data.Store({
    ...
    });

First it takes a proxy object; how and where is it going to fetch its data. In my case, since my data resides on the same server as the script, I'm using the HttpProxy.

         proxy: new Ext.data.HttpProxy({
            url: 'assets/snippets/database/grid-paging-data.php'
        }),

GOTCHA: Take note of the comma at the end. This is the first in a list of parameters for the data store object, so it needs a comma, not a semicolon. Be very careful not to have a comma after the last parameter, or IE will fail to display anything.

Next we need to specify a data reader. I'm formatting my data as json in the back-end, so I'll use the JsonReader object.

        reader: new Ext.data.JsonReader({
            root: 'results',
            totalProperty: 'total',
            id: 'term',
            fields: [
                {name: 'tb_id', type: 'int'}, 
                'german', 
                'english', 
                'italian', 
                'french'
            ]
        }),

This tells ext that I'm returning several pieces of data. To begin with, the name of the array containing the results of the query is "results". Another value being returned is the number of result rows being returned, this is the totalProperty and named "total". The element ID (for use in CSS or DOM traversal) should be "term". The next section lays out the structure of the data being returned. In my case, I was displaying words in up to four different languages. So I'm returning the row ID, and each of the four language fields.

Finally, I specified that the result set should be sorted at the back-end.

        remoteSort: true

Notice, no comma! This is the last parameter to my data store object.

Now that the data store object has been defined, I can use it.

store.setDefaultSort('english', 'asc');

This sets the default sort to the English field. So when the grid is first displayed, the first and subsequent pages of data will be sorted by English.

The Grid Column Model

Ok, we've got the data in our data store, now what are we going to do with it? I want to display it in a grid, so I'll configure a grid ColumnModel object to tell my grid how to lay out my data.

    var cm = new Ext.grid.ColumnModel([{
           header: "Id",
           dataIndex: 'tb_id',
           width: 65
        },
        {
           header: "German",
           dataIndex: 'german',
           width: 225
        },
        {
           header: "English",
           dataIndex: 'english',
           width: 225
        },
        {
           header: "Italian",
           dataIndex: 'italian',
           width: 225
        },
        {
           header: "French",
           dataIndex: 'french',
           width: 225
        }
    ]);

This should be pretty self-explanatory; it just describes the columns I want in the grid.

And since I've defined my column model object, I can use it to state that I want these columns all sortable by default. You can specify if a column is sortable in its description in the column model object if you prefer.

    cm.defaultSortable = true;

The Grid Object

Now I define my grid object.

    var grid = new Ext.grid.GridPanel({
        el:'topic-grid',
        width:985,
        height:500,
        title:'View Termbase',
        store: store,
        cm: cm,
        trackMouseOver:false,
        loadMask: true,
        enableHdMenu: false,
        bbar: new Ext.PagingToolbar({
            pageSize: 25,
            store: store,
            displayInfo: true,
            displayMsg: 'Displaying rows {0} - {1} of {2}',
            emptyMsg: "No data to display",
            items:[
                '-', 
                {text: 'Search', handler: 
                    function(){
                        grid.store.load({params:{start: 0, limit: 25}});                    
                    }
                },
                {xtype: 'field', id: 'filter', name: 'filter', autoCreate: {tag: "input", type: "text", size: "20", autocomplete: "off"}}            
            ]
        })
    });

Lots of parameters! We'll take them one at a time.

  • el - the element to put the grid into. If you read the previous article, you'll recall that in my document I put this:
<div id="grid-paging" style="margin:20px auto; border:1px solid #99bbe8; overflow:hidden; width:980px; height:450px;"></div>

So the el parameter "grid-paging" tells ext to put the grid in that div container.

  • width, height, title - specify the width and height of the grid, and what title to put in the top title bar of the grid.
  • store - the name of the data store, which we set when we created our data store object.
  • cm - the column model object to use.
  • trackMouseOver - This will cause the row the mouse is hovered over to be highlighted. I don't particularly want that for this grid, so I set it to false.
  • loadMask - this enables or disables the optional "Loading..." message and translucent grey screen overlay while loading data. It's a nice "Web 20" kind of touch.
  • enableHdMenu - enables or disables a drop-down menu in the header for each column. I don't want one, so I put "false".
  • bbar - this is for the bottom bar. It in itself is an object, and in this case I wanted a paging grid.

The Paging Bottom Bar

  • pageSize - how many pages to load each time.
  • store - again, the name of our data store object.
  • displayInfo - enables or disables paging information on the right side of the bar.
  • displayMsg - the text to display
  • emptyMsg - text to display if no records were found.
  • items - any custom items you want to add to the bar. I wanted to add a simple search field.

Adding a Search Field

I just wanted a simple search field, so I added it to the bottom bar. First, I added a hyphen to separate the prev-next paging buttons from the search field. Then I added a search button, with a function to reload the grid when it's clicked. The default item is a button, so I didn't need to specify what kind of element to add here.

{text: 'Search', handler: 
    function(){
        grid.store.load({params:{start: 0, limit: 25}});                    
    }
},

GOTCHA The bottom bar object has not yet been instantiated, so even though we did specify a store object, we can't access the bbar.store object at this point. We have to use the grid.store object here.

And finally, I added a field for the user to enter his search word or phrase into. This is not a very sophisticated search, but it is sufficient for my needs.

It took me several hours of searching through the ext documentation, and trial-and-error, before I finally got it to work properly. Let's take a closer look at the search field.

{xtype: 'field', id: 'filter', name: 'filter', 
autoCreate: {tag: "input", type: "text", size: "20", autocomplete: "off"}}            

The "xtype" tells ext what type of element this is; it's a field. Then its id, which is important, as we'll see later. Next is the field's name. Then I specify how it is to be created: its HTML tag is "input", its type is "text", its size is "20" and autocomplete is "off", so ext will create this:

<input type="text" name="filter" id="filter" size="20" autocomplete="off">

Finally, now that the grid and all of its parts have been defined, we tell ext to do it.

    grid.render();

At this point, even with no data, a grid will show on the page. An empty grid, of course.

Fetching the Data Since I want the data filtered and sorted, I have to tell it to pass these POST values on to the back-end script.

    store.on('beforeload', function() {
        store.baseParams = {
            client:document.getElementById('clientName').innerHTML,
            filter:document.getElementById('filter').value
        };
    }); 

Now, every time the data store gets loaded into the grid, these values get sent off along with the AJAX request. The 'client' POST value is taken from the form at the top of the page. The 'filter' value comes from the field we added to the grid's bottom bar. Now you see why the ID we gave that field was important.

And at last, the initial data load is requested (remember, this is all inside of the onReady function, so it will be fired off as soon as the page is loaded).

    store.load({params:{start:0, limit:25}});

Since this is a paging grid, we specify the initial starting point and limit for the records to select. The grid automatically keeps track so subsequent requests will select the proper set of records. But the actual database query is the job of the back-end script, and we'll go over that in the next article.

Data Grid Part III

In the previous article, I covered the javascript file line-by line. Now we'll go through the back-end php script to see how to process the AJAX request and return the data so ext can load it into the grid.

$link = mysql_pconnect("server", "username", "password") or die("Could not connect");
mysql_select_db("database") or die("Could not select database");

$sql_count = "SELECT * FROM tb_termbase WHERE tb_user = '".$_POST['client']."'";

if(isset($_REQUEST['filter']) && $_REQUEST['filter'] != '') {
    $sql_count .= "AND (english LIKE '%".$_REQUEST['filter']."%'       
                       OR german LIKE '%".$_REQUEST['filter']."%'
                          OR french LIKE '%".$_REQUEST['filter']."%'
                           OR italian LIKE '%".$_REQUEST['filter']."%')";
    }

$sql = $sql_count . " ORDER BY ".$_POST['sort']." ".$_POST['dir']."
                      LIMIT ".(integer)$_POST['start'].", ".(integer)$_POST['limit'];

$rs_count = mysql_query($sql_count);

$rows = mysql_num_rows($rs_count);

$rs = mysql_query($sql);

while($obj = mysql_fetch_object($rs))
{
	$arr[] = $obj;
}

echo $_REQUEST['callback'].'({"total":"'.$rows.'","results":'.json_encode($arr).'})'; 

Connecting to the Database

Since this is an independent script running outside of MODx, it doesn't have access to the MODx API. So we need to connect to the database ourselves.

$link = mysql_pconnect("server", "username", "password") or die("Could not connect");
mysql_select_db("database") or die("Could not select database");

A really nice feature of extjs is that it will generate an error log in the same folder as the script, so even though these messages will never show on the screen, if your grid isn't working you can check the error log where they will be recorded.

Building the Queries

Next is the usual tedious setup of the query. We need to check the POST values that were sent by the AJAX request and incorporate them into the query to filter it and sort it.

$client = mysql_real_escape_string($_POST['client']);
$sql_count = "SELECT * FROM table WHERE client = '".$client."'";

if(isset($_POST['filter']) && $_POST['filter'] != '') {
    $filter = mysql_real_escape_string($_POST['filter']);
    $sql_count .= "AND (english LIKE '%".$filter."%' 
                           OR german LIKE '%".$filter."%' 
                            OR french LIKE '%".$filter."%' 
                            OR italian LIKE '%".$filter."%')";
}

$sort = mysql_real_escape_string($_POST['sort']);
$dir = mysql_real_escape_string($_POST['dir']);
$start = is_numeric($_POST['start']) ? (integer)$_POST['start'] : 0;
$limit = is_numeric($_POST['limit']) ? (integer)$_POST['limit'] : 25;
$sql = $sql_count . " ORDER BY ".$sort." ".$dir." LIMIT ".$start.", ".$limit;

GOTCHA Notice that I typecast the start and limit values to integer. POST values are sent as strings, and the database needs an integer for these values.

All of the values are sanitized or validated to minimize the chances of evil-intended people trying to break the site with SQL injection. You should never, ever trust any data coming in from any outside source, including your own javascript.

Determining when ext sends by GET or by POST took a while. If you send extra parameters, then it uses POST. But if it's just a straight default request, it uses GET.

Another issue that took a while to discover was exactly what parameters does the paging and auto-sort functions send? I finally found the sort parameters in the API documentation under the Ext -> data -> store item, defined in the Remote Sort config option description.

If remote sorting is specified, then clicking on a column header causes the current page to be requested from the server with the addition of the following two parameters:

  • sort : String - The name (as specified in the Record's Field definition) of the field to sort on.
  • dir : String - The direction of the sort, "ASC" or "DESC" (case-sensitive).

I never did find the documentation for the paging parameters, but the demo I built this from had "start" and "limit", so it's fairly obvious those are what the php file needs to look for.

Running the Queries

First, we just run the basic SELECT query, in my case filtered by the username, and if it's a search request filtered by the search criteria, to get the count of the total number of records.

$rs_count = mysql_query($sql_count);
$rows = mysql_num_rows($rs_count);

Then we run the full combined query, with paging details and all.

$rs = mysql_query($sql);

The data returned by the second query is extracted and loaded into an array.

while($obj = mysql_fetch_object($rs))
{
    $arr[] = $obj;
}

And finally, the results are packaged up into a json-encoded string, and the AJAX callback function is invoked with the parameters for the total number of rows and the json string of results.

echo $_REQUEST['callback'].'({"total":"'.$rows.'","results":'.json_encode($arr).'})'; 

If you remember our reader object, it was defined with these parameters, so it can unpack and provide the data to the grid for display.

        reader: new Ext.data.JsonReader({
            root: 'results',
            totalProperty: 'total',
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment