Advanced

Custom Server Scripts

In case of custom server scripts you write all the logic of database connection, execute queries that get data from the necessary tables as well as queries for data inserting, updating and deleting.

Basic client-side loading and saving pattern remains unchanged:

webix.ui({
    view:"list",
    url:"load.php", 
    save:"save.php"
})

The url property loads data returned by the dedicated script, while save points to the script that saves data to database each time you make changes on the client side.

The following basic instructions will help you tune your scripts to return data and provide responses for editing operations. Here we work on PHP with an SQLite3 database.

In case you need to get data from a different domain with relations to the data in your app, and you are unsure what to do, consult our solution to cross-domain data loading.

Data Loading

Webix components support incoming data in JSON, JSArray, XML, HTMLtable and CSV formats, so ensure that your script returns properly formatted data.

For JSON, encode the resulting data array with the dedicated function (json_encode() in PHP)

//connect to database
$db = new SQLite3('../../../common/testdata.sqlite');
 
//select data
$res = $db->query("SELECT * FROM films");
 
//convert data to json
$data = array();
while ($rec = $res->fetchArray(SQLITE3_ASSOC)) //each row
    $data[] = $rec;
//output json
echo json_encode($data);

Datatree loading

Datatree can be populated with inline data grouped on the client side or with hierarchical data grouped by a server-side script.

For hierarchical data, you need to have:

  • the relation_id field in the database table, that for each record will store the ID of a record that will be a parent for this one. For the root record this parameter will be 0;
  • (optionally) - the open field for each record indicating whether this record will be initially opened (1) or not (0) - if it has children, of course.

A dataset is compiled with a function that contains an SQL query with a WHERE clause, which gets data from the root record (parent = 0) down to all its children. JSON data for a datatree should store children for an item in its data property:

function get($id = 0) {
    $db = new SQLite3('../../../common/testdata.sqlite');
    //connect to database
 
    //select data
    $res = $db->query("SELECT * FROM films_tree WHERE parent={$id}");
 
    //convert data to json
    $data = array();
    while ($rec = $res->fetchArray(SQLITE3_ASSOC)) {
        $rec["data"] = get($rec["id"]); //putting children into item "data" property
        $data[] = $rec;
    }
    //output json
    return $data;
}
 
// formatting output
echo json_encode(get());

Form Loading

On the client side, the load() method is applied to an already initialized form. The method contains a loading script that sometimes requires the ID of the record as a part of a GET request:

$$("form1").load("server/form.php?id=3");

Then on the server you'll have the ID to use in the WHERE clause of the SELECT query.

$id = $_GET["id"];
 
$res = $db->query("SELECT * FROM films WHERE id={$id}");

Data Saving

In essence, defining save initializes DataProcessor that gets data ready for sending to the server and triggers script execution each time changes are made. Default DataProcessor logic can be customized.

Scripts get changed data in the body of a POST request. For each inserted, updated or deleted record, all properties of its object are sent together with webix_operation

id  8
rank    99
rating  
title   New
votes   100
webix_operation delete
year    2012

Additional item properties can be set as well by updating this item on the client side:

var item = this.getItem(id);
item.old_rank = some; // setting new property and value for it
this.updateItem(id);

The new property old_rank will be passed in POST like native item properties.

Related sample:  Datatable: Reordering

You can easily get to these parameters to use them for making corresponding database queries.

$operation = $POST['webix_operation'];
 
if($operation=="insert")
    $db->query("INSERT SQL query");

Don't forget to escape user data to avoid SQL injections:

$title = $db->escapeString($_POST["title"]);

and convert the data to JSON according to the common pattern above.

Datatree Saving

With datatree use the same pattern as above, and don't forget to update the parent field with the parent item value in the INSERT and UPDATE queries.

It goes without saying that you should render this field during loading (SELECT * guarantees this for sure) and update it on the client side while working with tree data.

$parent = $_POST["parent"];
 
//queries

Related sample:  Tree: Saving

Form Saving

Form is typically saved on some page event (e.g. button clicking), so there's no need in initializing DataProcessor for it. As a result, a POST request will contain only form data and no webix_operation type.

You are to decide what should be done:

  • UPDATE data on the server side for the record that was previously loaded to this form;
  • INSERT data into the necessary database table (i.e. create a new record);
  • update data on the client side - in this case form should be bound with a component or Data Collection, so that form could get data from them and send changes back, while their save scripts handle these changes.

A typical Ajax request to save form data to the server looks like this:

webix.ajax().post("server/form_save.php", $$("myform").getValues(), function(response) {
    webix.message(response.status);
});

Form getValues() returns data from all fields as a JSON object - just what is required for parameters sent via Ajax.

For tuning responses, scroll down a little bit. For learning Webix API for Ajax operations - go there.

Tuning ServerSide Response

For each query you should tune a response, which can be either plain text or a JSON object with necessary parameters:

//plain text on failure
echo "Data saving was not performed";
 
//JSON with item ID and status for failure
echo '{ "id":"'.$id.'", "status":"error" }';
 
//JSON with item ID and status for success
echo '{ "id":"'.$id.'", "status":"success" }';
 
//JSON for successful inserting operation 
echo '{ "id":"'.$id.'", "status":"success", "newid":"'.$db->lastInsertRowID().'" }';
 
$res = $db->query(SQL query);
 
if($res)
    //response for success
else
    //response for failure

For INSERT operations a new ID is important as on the client side any newly added item gets a temporary ID, randomly generated by the webix.uid() method (something like 1369905287157).

After adding this item to a database table, it gets a normal ID generated by auto increment, and this ID will be loaded to the client side on page refresh. So you might require this new ID at once to work with the recently added item.

If you use an Ajax request, these responses are easily derived in a callback. If the response is a JSON object, any of its properties can be treated separately.

webix.ajax().post("server/save.php", {}, function(response) {
    webix.message(response.status);
});

More about Ajax operations in Webix.

Back to top