Getting started

Editor comes with a set of pre-built PHP (5.3+) libraries which are designed to make it super easy to create a script on the server-side that will provide an anchor point for Editor's client-side scripts. Client / server communication is required in order to provide the initial data for the DataTable and then for Editor to instruct the server to create, edit or delete rows as required by the end user. The PHP libraries can do this for you in just a few lines.

Installation

The Editor PHP libraries are found in the lib directory of the Editor download package. If you have gone through the installation documentation to get the Editor examples up and running on your server, you have already configured and used the libraries.

The only file that needs to be configured in the library files is config.php. This file contains information that will instruct the libraries how to connect to your database and where the database is.

Either edit the existing file, or create a new file with the following information, simply filling in the six parameters of the $sql_details array:

<?php
 
error_reporting(E_ALL);
ini_set('display_errors', '1');
 
 
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * Database user / pass
 */
$sql_details = array(
    "type" => "",    // Database type: "Mysql", "Postgres", "Sqlite" or "Sqlserver"
    "user" => "",    // User name
    "pass" => "",    // Password
    "host" => "",    // Database server
    "port" => "",    // Database port (can be left empty for default)
    "db"   => "",    // Database name
    "dsn"  => ""     // PHP DSN extra information. Set as `charset=utf8` if you are using MySQL
);

Note that on lines 3 and 4 we enable error reporting. This can be very useful for debugging problems when getting started with Editor, but you may wish to remove these two lines before deploying your web-site.

The dsn option on line 17 provides the ability to provide additional information to the PHP PDO driver if you need to. Each PDO driver has its own set of parameters that effect how the client / server database connection will operate (please refer to the PHP PDO driver documentation for full details). In the case of MySQL, it is quite common to add charset=utf8 in the DSN to ensure that UTF8 is used for the connection.

Concept

It is important to understand the big picture of the approach the Editor PHP libraries take for interfacing between the database and the client-side DataTable / Editor before delving into the detail of how to actually read and write data.

The PHP libraries provide an Editor class, an instance of which is created and will relate, primarily, to a single SQL table which it will read and write data from and to.

The Editor instance is then instructed about the database table columns that it should operate on and how. These columns are used to populate both the DataTable and Editor, and Editor has the ability to write to them. Fields from other tables can also be included through the use of join statements.

Finally the data being sent by the client side is processed. 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 four 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

With the database connection configuration defined in the config.php file, we can now start using the libraries. We do this by including the lib/DataTables.php file. For example (note the use of the use namespace construct - namespaces are explained at the end of this page if you are not familiar with them):

include( "../../lib/DataTables.php" );

use DataTables\Editor;

We can now construct a PHP Editor instance using:

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

The $db variable was automatically defined as an instance of the Database() class which was used along with the $sql_details variable to establish a connection with the database automatically. The $db variable is defined in the Bootstrap.php library file and the variable name can be changed there if you require.

The second parameter passed into the instance constructor is the database table name that the Editor instance will operate on.

Note that in the above code a factory function (inst()) is used to construct a class instance in the above code - this is exactly the same as using:

$editor = new Editor( $db, 'staff' );

The reason you will find the former format in the examples is that PHP 5.3 didn't allow chaining from a newly created class instance. This issue was resolved in PHP 5.4 and if you are using the newer versions, you can use either format you wish - the inst static function (short for instance) simply provides chaining support (see below).

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', array('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:

// DataTables PHP library
include( "lib/DataTables.php" );

// Alias Editor 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 Editor PHP libraries to add read / write ability to your database table. Additional fields can be trivially added and other tables also setup for editing.

The next step is to add validation to your fields to ensure that the data sent is the data you expect.

PHP paradigms

There a couple of key programming concepts that the Editor PHP libraries make use of and it is important to understand them in this context. If you are already comfortable with the ideas of chaining and namespaces in PHP skip this section, otherwise read on for a summary of these PHP features, as they are used above and also in the Editor examples.

Chaining

Like the Editor Javascript API the PHP Editor API is fully chainable, allowing potentially complex code to be expressed succinctly. The majority of the methods that Editor and its child classes provide are chainable - i.e. they return the instance that you are working with. Consider for example:

$editor = Editor::inst( $db, 'staff' );
$editor->fields( ... );
$editor->process( ... );
$editor->json();

Using chained style, the above code block can be rewritten as:

Editor::inst( $db, 'staff' )
    ->fields( ... )
    ->process( ... )
    ->json();

Using a chained style of coding is not required at all, although you will see it used in this documentation and the examples Editor comes with.

Namespaces

The Editor PHP libraries make use of PHP namespaces. Namespaces are used in PHP to package software and reduce the chance of a name collision, for example, Editor is a relatively common name and other software that you include in your application might also define an Editor class. Namespacing resolves this issue.

You can use the namespaces directly:

$editor = new \DataTables\Editor( $db, 'staff' );

but obviously this involves extra typing, particularly when you have a lot of fields which also need to have their namespace specified. In PHP it is possible to shorten the code to just the class name by using PHP's use keyword.

In the examples you will typically see:

// Alias Editor 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;

This means that rather than typing DataTables\Editor or DataTables\Editor\Field every time we want an Editor or Field instance, we can just use Editor or Field respectively. So the above simple example becomes:

// DataTables PHP library
include( "lib/DataTables.php" );

// Alias Editor 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' );