Author: Ben Martin
dhtmlxGrid is a JavaScript grid control that you can use to create an AJAX interface to a relational database server. The grid view provided by dhtmlxGrid offers actions that one would expect from a desktop grid control, such as sorting by columns by clicking on them, resizing columns, rich cell rendering, keyboard navigation, themes, and drag and drop.
The standard edition of dhtmlxGrid is available under the GNU GPL and free to use, while other editions include additional features and cost from $150 to $450 depending on your needs. Methods in the JavaScript API for dhtmlxGrid indicate if they are only available in the paid versions. For this article I use the free GPL version.
Getting started
The GPL Standard Edition is distributed as a zip file. The zip file does not contain a top-level directory, so it is best expanded to an existing project or into /var/www/html/dhtmlxSuite if you plan to share it across projects on the same Web server. The commands below will install dhtmlxSuite, which contains dhtmlxGrid, and allow it to be accessible through /dhtmlxSuite on the Web server.
mkdir /var/www/html/dhtmlxSuite cd /var/www/html/dhtmlxSuite unzip /T/dhtmlxSuite.zip chown -R root.apache /var/www/html/dhtmlxSuite chmod -R 640 /var/www/html/dhtmlxSuite find /var/www/html/dhtmlxSuite -type d -exec chmod ug+x {} ;
Getting a dhtmlxGrid object on the Web page takes three steps:
- Include the needed JavaScript files and a stylesheet to theme the grid.
- Create a DIV HTML element where you would like the control to exist.
- Instantiate and initialize a dhtmlXGridObject JavaScript object, associating it with the name attribute of the DIV element it should use for display.
When you set up a dhtmlxGrid using a shared install of dhtmlxGrid you have to tell the JavaScript object what URL it can expect to find dhtmlxSuite image objects at, as in the simple example below. Even at this level the grid control will handle column resizing and sorting.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title>Simple dhtmlxSuite</title> <!-- Step 1. Include JavaScript and stylesheet for grid --> <link rel="STYLESHEET" type="text/css" href="/dhtmlxSuite/dhtmlxGrid/codebase/dhtmlxgrid.css"> <script src="/dhtmlxSuite/dhtmlxGrid/codebase/dhtmlxcommon.js"></script> <script src="/dhtmlxSuite/dhtmlxGrid/codebase/dhtmlxgrid.js"></script> <script src="/dhtmlxSuite/dhtmlxGrid/codebase/dhtmlxgridcell.js"></script> </head> <body> <h1>Simple dhtmlxSuite</h1> <!-- Step 2. Where to display the grid. --> <div id="gridbox" width="100%" height="600px" style="background-color:lightgrey;overflow:hidden"></div> <!-- Step 3. Setup the JavaScript object. --> <script> mygrid = new dhtmlXGridObject('gridbox'); mygrid.imgURL = "/dhtmlxSuite/dhtmlxGrid/codebase/imgs/"; mygrid.init(); mygrid.loadXML("simple-grid.xml"); </script> </body> </html>
The main method of interest for the grid is loadXML. This method makes building a query page simple. HTML form elements can be used to obtain information from the user, and the form submission can call a small piece of JavaScript which forms a query for the server and tells the grid object to loadXML the query URL. When the server returns an XML response to the query it is automatically displayed in the grid.
The data that was loaded in the above example came from the simple-grid.xml file, which is shown below. The XML file has a simple format. The core of the data in the grid is serialized in row elements, each containing a collection of column elements. The head element describes the columns: how to sort them, how large they should be, and what type of data the cell will contain. In this example the URL column should be changed to type="link" in order to let the user click hyperlinks for a more natural interaction. I’ll use link type cells in the next example.
<?xml version="1.0" encoding="UTF-8"?> <rows> <head> <column width="200" type="ro" sort="str">URL</column> <column width="*" type="ro" sort="str">Description</column> </head> <row id="1"> <cell>linux.com</cell> <cell>Informative article about Linux stuff</cell> </row> <row id="2"> <cell>freshmeat.net</cell> <cell>What's new in the software release scene</cell> </row> </rows>
Querying a PostgreSQL database
To keep the example simple I’ll use data similar to what was contained in the simple-grid.xml
file above, but inside a sites table in the linuxcom PostgreSQL database on localhost. The table is shown below;
linuxcom=# select * from sites; url | d ----------------------------------------------+---------------------------------------------- linux.com | Informative articles about Linux stuff freshmeat.net | What's new in the software release scene slashdot.org | Slashdot: News for nerds, stuff that matters http://www.linux-foundation.org/en/Main_Page | Main Page - The Linux Foundation (4 rows)
The PHP code shown below performs the heavy lifting. When passed a “d” CGI parameter it will
query the database for any site whose description contains the “d” string and return the resulting Web sites in a format that dhtmlxGrid can load. Once the connection with the database is created the SQL is generated and the resulting tuples looped over to generate the XML data to display to the user. The URL column is output in a format that dhtmlxGrid can create HTML links from. The cells for hyperlink type information have the format name^URL where name is shown to the user as the text of the link and the URL forms the href for the link. You will also notice that there is no longer any header information in the XML data.
<?php header('Content-type: text/xml'); echo "<rows>n"; $dbconn = pg_connect("host=localhost dbname=linuxcom user=linuxcom password=linuxcom") or die('Could not connect: ' . pg_last_error()); $wanteddesc=$_REQUEST["d"]; pg_escape_string($dbconn,$wanteddesc); $query = "select * from sites where d like '%$wanteddesc%'"; $result = pg_query($query) or die('Query failed: ' . pg_last_error()); $idx=0; while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) { echo "t<row id="$idx">n"; $firstcol=1; foreach ($line as $cvalue) { if( $firstcol ) { $firstcol=0; echo "tt<cell>$cvalue^$cvalue</cell>n"; } else { echo "tt<cell>$cvalue</cell>n"; } } echo "t</row>n"; $idx++; } ?> </rows>
I’ve kept the front end very simple for illustration purposes. A single form entry allows the user to input a search string. When the user presses Enter the search JavaScript function is called. First any existing data is cleared from the grid, then it is told to load new data from the XML obtained at the given URL. The other notable change is at the bottom of the PHP file, where the grid’s header information is set up pragmatically. The setInitWidthsP method sets the initial column widths based on given percentages instead of using pixel values.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title>PostgreSQL dhtmlxGrid example</title> <script language="JavaScript"> function search( dsc ) { mygrid.clearAll(0); mygrid.loadXML( "postgres-query-to-griddata.php?d=" + dsc ); } </script> </head> <body> <link rel="STYLESHEET" type="text/css" href="/dhtmlxSuite/dhtmlxGrid/codebase/dhtmlxgrid.css"> <script src="/dhtmlxSuite/dhtmlxGrid/codebase/dhtmlxcommon.js"></script> <script src="/dhtmlxSuite/dhtmlxGrid/codebase/dhtmlxgrid.js"></script> <script src="/dhtmlxSuite/dhtmlxGrid/codebase/dhtmlxgridcell.js"></script> <script src="/dhtmlxSuite/dhtmlxGrid/codebase/excells/dhtmlxgrid_excell_link.js"></script> Search for: <form name="searchdesc" action="javascript: search( document.searchdesc.query.value )"> <input type='text' name='query'> </form> <hr> <div id="gridbox" width="100%" height="800px" style="background-color:lightgrey;overflow:hidden"></div> <script> mygrid = new dhtmlXGridObject('gridbox'); mygrid.imgURL = "/dhtmlxSuite/dhtmlxGrid/codebase/imgs/"; mygrid.setHeader("URL,Description"); mygrid.setColTypes("link,ro"); mygrid.setColSorting("str,str"); mygrid.setInitWidthsP("30,70"); mygrid.init(); </script> </body> </html>
The resulting Web interface is shown at right. Note that the only part of the page that is reloaded or changed by submitting the form is the grid itself.
The dhtmlxGrid is only one of many components that make up the dhtmlxSuite distribution. Although many methods are not present in the GPL edition, it is still effective for quickly creating an AJAX interface to grid data.
Categories:
- Programming
- Internet & WWW
- Databases
- Apache & Web Servers
