Permanent inline checkboxes
The primary interaction methods used with Editor is its main lightbox input form, the bubble editor or inline editing, but it is also quite possible to construct your own form controls that make use of Editor through its extensive API.
I have recently been asked if it is possible to have checkboxes permanently shown in a table that would show the status of a boolean field and will update that field when toggled with a single click. Using Editor's inline editing option a checkbox can be inserted into the table with a single click and calling the inline() method, but another click would be needed to change the value and submit the form. Fast, but when you have data where boolean state toggling is common a single click update can improve the interaction of your app.
In this post I show how Editor's API can be used to trigger data updates thorugh the constructions of a table that has a column of checkboxes and will immediately write to the database when toggled. Furthermore, the full row (including the boolean status) will remain editable through the standard Editor editing options.
If you are keen to jump to the end and see the finished product, the code developed in this post can be see running live on the Editor examples site.
Starting point
For the applied example created in this post, I'll use one of the sample database tables that the Editor package comes with as it is ideal for this use case. Specifically the users table has a binary active field which we can use for our list of checkboxes.
As the starting point for this post, consider the following Editor and DataTables initialisation (note if you are new to DataTables and Editor, their respective manuals, DataTables / Editor, both have extensive details about how to set up and configure the two libraries):
Javascript
var editor = new $.fn.dataTable.Editor( {
"ajax": "../php/checkbox.php",
"table": "#example",
"fields": [
{
label: "Active:",
name: "active",
type: "checkbox",
separator: "|",
ipOpts: [
{ label: '', value: 1 }
]
},
{ label: "First name:", name: "first_name" },
{ label: "Last name:", name: "last_name" },
{ label: "Phone:", name: "phone" },
{ label: "City:", name: "city" },
{ label: "Zip:", name: "zip" }
]
} );
$('#example').dataTable( {
dom: "Tfrtip",
ajax: "../php/checkbox.php",
columns: [
{ data: "first_name" },
{ data: "last_name" },
{ data: "phone" },
{ data: "city" },
{ data: "zip" },
{ data: "active" }
],
tableTools: {
sRowSelect: "os",
aButtons: [
{ sExtends: "editor_create", editor: editor },
{ sExtends: "editor_edit", editor: editor },
{ sExtends: "editor_remove", editor: editor }
]
}
} );
PHP
include( "DataTables.php" );
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Join,
DataTables\Editor\Validate;
Editor::inst( $db, 'users' )
->fields(
Field::inst( 'first_name' ),
Field::inst( 'last_name' ),
Field::inst( 'phone' ),
Field::inst( 'city' ),
Field::inst( 'zip' ),
Field::inst( 'active' )
->setFormatter( function ( $val, $data, $opts ) {
return ! $val ? 0 : 1;
} )
)
->process( $_POST )
->json();
You'll be able to note from the above code that we have six columns in the table and each field is editable in Editor. The active field is a checkbox type and we use the separator option to have Editor submit the data as a string, rather than as an array as it would by default (useful for cases where there are multiple checkboxes).
In the PHP the only one special consideration is for the checkbox when it is unchecked. Browsers will not submit a value when the checkbox is not checked, so we use a setFormatter to ensure that either 0 or 1 is used as the value the database sees (see Editor PHP manual).
The above code will run successfully and our table is fully editable, but let us now look at adding the permanent checkboxes to the table.
Displaying the input checkbox
Without any data renderers (as is the case above) the information read from the database is written directly into the table and thus is what the user will see. In the case of the active column this would be a 0 or 1 but we want to display a checkbox. To achieve that we can use DataTables' column rendering option: columns.render. Specifically we want to check for the display data type and return an HTML checkbox. For other data types (sort for example) we return the original data (see orthogonal data for more information). This ensures that the column is still sortable and searchable.
{
data: "active",
render: function ( data, type, row ) {
if ( type === 'display' ) {
return '<input type="checkbox" class="editor-active">';
}
return data;
},
className: "dt-body-center"
}
Note that the columns.className option is also used to specify the dt-body-center class, which is built into the DataTables default stylesheet, to center align the checkbox in the column - for more on the built in style options, please refer to the styling manual.
Next, we need to set the checked state of the checkbox. In order to ensure that this is always up-to-date with the source data for the row (for example if the state is altered in the Editor lightbox form) the rowCallback option is used. This is a callback function that is run for every row that is displayed by DataTables, so we can use a trivial line of jQuery to set the checked property:
rowCallback: function ( row, data ) {
// Set the checked state of the checkbox in the table
$('input.editor-active', row).prop( 'checked', data.active == 1 );
}
Finally for the table display we need to recognise that the TableTools row selector by default will select a row if any part of the row, including child elements such as the checkbox, is clicked on. Checking the checkbox with a click is a desirable action, but we don't want the row to be selected as a result. While it would be possible to use stopPropagation() with event listeners, TableTools offers an easier method - the sRowSelector option.
This property provides the ability to tell TableTools what selector to use for the row selection. In this case we want to use all of the row, except the last column. For this we can use a simple CSS selector (note that this is added to the tableTools initialisation object):
sRowSelector: 'td:not(:last-child)' // no row selection on last column
API update
With the checkbox shown, the next and final step in this process is to perform an Editor action when the state is changed. For this we use a jQuery change event handler to call the edit() method to start an edit for the row. Using the second parameter of edit() we can tell the form to not display (as it would by default).
Then the set() method is used to the value of the active state based on the checkbox that was toggles and finally the submit() method is used to submit the data to the server.
$('#example').on( 'change', 'input.editor-active', function () {
editor
.edit( $(this).closest('tr'), false )
.set( 'active', $(this).prop( 'checked' ) ? 1 : 0 )
.submit();
} );
And that is it!
Running example
This example is available on the Editor web-site where you will be able to use the code developed in this post and de-construct it further if you are interested.
Complete code
For completeness, the code discussed above is merged into our starting point from the top of this post to show the fully assembled and complete working code:
var editor = new $.fn.dataTable.Editor( {
"ajax": "../php/checkbox.php",
"table": "#example",
"fields": [ {
label: "Active:",
name: "active",
type: "checkbox",
separator: "|",
ipOpts: [
{ label: '', value: 1 }
]
},
{ label: "First name:", name: "first_name" },
{ label: "Last name:", name: "last_name" },
{ label: "Phone:", name: "phone" },
{ label: "City:", name: "city" },
{ label: "Zip:", name: "zip" }
]
} );
$('#example').dataTable( {
dom: "Tfrtip",
ajax: "../php/checkbox.php",
columns: [
{ data: "first_name" },
{ data: "last_name" },
{ data: "phone" },
{ data: "city" },
{ data: "zip" },
{
data: "active",
render: function ( data, type, row ) {
if ( type === 'display' ) {
return '<input type="checkbox" class="editor-active">';
}
return data;
},
className: "dt-body-center"
}
],
tableTools: {
sRowSelect: "os",
aButtons: [
{ sExtends: "editor_create", editor: editor },
{ sExtends: "editor_edit", editor: editor },
{ sExtends: "editor_remove", editor: editor }
],
sRowSelector: 'td:not(:last-child)' // no row selection on last column
},
rowCallback: function ( row, data ) {
// Set the checked state of the checkbox in the table
$('input.editor-active', row).prop( 'checked', data.active == 1 );
}
} );
$('#example').on( 'change', 'input.editor-active', function () {
editor
.edit( $(this).closest('tr'), false )
.set( 'active', $(this).prop( 'checked' ) ? 1 : 0 )
.submit();
} );