Editing

The Editor class is one of the main access points for processing requests from the server-side. It can handle all requests for reading and writing data to a database for a DataTable. It is very similar to the DataTable class, providing a very similar interface style. The documentation on this page will partly refer to the DataTable documentation due to the overlap!

It is important to note that the Editor class provides all of the same features as the DataTable class for reading data (i.e. you do not need to use both for the same table!), but also adds support for writing data. There are four basic request types that DataTables and Editor can make:

• Fetch - Get the data to display
• Create - Create a new row of data
• Edit - Update an existing row with changed data
• Remove - Delete one or more rows from the database

The Editor class will automatically detect which of these requests are being made and handle it correctly for you without adjustment. The data resulting from the request is then sent back to the client for it to complete the processing.

Basic initialisation

As with the DataTable documentation here we'll assume $db variable is available with the Database connection, and that you've included the DataTable's PHP libraries in your script, per the installing documentation.

Construct a PHP Editor instance using:

use DataTables\Editor;

$editor = Editor::inst( $db, 'staff' );

The first parameter is the connection to the database, while the the second parameter is the database table name that the Editor instance will operate on.

Primary key name

Editor requires that the database table it is setup to edit have a primary key. By default it looks for a column called id. This can be altered using the optional third parameter for the Editor constructor - for example:

$editor = Editor::inst( $db, 'staff', 'staffid' );

Compound keys

As of Editor 1.6 the PHP libraries support compound keys: a key which is made up from the data in two or more table columns, rather than just a single column as used in many tables. To use a compound key, simply pass the primary key information into the third parameter for the Editor constructor as an array:

$editor = Editor::inst( $db, 'visitors', ['visitor_id', 'visit_date'] );

No special configuration is required on the client-side to support compound keys, however, when creating new rows you must submit the data for the columns that make up the compound key (an error will be shown otherwise). Editor cannot currently read information that is generated by the database. If you need to set a server-side computed value (e.g. current time), use the Field->setValue() method to set the value. This limitation is due to the database engines that Editor currently supports.

Fields

Once the Editor instance has been created and configured for what database table it should read from, it needs to be instructed what fields it should access on that table. This is done through the Editor->fields() method which is passed Field class instances. Like the Editor class these are constructed using Field::inst() and the parameter given is the name of the column to read / write data, and also the name that will be used to return the data to DataTables and Editor.

The fields() method can take as many field instances as you wish to define and can also be called multiple times to add additional fields.

In the example below the Editor instance is configured with five simple fields.

Editor::inst( $db, 'staff' )
    ->fields(
        Field::inst( 'first_name' ),
        Field::inst( 'last_name' ),
        Field::inst( 'position' ),
        Field::inst( 'email' ),
        Field::inst( 'office' )
    );

Field instances provide validation and formatting options as well as simple get / set options. Please refer to the documentation for those sections for detailed information.

Parameter names

By default the column name given as the first parameter to the Field instance constructor will also be the name that the field is given in the JSON sent to the client, and the name of the field that the libraries will look for in the data submitted to the server from the Editor form. This name (JSON and submitted data) can be modified using an optional second parameter for the Field constructor - e.g.:

Field::inst( 'first_name', 'fname' )

This can be useful if you wish to obfuscate the SQL columns names so the client will never see the actual database naming, or simply want to reduce the size of the JSON object transmitted to the client.

SQL functions

While you will usually wish to just obtain data from SQL columns, as shown above, it can also be useful to use SQL functions for certain operations. The Field instance can be specified with an SQL function - this is found by Editor simply by the use of parenthesis. In this case you will likely wish to use the optional second parameter to name the data in the JSON sent to the client.

Important Fields which make use of an SQL function can only be read. They cannot be written to. If an attempt is made to write to such a field, an error will be thrown. Use Field->set( false ) to ensure that the field is not written to.

In the following example, the MySQL function timestampdiff is used (note that in other SQL dialects, functions can also be used). The method is used to calculate the difference between a field (start_date) and now, in days, then naming the field as ago in the JSON sent to the server. Note also that the Field->set() method is used to ensure that the field is not written to:

Field::inst( 'timestampdiff(DAY, now(), start_date)', 'ago' )
    ->set( false )

Data processing

When DataTables and Editor make a request to the server to get or update data, they send the data using HTTP parameters (POST is the default for Editor). PHP provides this information in its global $_POST (or $_GET if you are using GET) variable which can be given to the Editor instance for processing.

The data is processed using the process() method of the Editor class, with the data sent from the client being passed into it. At this point the Editor instance will perform whatever action the client-side has asked of it - be it to create a new row, update an existing row, etc. These actions will be automatically detected.

To continue the example above, if we add a process() method call we now have:

Editor::inst( $db, 'staff' )
    ->fields(
        Field::inst( 'first_name' ),
        Field::inst( 'last_name' ),
        Field::inst( 'position' ),
        Field::inst( 'email' ),
        Field::inst( 'office' )
    )
    ->process( $_POST );

Return data

The final step is to send the data back to the client for it to process (e.g. display an error message if validation failed or redraw the table with the updated data if everything was successful). The data sent back to the client is in the JSON data format and again the Editor class will set this up automatically for you. The json() method of the Editor class will automatically echo out JSON data for the client-side to read. A data() method is also available if you want to access the data before it is sent back to the client-side, useful if you need to send extra data (the json() method is basically echo json_encode( $this->data() );).

Now the complete example is:

// Alias classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;

$editor = Editor::inst( $db, 'staff' )
    ->fields(
        Field::inst( 'first_name' ),
        Field::inst( 'last_name' ),
        Field::inst( 'position' ),
        Field::inst( 'email' ),
        Field::inst( 'office' )
    )
    ->process( $_POST )
    ->json();

And that's it! That is all that is required on the server-side, when using the DataTables PHP libraries to add read / write ability to your database table. Additional fields can be trivially added and other tables also setup for editing.