This is a read-only archive. Find the latest Linux articles, documentation, and answers at the new Linux.com!

Linux.com

Feature: Programming

Create an AJAX Web site using dhtmlxGrid to present data

By Ben Martin on January 28, 2008 (7:00:00 PM)

Share    Print    Comments   

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:

  1. Include the needed JavaScript files and a stylesheet to theme the grid.
  2. Create a DIV HTML element where you would like the control to exist.
  3. 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 "\t\t<cell>$cvalue^$cvalue</cell>\n"; } else { echo "\t\t<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.

Ben Martin has been working on filesystems for more than 10 years. He completed his Ph.D. and now offers consulting services focused on libferris, filesystems, and search solutions.

Share    Print    Comments   

Comments

on Create an AJAX Web site using dhtmlxGrid to present data

Note: Comments are owned by the poster. We are not responsible for their content.

Create an AJAX Web site using dhtmlxGrid to present data

Posted by: Anonymous [ip: 70.50.233.231] on January 28, 2008 08:14 PM
Be careful before you buy that control. The code is highly unorganized and low-quality. I fell victim to it.

#

Create an AJAX Web site using dhtmlxGrid to present data

Posted by: Anonymous [ip: 192.168.1.89] on January 29, 2008 10:37 AM
>>The code is highly unorganized
Can you point to any component with the similar functionality and "high quality"?
By my tests, it is a fastest javascript grid - if it is a "loq-quality", then what is a "high-quality" code?

#

Create an AJAX Web site using dhtmlxGrid to present data

Posted by: Anonymous [ip: 192.168.1.181] on January 29, 2008 11:13 AM
Why do I need to look into its code? To my mind any someone else's code is highly unorganized and low-quality :)

#

Re: Create an AJAX Web site using dhtmlxGrid to present data

Posted by: Anonymous [ip: 99.229.127.91] on January 29, 2008 06:21 PM
Agreed... but buggy or non-secure is quite another. :)

#

Create an AJAX Web site using dhtmlxGrid to present data

Posted by: Anonymous [ip: 83.160.225.5] on January 30, 2008 12:16 PM
What about ExtJS... Combine it with PHP and you are done, a quality javascript powered grid for free. Want something more maintainable try adding Symfony to PHP or use Ruby if you prefere...

#

Create an AJAX Web site using dhtmlxGrid to present data

Posted by: Anonymous [ip: 192.168.1.89] on January 31, 2008 09:39 AM
dhtmlxGrid contains dataProcessor lib with ready to use code for PHP, is some similar solution exists for ExtJS ?

#

Create an AJAX Web site using dhtmlxGrid to present data

Posted by: Anonymous [ip: 71.8.76.209] on February 01, 2008 06:59 AM
I second the ExtJs comment. It's the best rich UI Javascript control library out there. works on top of prototype, jquery, or stands on it's own... your choice ... lgpl and commercial licenses available. http://extjs.com

#

Create an AJAX Web site using dhtmlxGrid to present data

Posted by: Anonymous [ip: 83.160.225.5] on February 01, 2008 09:02 PM
I am working on a plugin for symfony (PHP framework) which processes the data of the ExtJs Grid. Take a look at http://trac.symfony-project.com/wiki/sfExtjsThemePlugin

#

Create an AJAX Web site using dhtmlxGrid to present data

Posted by: Anonymous [ip: 192.168.1.150] on February 13, 2008 09:59 AM
ExtJS is a framework for creating samples. I tried it with real amount of data and it performance was auful. Interface is nice looking but blinks all the time when mouse has been moving over. Also, you need to write some complex structures to make it work you need. dhtmlx is perfect - who said about bugs? All problems I had (mostly questions) were resolved by their support same day. I like it. :)

#

This story has been archived. Comments can no longer be posted.



 
Tableless layout Validate XHTML 1.0 Strict Validate CSS Powered by Xaraya