Making Queries

Simple queries

Applicable to: Grid, TreeGrid, Tree, Combo, Scheduler, DataView, Chart, Form, DataStore, DHTMLX Touch Components

By default, the connector generates all INSERT/UPDATE/DELETE queries automatically, based on configuration.

For more details on this topic, see the 'Basic Loading' chapter.

In case of dnd, the connector will process an action as a sequence of 'insert' and 'delete' operations.

Complex queries

When you need to define your own logic you should use one of two ways:

Custom queries for an action

Applicable to: Grid, TreeGrid, Tree, Combo, Scheduler, DataView, Chart, Form, DataStore, DHTXML Touch Components

You can define your own SQL code for a specific action (INSERT, UPDATE or DELETE) as follows:

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

Parameters:

  • an action's name. Possible values are: 'Update', 'Insert', 'Delete'
  • SQL statement. It can use the fields(or their aliases) which were mentioned in the render_sql or render_table method while loading data.

Using server-side events

Applicable to: Grid, TreeGrid, Tree, Combo, Scheduler, DataView, Chart, Form, DataStore, DHTMLX Touch Components

To customize operations you can use the following server-side events:

//data preprocessing before update
function my_update($data){
    $price = $data->get_value("price");
    $price = intval($price);
    $data->set_value("price","10");
} 
$conn->event->attach("beforeUpdate","my_update")
//including an additional field to request
function my_update($data){
    $data->add_field("userId",1); //will be included in update processing
} 
$conn->event->attach("beforeUpdate","my_update")
//a fully custom code
function my_update($data){
    global $conn;
    $price=$data->get_value("price");
    $id=$data->get_value("id");
    $conn->sql->query("UPDATE some_table SET price='{$price}' where id={$id}");
    //success() marks operation as finished and stops any further action processing
    $data->success(); 
} 
$conn->event->attach("beforeUpdate","my_update")

Transactions

Applicable to: Grid

Grid allows using transactions for data INSERT/UPDATE/DELETE operations (make sure that the used DB engine has the support of transactions).

They can be enabled in 2 modes:

1) global

$conn->sql->set_transaction_mode("global");
  • Component uses single transaction for all records inside of single request.
  • If any operation fails, all record operations will be rolled back, all updated rows will be returned with error status
  • If custom validation was assigned and has blocked any record - all the records inside of the request will be blocked

2) record

$conn->sql->set_transaction_mode("record");
  • Component uses separate transactions for each record in request
  • If any operation fails, it will not affect other operations

INSERT operation in Oracle DB

Applicable to: Grid, TreeGrid, Tree, Combo, Scheduler, DataView, Chart, Form, DataStore, DHTMLX Touch Components

Oracle doesn't have auto ID generation functionality, so you need to provide some custom ID for each insert operation.

There are 2 ways to achieve this:

1) custom ID generation - id generated by PHP code

function pre_insert($data){
    $new_id = time()%10000; //any other ID generation logic can be used here
    $data->add_field("EMPLOYEE_ID",$new_id);
}
$grid->event->attach("beforeInsert","pre_insert");
$grid->render_table("EMPLOYEES","EMPLOYEE_ID","FIRST_NAME,LAST_NAME");

2) using sequence for ID generation

$grid->sql->sequence("EMPLOYEES_INC.nextVal"); //sequence name
$grid->render_table("EMPLOYEES","EMPLOYEE_ID","FIRST_NAME,LAST_NAME");
Back to top