$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:
-
sql statement
-
field to use as identifier *
-
list of fields which should be used as values of component item (cells for grid, item label for tree, text of option for combo) *
-
parent ID field name for hierarchical structures (required for tree and treegrid) *
* 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:
-
Using the render_table method. In this case you should define for which table insert/update/delete operations will be applied, then provide connector with details about this table using the following code snippet:
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");
}
-
Using event handlers. In case the logic of update operations is more complex you should use events handlers to define update/insert/delete statements accordingly.
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.
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:
-
no parameters for tree, treegrid and combo are required.
-
number of rows which should be initially loaded (the value should be more than number of rows visible in grid, or at least any positive number) for grid.
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.
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:
-
#connector_text_filter - searches for values which contain mask defined through text field
-
#connector_select_filter - searches for values which contain mask defined through list of possible values
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.
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:
-
action name. Possible values are: "Update", "Insert", "Delete"
-
SQL statement. It can use fields or aliases which were mentioned in render_sql or render_table method used for loading data.
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.
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.
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:
-
absolute or relative path to text file where log will be written
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);
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
Available events:
-
beforeSort
-
beforeFilter
-
beforeFilterOptions
-
beforeUpdate
-
beforeInsert
-
beforeDelete
-
beforeProcess
-
afterProcessing
-
beforeRender
$gridConn->event->attach("beforeSort",handlerFunc);
handlerFunc gets the following arguments:
-
$column - name of column (table column name or aliase)
-
$order - order of sorting (ASC, DESC)
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:
-
$column - name of column
-
$mask - current filtering mask
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);