?> Datatables child rows with ajax

Datatables child rows with ajax

This can be used to show additional information about a row, useful for cases where you wish to convey more information about a row than there is space for in the host table.

The example below makes use of the row().child methods to firstly check if a row is already displayed, and if so hide it (row().child.hide()), otherwise show it (row().child.show()).

Javascript to initialise data tables:

  var Table = $('#table').DataTable({
"responsive": true,
"scrollY": '85vh',
"scrollCollapse": true,
"scrollX": true,
"ajax": {
"url": "URL",
"type": "POST",
"data": function ( d ) {
d.name = dataToSend;
}
},
"columns": [
{
"className": 'details-control', // details for drop down selector column
"orderable": false,
"data": null,
"defaultContent": ''
},
{title: "Name", data: "Name"},
{title: "Age", data: "Age"},
{title: "Date of Birth", data: "DOB"},
{title: "Address", data: "Address"},
{title: "Email", data: "Email"}
]
}

Javascript child rows:

  $('#monthlytable tbody').on('click', 'td.details-control', function () {
var tr = $(this).closest('tr');
var row = Table.row( tr ); // variable name of the datatable
if ( row.child.isShown() ) {
row.child.hide();
tr.removeClass('shown');
}
else {
var result = '';
$.post('URL', { name: data, name: data }, function( success ) {
$.each(response.data, function (i, d) { // loop through data returned from ajax
result += '' + d.CarMake + '' + d.Model + '' + d.Year + '' + d.Colour + '';
});

row.child( $(result) ).show(); // use selector $() for result to align child rows with main table column headings
tr.addClass('shown');
}, 'json');
}
} );

The ajax request used here is POST, however GET can also be used. Three parameters are used within the ajax; URL, data to pass and the success function. You can pass data entered from the user and also data from the main data tables columns. Using the example above, name can be passed using row.data().Name

jQuery $.each method is used to loop through the results returned from the ajax request. The data is placed within its own table row to in order to match main table style

row.child( $(result) ).show(); is used to display the child rows. Note selector $() is being used in order to align child rows with main table column headings.

CSS:

  td.details-control {
background: url('../resources/details_open.png') no-repeat center center;
cursor: pointer;
}

tr.shown td.details-control {
background: url('../resources/details_close.png') no-repeat center center;
}

PHP to get main table data:

  $sql = "SELECT * FROM table";
$res = mysqli_query($conn, $sql);
$returnarray = array();
while($row = mysqli_fetch_assoc($res)) {
$rowarray = array(
"Name"=> $row['name'],
"Age"=> $row['age'],
"DOB"=> $row['dob'],
"Address"=> $row['address'],
"Email"=>$row['email']
);
$returnarray[] = $rowarray;
}
mysqli_free_result($res);
echo json_encode($returnarray);

PHP to get child rows data:

  if($_POST['data']){
$sql = "SELECT * FROM table";
$res = mysqli_query($conn, $sql);
$returnarray = array();
while($row = mysqli_fetch_assoc($res)) {
$rowarray = array(
"CarMake"=> $row['make'],
"Model"=> $row['model'],
"Year"=> $row['year'],
"Colour"=> $row['colour']
);
$returnarray[] = $rowarray;
}
mysqli_free_result($res);
echo json_encode($returnarray);
}

In the example above, MySQL is being used to retrieve data from a database for the main table as well as the child rows.
The code to be executed for child rows is placed within an isset. This is to check that the data has been passed so the code should be executed.

The full details about datatables can be found here

Top