I am using the "datatables" plugin in a server-side way, it works perfectly but if I add the "select inputs" it does not work for me, just that option. I got the code from the following link: API link
Here is the Ajax code:
var table = $('#table').DataTable({
"destroy": true,
"processing": true,
"serverSide": true,
"autoWidth": true,
"ajax": "tabla/usuarios",
"columns": [
{data: 0, searchable: false, orderable: false, render: function( data, type, full, meta ){
return meta.row+1;
}
},
{data: 1},
{data: 2},
{data: 10},
{data: 12},
{data: 5, searchable: false, render: function( data, type, full, meta ){
if (data) {
return '<input id="toggle" data-info="toggle'+full.id+'" name="my-checkbox" type="checkbox" checked data-toggle="toggle" data-on="Activo" data-off="Inactivo" data-onstyle="success" data-offstyle="danger">';
}else {
return '<input id="toggle" data-info="toggle'+full.id+'" name="my-checkbox" type="checkbox" data-toggle="toggle" data-on="Activo" data-off="Inactivo" data-onstyle="success" data-offstyle="danger">';
}
}
},
{data: 9, searchable: false, orderable: false},
/*
data:
0 - id
1 - name
2 - email
3 - cedula
4 - nombre
5 - status
6 - nombres
7 - apellidos
8 - id_perfiles
9 - botones de editar y eliminar
10 - boton info de cedula
11 - password
12 - boton info perfil
*/
],
order: [[1, 'asc']],
fnDrawCallback: function() {
$("[name='my-checkbox']").bootstrapToggle();
$('[data-toggle="popover"]').popover({
placement : 'top',
html : true
});
},
"language": {
"url": "js/idioma_espaniol_datatables.js"
},
initComplete: function () {
this.api().columns().every( function () {
var column = this;
var select = $('<select><option value=""></option></select>')
.appendTo( $(column.footer()).empty() )
.on( 'change', function () {
var val = $.fn.dataTable.util.escapeRegex(
$(this).val()
);
column
.search( val ? '^'+val+'$' : '', true, false )
.draw();
} );
column.data().unique().sort().each( function ( d, j ) {
select.append( '<option value="'+d+'">'+d+'</option>' )
} );
} );
}
});Route::get('tabla/usuarios', 'UsuariosController@tabla');
Here is the controller code:
public function tabla(){
try{
$sqls = User::join('personas','users.id_personas','=','personas.id')
->join('perfiles','users.id_perfiles','=','perfiles.id')
->select(array(
'users.id',
'users.name',
'users.email',
'personas.cedula',
'perfiles.nombre',
'users.status',
'users.password',
'personas.nombres',
'personas.apellidos',
'users.id_perfiles',
));
return Datatables::of($sqls)
->addColumn('botones', '<button type="button" class="editar edit-modal btn btn-warning "><span class="fa fa-edit"></span> Editar</button> <button type="button" class="eliminar delete-modal btn btn-danger"><span class="fa fa-trash"></span> Eliminar</button>')
->addColumn('cedula-popover', function ($sql) {
return '<button type="button" class="btn btn-default" data-toggle="popover" data-trigger="focus" title="Datos Personales" data-content="Nombres: '.$sql->nombres.' <br /> Apellidos: '.$sql->apellidos.'">'.$sql->cedula.'</button>';
})
->addColumn('password', function ($sql) {
return bcrypt($sql->password);
})
->addColumn('funciones', function ($sql) {
$result = \SIMante\Funciones::join('perfiles_funciones','perfiles_funciones.id_funciones','=','funciones.id')
->select(array('funciones.nombre'))
->where('perfiles_funciones.id_perfiles', '=', $sql->id_perfiles)->get();
$funciones = "";
for($i=0;$i<count($result);$i++){
$funciones = $funciones . " - " . $result[$i]->nombre . " <br /> ";
}
return '<button type="button" class="btn btn-default" data-toggle="popover" data-trigger="focus" title="Funciones" data-content="'.$funciones.' <br />">'.$sql->nombre.'</button>';
})
->make();
}catch(\Illuminate\Database\QueryException $e){
$array = array(
'mensaje' => $e->getMessage(),
'codigo' => $e->getCode(),
'sql' => $e->getSql(),
'bindings' => $e->getBindings(),
);
return Response::json(array('ErrorSql' => $array));
}
}When I select one of the options of the "selects" send me this:
{
"ErrorSql": {
"mensaje": "SQLSTATE[42601]: Syntax error: 7 ERROR: error de sintaxis en o cerca de «REGEXP»\nLINE 1: ...rfiles\" = \"perfiles\".\"id\" where LOWER(users.name) REGEXP $1)...\n ^ (SQL: select count(*) as aggregate from (select '1' as \"row_count\" from \"users\" inner join \"personas\" on \"users\".\"id_personas\" = \"personas\".\"id\" inner join \"perfiles\" on \"users\".\"id_perfiles\" = \"perfiles\".\"id\" where LOWER(users.name) REGEXP ^admin$) count_row_table)",
"codigo": "42601",
"sql": "select count(*) as aggregate from (select '1' as \"row_count\" from \"users\" inner join \"personas\" on \"users\".\"id_personas\" = \"personas\".\"id\" inner join \"perfiles\" on \"users\".\"id_perfiles\" = \"perfiles\".\"id\" where LOWER(users.name) REGEXP ?) count_row_table",
"bindings": [
"^admin$"
]
}
}He tells me it's a syntax error, but why?
The table works well for me, here's the demonstration:
But if I select one of the selects, it launches the following:
