Reading Data

The DataTable class provides a read only interface to a database table, reading the data from the columns configured and returning it to the client-side for display in response to a DataTables ajax request. It also supports server-side processing for large data sets and advanced filtering options for ColumnControl and SearchBuilder.

Basic initialisation

In this documentation we'll assume a $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 DataTable instance using:

use DataTables\DataTable;

$table = DataTable::inst( $db, 'staff' );

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

Primary key name

It can often be useful to be able to uniquely identify rows in a DataTable (e.g. for row selection, or performing actions on the data). This can be done by telling the DataTable class what your table's primary key is with the third parameter (by default it will assume id is the column name):

$table = DataTable::inst( $db, 'staff', 'staffid' );

An array can also be given for the third parameter if your table has a compound key.

Columns

Once the DataTable instance has been created and configured for what database table it should read from, it needs to be instructed what columns it should read data from and send back to the client. This is done through the DataTable->columns() method which is passed Column class instances. Like the DataTable class these can be constructed using Column::inst() and in this case the parameter given is the name of the column to read data from, and also the name that will be used to return the data to DataTables and Editor.

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

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

DataTable::inst( $db, 'staff' )
    ->columns(
        Column::inst( 'first_name' ),
        Column::inst( 'last_name' ),
        Column::inst( 'position' ),
        Column::inst( 'email' ),
        Column::inst( 'office' )
    );

Column instances also provide the ability to operate on columns for filtering via ColumnControl and SearchBuilder as needed. 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 Column instance constructor will also be the name that the column is given in the JSON sent to the client, and the name of the column 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 Column constructor - e.g.:

Column::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.

Data processing

In the most simple case, where no additional data is required from the client-side to service the data request, you can simply call the process() method on the DataTable class. However, there might be times when extra data is submitted - e.g. for a server-side processing request. This information should be passed directly to the process() method, so it can handle it:

DataTable::inst( $db, 'staff' )
    ->columns(
        Column::inst( 'first_name' ),
        Column::inst( 'last_name' ),
        Column::inst( 'position' ),
        Column::inst( 'email' ),
        Column::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 DataTable class will set this up automatically for you. The json() method of the DataTable 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\DataTable,
    DataTables\DataTable\Column;

DataTable::inst( $db, 'staff' )
    ->columns(
        Column::inst( 'first_name' ),
        Column::inst( 'last_name' ),
        Column::inst( 'position' ),
        Column::inst( 'email' ),
        Column::inst( 'office' )
    )
    ->process( $_POST )
    ->json();

And that's it! This will now read data from the database and send it to the client-side for display.