I have a datatable for users as serverside that the search doesn't work due to a database error. The rest works perfectly but when using search i get this error in an alert:
DataTables warning: table id=users - An SQL error occurred: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens
I though it was due to the first column having mixed data from different columns, but seems that is not that case as adding the following line to js did not work:
{ "searchable": false, "targets": [0] },
This is the code for the datatable:
<table
class="table align-middle table-striped table-row-dashed fs-6 gy-5" id="users">
<thead class="thead-light">
<tr class="text-start text-muted fw-bolder fs-7 text-uppercase gs-0">
<th>Name</th>
<th>Username</th>
<th>Rol</th>
<th>Joined date</th>
<th>Status</th>
<th>Actions</th>
</tr>
</thead>
<tbody id="tBody" class="fw-bold text-gray-600"></tbody>
</tbody>
</table>
Js
$(document).ready(function () {
$('#users').DataTable({
processing: true,
language: {
emptyTable: 'There are 0 users',
processing:
'<i class="fa fa-spinner fa-spin fa-3x fa-fw"></i><span class="sr-only">Loading...</span> ',
},
dom:
"<'row'<'col-md-4'B><'col-md-4'f><'col-md-4'p>>" +
"<'row'<'col-md-6'><'col-md-6'>>" +
"<'row'<'col-md-12't>><'row'<'col-md-4'l><'col-md-4'i><'col-md-4'p>>",
buttons: [
{
extend: 'collection',
text: '<i class="la la-download"></i> Export',
autoClose: true,
className:
'btn btn-primary btn-icon-sm btn-square dropdown-toggle',
buttons: [
{
extend: 'copyHtml5',
text: '<i class="fas fa-copy"></i>\xa0\xa0 Copy',
exportOptions: {
columns: [9, 6, 1, 7, 2],
},
},
{
extend: 'excelHtml5',
text: '<i class="fas fa-file-excel"></i>\xa0\xa0 Excel',
exportOptions: {
columns: [9, 6, 1, 7, 2],
},
},
{
extend: 'csvHtml5',
text: '<i class="fas fa-file-csv"></i>\xa0\xa0 CSV',
exportOptions: {
columns: [9, 6, 1, 7, 2],
},
},
{
extend: 'pdfHtml5',
text: '<i class="fas fa-file-pdf"></i>\xa0\xa0 PDF',
exportOptions: {
columns: [9, 6, 1, 7, 2],
},
},
{
extend: 'print',
text: '<i class="fas fa-print"></i>\xa0\xa0 Print',
exportOptions: {
columns: [9, 6, 1, 7, 2],
},
},
'colvis',
],
fade: true,
},
],
columnDefs: [
{
render: function (data, type, row) {
if (row[8] == null) {
var photo =
'<span class=" symbol-label fs-3 bg-light-success text-success"><span>' +
row[0].charAt(0) +
row[6].charAt(0) +
'</span></span>';
} else {
var photo =
'<img src="' +
row[8] +
'" alt="Francis Mitcham" class="w-100">';
}
return (
'<div class="symbol symbol-circle symbol-50px overflow-hidden me-3" style="margin-left:15px;"><a href="./users/user/' +
row[5] +
'/' +
row[1].toLowerCase() +
'"><div class="symbol-label">' +
photo +
'</div></a></div><div class="d-flex flex-column"><a href="./users/user/' +
row[5] +
'/' +
row[1].toLowerCase() +
'" class="text-gray-800 text-hover-primary mb-1">' +
row[0] +
' ' +
row[6] +
'</a><span class="text-primary"><a href="mailto:' +
row[7] +
'" class=" text-primary mb-1">' +
row[7] +
'</a></span></div>'
);
},
targets: 0,
},
{
render: function (data, type, row) {
return (
'<span class="badge badge-light-info">' +
row[2] +
'</span>'
);
},
targets: 2,
},
{
render: function (data, type, row) {
if (row[4] == 1) {
return '<span class="badge badge-light-success">Active</span>';
} else {
return '<span class="badge badge-light-danger">Inactive</span>';
}
},
targets: 4,
},
{
render: function (data, type, row) {
return (
'<a href="./users/user/' +
row[5] +
'/' +
row[1].toLowerCase() +
'" class="btn btn-light-primary btn-sm btn-icon" title="View User"> <i class="fas fa-eye"></i></a> <button class="btn btn-light-warning btn-sm btn-icon btn-user-edit" title="Edit User" data-placement="bottom" title="Edit User" data-id="' +
row[5] +
'"><i class="fas fa-edit"></i></button> <button class="btn btn-light-danger btn-sm btn-icon btn-user-del" title="Delete User" data-placement="bottom" title="Delete User" data-id="' +
row[5] +
'"><i class="fa-solid fa-trash-can"></i></button>'
);
},
targets: 5,
},
{ orderable: true, targets: 0 },
{ className: 'd-flex', targets: [0] },
{ visible: false, targets: [6] },
{ visible: false, targets: [9] },
{ visible: false, targets: [7] },
{ searchable: false, targets: [0] },
{ visible: false, targets: [8] },
],
pageLength: 10,
responsive: true,
processing: true,
serverSide: true,
ajax: './index.php?module=users&action=users-get',
});
});
users-get.php
<?php
$table = 'users_users';
$primaryKey = 'id';
$columns = array(
array(
'db' => 'name',
'dt' => 0,
'formatter' => function( $d, $row ) {
return $d;
}
),
array(
'db' => 'user_name',
'dt' => 1,
'formatter' => function( $d, $row ) {
return $d;
}
),
array(
'db' => 'rol',
'dt' => 2,
'formatter' => function( $d, $row ) {
$d = classUsers::getRolByID($d);
$d = $d->fields['name'];
return $d;
}
),
array(
'db' => 'created_at',
'dt' => 3,
'formatter' => function( $d, $row ) {
return $d;
}
),
array(
'db' => 'is_active',
'dt' => 4,
'formatter' => function( $d, $row ) {
return $d;
}
),
array(
'db' => 'id',
'dt' => 5,
'formatter' => function( $d, $row ) {
return $d;
}
),
array(
'db' => 'last_name',
'dt' => 6,
'formatter' => function( $d, $row ) {
return $d;
}
),
array(
'db' => 'email',
'dt' => 7,
'formatter' => function( $d, $row ) {
return $d;
}
),
array(
'db' => 'profile_pic',
'dt' => 8,
'formatter' => function( $d, $row ) {
return $d;
}
),
array(
'db' => 'name',
'dt' => 9,
'formatter' => function( $d, $row ) {
return $d;
}
)
);
require( 'model/class/ssp.class.php' );
echo json_encode(
SSP::complex( $_GET, $table, $primaryKey, $columns, $whereResult=null, $whereAll="hidden = 0 " )
);
?>
