I am pulling data via AJAX / PHP into a table and want to display the data via the DataTable JS plugin. The data query works well, but now I would like to add one more column for each record containing several buttons that would allow the user to preview / edit / delete the respective record.
I seem to be unable to append the additional column data containing the html code for the buttons to data table array, respectively DataTable is throwing an error message saying that the received json data is not properly formatted. This must be referring to the html code of the buttons as it works fine when pulling data without the extra column.
Below my code:
Mysqli query:
$result = $db->query($sql) or die(mysqli_error($db));
if($result->num_rows > 0) {
// return total number of rows for pagination
$totalData = $result->num_rows;
$totalFiltered = $totalData;
// return table data - MUST BE NON-ASSOCIATIVE ARRAY
while($row = mysqli_fetch_array($result)) {
$data[] = array(
$row['ID'],
$row['Holidex'],
$row['First'],
$row['Last'],
$row['Email'],
"Button" => "<button type=\"button\" class=\"btn btn-primary btn-xs\" data-toggle=\"modal\" data-target=\"#EditEmailModal\" data-keyboard=\"true\" data-id=\"".$row['ID']."\"><i class=\"fa fa-edit\"></i></button> <button type=\"button\" class=\"btn btn-danger btn-xs\" data-toggle=\"modal\" data-target=\"#EditEmailModal\" data-keyboard=\"true\" data-id=\"".$row['ID']."\"><i class=\"fa fa-times\"></i></button>",
);
}
// finalize array with elements required by DataTable plugin
$json_data = array(
"recordsTotal" => intval( $totalData ), // total number of records
"recordsFiltered" => intval( $totalFiltered ), // total number of records after searching, if there is no searching then totalFiltered = totalData
"success" => true, // must have a success message (false / true)
"aaData" => $data // table data as array
);
echo json_encode($json_data);
}
I tried replacing the double quotes " with single quotes ' and to remove the escape backslashes \ without success. What else can I do?
Thanks, A2k
UPDATE:
updated my JS accordingly to have buttons generated after the server-side procedure is done, but I am unable to attach the data.ID value to the button data-* element. I tried using both data and aaData when refering to the ID column, how come its not picking up the value?
UPDATE2: Edited the JS code to reflect latest changes
<script type="text/javascript" language="javascript" class="init">
$(document).ready(function() {
$('#auto_cc_table').DataTable( {
// load table data via AJAX
"processing": true,
"serverSide": true,
"ajax":{
url: "../../plugins/MySQL/ajax_action.php", // json datasource
data: { action:"view_auto_cc_email_AJAX" },
type: "post", // connection method (default: GET)
},
"columns": [
{ "aaData": "ID" },
{ "aaData": "Holidex" },
{ "aaData": "First" },
{ "aaData": "Last" },
{ "aaData": "Email" },
{ "aaData": null },
],
columnDefs: [{
className: "text-right", "targets": [1],
className: "text-nowrap", "targets": [3],
// puts a button in the last column
targets: [-1], render: function (a, b, data, d) {
//console.log(data); // error - undefinded in console
console.log(row); // ok
console.log(row.ID); // error - undefined in console
//console.log(row.Holidex); // error - undefined in console
//console.log(row.First); // error - undefined in console
//console.log(row.Last); // error - undefined in console
//console.log(row.Email); // error - undefined in console
return '<button type="button" class="btn btn-primary btn-xs" data-toggle="modal" data-target="#EditEmailModal" data-keyboard="true" data-id="'+ data.ID +'"><i class="fa fa-edit"></i></button> '
+'<button type="button" class="btn btn-danger btn-xs" data-toggle="modal" data-target="#EditEmailModal" data-keyboard="true" data-id="'+ data.ID +'"><i class="fa fa-times"></i></button>'
},
}],
dom: 'Bfrtip',
stateSave: true,
buttons: [
'copyHtml5',
'excelHtml5',
'csvHtml5',
'pdfHtml5',
{
extend: 'print',
message: '(c) Copyright'
},
{
extend: 'collection',
text: 'Others',
buttons: [
{
text: 'Toggle button',
action: function ( e, dt, node, config ) {
dt.column( -6 ).visible( ! dt.column( -6 ).visible() );
}
},
'colvis',
'columnsToggle',
]
},
],
"pagingType": "full_numbers",
"pageLength": 25,
"lengthChange": true,
"searching": true,
"ordering": false,
"order": [[ 1, "asc" ], [ 2, "asc" ]],
"info": true,
"autoWidth": true
})
});
</script>
UPDATE 3:
Following the solution below, I updated the JS code above to narrow down the console log issues, learning that console.log(data) and console.log(row.ID) result in undefined in the JS console, while console.log(row) returns the full row data eg. 7,ADMIN,John, Doe,[email protected].
Since I cant get the ID of the specific row via row.ID, I am now playing around with .split() to obtain the first left comma separated value, but this is nothing more but a monkeypatch for something that shouldnt be in the first place...
Note: var rowData = $('#auto_cc_table').DataTable().rows(btnRow).data()[0]; does return the full row data as shown above.
Modal Call:
// DISPLAY EMAIL RECORD
$('#EditEmailModal').on('show.bs.modal', function (event) {
var button = $(event.relatedTarget);
var btnRow = button.closest("tr");
var rowData = $('#auto_cc_table').DataTable().rows(btnRow).data()[0];
var rowData1 = rowData + '';
var rowData2 = rowData1.split(",");
alert("RowData: " + rowData2);
});
