dhtmlxConnector guide and code samples


1. Introduction

1.1. Scope of the Document


This document describes dhtmlxConnector class set, defines its methods and global parameters. This documentation is created to give users full understanding of every feature of this class set, and also to enable users to implement dhtmlxConnector quickly and easily.

1.2 Target Readers

Target readers are users (developers).

2. What is dhtmlxConnector

dhtmlxConnector is a set of PHP classes which helps to simplify server side operations related to dhtmlx library components (dhtmlxGrid/TreeGrid, dhtmlxTree, dhtmlxCombo).

The following operations are available in dhtmlxConnector :


Wide range of events provided by dhtmlxConnector gives to developer the ability to implement server side functionality of any level of complexity as for loading data, as for update operations.

3. Initialization of dhtmlxConnector


3.1 Client side code

No modifications on client side are required for regular data loading. In other cases you should include the connector.js file (located in dhtmlxConnector_php/codebase) into your page. To perform any update operations you have to use dhtmlxDataProcessor, which has been already included in both Professional and Standard editions of dhtmlx library.

For data loading you need to point load (or loadXML) method of dhtmlx component to connector file:

myGrid = new dhtmlXGridObject("pObjId");
... //grid configuration commands
myGrid.load("myconnector.php");

To perform insert/update/delete operations you should add dhtmlxDataProcessor (for more details about dhtmlxDataProcessor see related documentation):

myDP = new dataProcessor("myconnector.php");
myDP.init(myGrid);

3.2 Server side code

If you need to use dhtmlxConnector on server side we strongly recommend to use internal objects for getting/setting data values (to avoid any confusions do not use GET or POST variables directly).

To start operating with dhtmlxConnector you should do the following:

require("connector/grid_connector.php");


$res=mysql_connect("localhost","root","");
mysql_select_db("myDatabase");

After have implemented these operations you are ready to instantiate connector object. The only database connection link variable is mandatory parameter in all constructors. Optionally, you can specify database type ("MySQL" by default. Other possible: "Postgre")

$gridConn = new GridConnector($res,"MySQL");


4. Loading/Editing data

4.1 Single table operations

Once you get connection object you are ready to decide what data should take part in load/update operations. Where all necessary data is placed in one database table you should use the render_table method:

$gridConn->render_table("mytable","item_id","item_nm,item_cd",["parent_id"]);

The parameters of this method are:


This is all you need to make connector implement select, insert, update and delete operations.

* You are allowed to use alias names for fields.
The syntax is:

field_name{alias_name}

If you set alias, you'll have to refer to this field using alias in other operations as well.

4.2 Joint tables and complex queries operations

You are allowed to use any SQL statements to populate any dhtmlx component through dhtmlxConnector. This is possible with the render_sql method:

$gridConn->render_sql("Select * from tableA, tableB where tableA.id=tableB.id", "a.id","name,price","parent_id");
The parameters of the render_sql method are the following:


* You are allowed to use aliases for fields.
The syntax is:
field_name{alias_name}
If you set alias, you'll have to refer to this field using alias in other operations as well.

In case your SQL query was against single table, it is quite probable that insert/update/delete operations do not require any additional code. dhtmlxConnector will parse your SQL and generate insert/update/delete statements based on used table and fields names.

If your SQL statement contains more than one table, you should choose one of two ways to implement insert/update/delete operations correctly:


if($gridConn->is_select_mode()){//code for loading data
$gridConn->render_sql("Select * from tableA, tableB where tableA.id=tableB.id", "a.id","name,price");
}else{//code for other operations - i.e. update/insert/delete
$gridConn->render_table("tableA","id","name,price");
}

5. Formatting/Changing Data before Loading

In case you need to update values which were returned from database table or set some specific formatting before sending them to client side, you should use the "beforeRender" event handler. For more details see Events System chapter of this manual.

6. Dynamical Loading

dhtmlxConnector supports dhtmlxGrid "Smart Rendring with Dynamical Loading" mode, dhtmlxTree and dhtmlxTreeGrid "Dynamical Loading" modes, dynamical loading for dhtmlxCombo autocomplete mode.

   $gridConn->dynamic_loading([$rowsNum]);

The parameter(s) are:


7. Sorting

This functionality is available for dhtmlxGrid/TreeGrid. Server side sorting with dhtmlxTreeGrid doesn't support open states.

Using "beforeSort" event you can define SQL for "order" statement. For more details see Events System chapter of this manual.

8. Filtering

This functionality is available for dhtmlxGrid/TreeGrid. Server side filtering with dhtmlxTreeGrid doesn't maintain open states.

To enable server side filtering you should use one of the following in-header filter types while configuring dhtmlxGrid:


To change filtering rule you can define related part of "where" statement using "beforeFilter" event.

In case of #connector_select_filter you can define which options will be shown in list (usefull for dyn. loading , when full list of options may be not available on client side) with "beforeFilterOptions" event.

For more details see Events System chapter of this manual.

9. Complex updates

For update operations which require their own logic, you can use custom sql statements or event handlers.

Using custom SQL statements you can define your own SQL for specific action (Insert, Update or Delete) as follows:

$gridConn->sql->attach("Update","Update tableA set name='{name}', price={price} where id={a.id}");

The parameters are the following:



In case custom SQL is unsufficient (for example you need to process values before using them in SQL) you should use events handlers. For more details see Events System chapter of this manual.

10. Validating Data

To implement server side validation of incoming data you should use "beforeUpdate", "beforeInsert", "beforeDelete" events . For more details see Events System chapter of this manual.

11. Error Handling and Logging

We strongly recommend to log all operations.

$gridConn->enable_log("path to log file");

In the code string above you should specify only one parameter:


If any error occurs during data processing client side data processor object will receive "error" action, which will contain short info about the problem (full info will be written in the log )
If you want to show full error info on client side you should use the following code string (useful for debug, not recommended for production )

$gridConn->enable_log("path to log file",true);

12. Security

dhtmlxConnector is open to external access of another programs that's why any external user is allowed to change data in DB, thus adding some kind of session based autentification is strongly recommended.
Built in security manager allows to limit connector for certain operations only.

$gridConn->access->deny("read"); //blocks Select action
$gridConn->access->deny("add"); //blocks Insert action
$gridConn->access->deny("edit"); //blocks Update action
$gridConn->access->deny("delete"); //blocks Delete action

$gridConn->allow($some) can be used to revert denied call

13. Events System

Available events:



13.1 beforeSort

This event occurs before sorting on server side. Returned value replaces sorting rule used by default.

$gridConn->event->attach("beforeSort",handlerFunc);

handlerFunc gets the following arguments:


returned value must be valid SQL string, which will be added to select query, you can use any SQL commands here

Sample of usage:

function handlerFunc($column,$order){
return " LENGTH(".$column.") ".$order;
}
$gridConn->event->attach("beforeSort",handlerFunc);

//sorts by length of $column


13.2 beforeFilter

The beforeFilter event occurs before filtering on sever side. Returned value replaces default part of "where" statement related to filtered column.

$gridConn->event->attach("beforeFilter",handlerFunc);

In this event handlerFunc gets the following arguments:


returned value must be a valid SQL rule, you can use any SQL commands here

Sample of usage:

function handlerFunc($column,$mask){
return $column." LIKE '".$mask."%'";
}
$gridConn->event->attach("beforeFilter",handlerFunc);

//filters grid by $column searching for values which begin with $mask


13.3 beforeUpdate

This event occurs before updating values in database and can cancel default update statement (see error, invalid and success methods below). It can be used also to validate incoming values (see invalid() method below).

$gridConn->event->attach("beforeUpdate",myUpdate);

In the code string above myUpdate function gets $action object as incoming argument.
$action is an instance of DataAction object, details can be checked here


Samples of usage:

function myUpdate($action){
mysql_query("UPDATE Countries SET item_nm='{$action->get_data('name')}' WHERE item_id='{$action->get_id()}'");
$action->success();
}

//creates and runs own update statement using values came in request, cancels default update

function myUpdate($action){
if($action->get_value("name")=="")
$action->invalid();
}
//checks if value of name is empty, then cancel update. Otherwise proceed with default update.

function myUpdate($action){
$new_value = rand(0,100);
$action->set_value("name",$new_value);
}
//sets new value for name and proceeds with default update.

13.4 beforeInsert

This event occurs before inserting values in database and can cancel default insert statement. It can be used also to validate incoming values. For more details see "beforeUpdate" event description.

13.5 beforeDelete

The beforeDelete event occurs before deleting record in database and can cancel default delete statement. For more details see "beforeUpdate" event description .

13.6 beforeProcessing

This event occurs before beforeInsert, beforeUpdate, beforeDelete events occur. The beforeProcessing method occurs for all these operations. It can be cancelled in the same way as the aforementioned events. For more details see "beforeUpdate" event description .

13.7 afterProcessing

This event occurs after insert, update, delete actions. It can be used to pass additional values to client side.

Example of usage:

function doAfterProcessing($action){
$action->set_response_xml("<guid>some value</guid>")
}
$gridConn->event->attach("afterProcessing",doAfterProcessing);

//adds new xml as a child of default <action> tag which is passed to client side as response

13.8 beforeRender

The beforeRender event occurs after data has been selected from the database but before its outputting to client.

$gridConn->event->attach("beforeRender",myFunction);

In this event myFunction gets data item object as incoming argument. This object has different methods for different client side components. Details can be checked here



function my_code2($column){
if ($column=="item_cd")
return array("dummy","838","known");
return true;
}
$gridConn->event->attach("DataFilterOptions",my_code2);

13.9 beforeFilterOptions


In case of #connector_select_filter you can define which options will be shown in list ( usefull for dyn. loading , when full list of options may be not available on client side )

function createMyOptions($column){
if ($column=="item_cd")
return array("dummy","838","known");
return true;
}
$gridConn->event->attach("beforeFilterOptions",createMyOptions);