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.
The Editor PHP libraries are found in the
php 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
<?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.
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.
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, a instance of which is created and will relate, primarily, to a single SQL table which it will read and write data from and to.
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
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.
With the database connection configuration defined in the
config.php file, we can now start using the libraries. We do this by including the
php/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( "../../php/DataTables.php" ); use DataTables\Editor;
We can now construct a PHP Editor instance using:
$editor = Editor::inst( $db, 'staff' );
$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).
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' );
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.
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.
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' ) );
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.
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 )
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
$_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 );
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( "php/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.
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.
$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.
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' );
that that is obviously extra typing, particularly when you have a lot of fields which also need to have their namespace specified. In PHP it is also possible to shorten the code to just the class name by using PHP's
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\Field every time we want an Editor or Field instance, we can just use
Field respectively. So the above simple example becomes:
// DataTables PHP library include( "php/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' );