Datatables won't filter a table populated with PHP
Datatables won't filter a table populated with PHP
matsrom
Posts: 1Questions: 1Answers: 0
Hi everyone!
I am populating a table with php making a sql query and then a foreach loop in the html code echoeing the column values. Its a weird table because I'm able to edit it directly and values are saved back to the server with AJAX.
I followed this example to implement filtering but when I write anything in any field, I have no reuslts. If I switch my table to a fully html table, it works just fine.
The table:
<table id="tblTrabajos" class="table table-bordered table-striped" style="width: 2000px;">
<thead>
<tr>
<th>Acciones</th>
<th>Código</th>
<th>Empresa</th>
<th>Actividad</th>
<th>Categoria</th>
<th>Revisor</th>
<th>Fecha auditoría</th>
<th>Alerta auditoría</th>
<th>Auditor</th>
<th>Calificación</th>
<th>Última modificación</th>
</tr>
</thead>
<tbody>
<?php foreach($trabajos as $fila): ?>
<tr>
<td class="acciones">
<a id="editar<?php echo $fila->id ?>" class="btn btn-info edit" onclick="editar(this)"><i class="far fa-edit"></i></a>
<a id="guardar<?php echo $fila->id ?>" class="btn btn-success save disabled" onclick="guardar(this)"><i class="far fa-save"></i></a>
<a id="borrar<?php echo $fila->id ?>" class="btn btn-danger delete" onclick="borrar(this)"><i class="far fa-trash-alt"></i></a>
</td>
<td><input type="text" class="form-control readOnlyInput sece <?php echo $fila->id ?>" name="codigo" value="<?php echo $fila->codigo ?>" readonly></td>
<td><input type="text" class="form-control readOnlyInput sece <?php echo $fila->id ?>" name="empresa" placeholder="Introduce la empresa" value="<?php echo $fila->empresa ?>" readonly></td>
<td><input type="text" class="form-control readOnlyInput sece <?php echo $fila->id ?>" name="actividad" placeholder="Introduce la actividad" value="<?php echo $fila->actividad ?>" readonly></td>
<td><input type="text" class="form-control readOnlyInput sece <?php echo $fila->id ?>" name="categoria" placeholder="Introduce la categoría" value="<?php echo $fila->categoria ?>" readonly></td>
<td>
<select name="revisor" class="custom-select <?php echo $fila->id ?>" disabled>
<option value="">--Seleccionar revisor--</option>
<?php foreach($revisores as $revisor): ?>
<option value="<?php echo $revisor->id ?>" <?php if($revisor->id == $fila->revisor){echo "selected";} ?>><?php echo $revisor->nombre . " " . $revisor->apellidos?></option>
<?php endforeach; ?>
</select>
</td>
<td><input type="text" class="form-control readOnlyInput sece <?php echo $fila->id ?>" name="fecha_audit" placeholder="Fecha de auditoría" value="<?php if($fila->fecha_audit != "0000-00-00"){echo date("d/m/Y", strtotime($fila->fecha_audit));} ?>" readonly></td>
<td class="alerta-auditoria"><input id="al-aud-<?php echo $fila->id ?>" type="text" class="form-control readOnlyInput alertaAuditoria <?php echo $fila->id ?>" name="fecha_audit" placeholder="Días hasta auditoría" value="<?php echo $fila->dias_hasta_audit ?>" readonly></td>
<td>
<select name="auditor" class="custom-select <?php echo $fila->id ?>" disabled>
<option value="">--Seleccionar auditor--</option>
<?php foreach($auditores as $auditor): ?>
<option value="<?php echo $auditor->id ?>" <?php if($auditor->id == $fila->auditor){echo "selected";} ?>><?php echo $auditor->nombre . " " . $auditor->apellidos?></option>
<?php endforeach; ?>
</select>
</td>
<td><input type="text" class="form-control readOnlyInput sece <?php echo $fila->id ?>" name="calificacion" placeholder="Introduce la calificaciónd" value="<?php echo $fila->calificacion ?>" readonly></td>
<td><input type="text" class="form-control readOnlyInput" name="usuario_ult_modificacion" value="<?php echo $fila->fecha_ult_modificacion ?>" readonly></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
The datatables script:
<script>
$(document).ready(function() {
// Setup - add a text input to each footer cell
$('#tblTrabajos thead tr').clone(true).addClass('filters').appendTo( '#tblTrabajos thead' );
var table = $('#tblTrabajos').DataTable( {
orderCellsTop: true,
fixedHeader: true,
initComplete: function() {
var api = this.api();
// For each column
api.columns().eq(0).each(function(colIdx) {
// Set the header cell to contain the input element
var cell = $('.filters th').eq($(api.column(colIdx).header()).index());
var title = $(cell).text();
$(cell).html( '<input type="text" placeholder="'+title+'" />' );
// On every keypress in this input
$('input', $('.filters th').eq($(api.column(colIdx).header()).index()) )
.off('keyup change')
.on('keyup change', function (e) {
e.stopPropagation();
// Get the search value
$(this).attr('title', $(this).val());
var regexr = '({search})'; //$(this).parents('th').find('select').val();
var cursorPosition = this.selectionStart;
// Search the column for that value
api
.column(colIdx)
.search((this.value != "") ? regexr.replace('{search}', '((('+this.value+')))') : "", this.value != "", this.value == "")
.draw();
$(this).focus()[0].setSelectionRange(cursorPosition, cursorPosition);
});
});
}
} );
} );
</script>
Also, how can I exclude some columns? The first column is for some buttons that allow editing the row and I don't want to have the filter option there. Neither in the last column (las modification date).
Thank you!
Answers
Sounds like you are populating your table without using the API. Then data tables won't know your table is populated at all. Hence the result of filtering is that nothing is shown. Nothing filtered out of nothing, I guess.
Check the docs please on how to populate your data table using the api. I only use data tables with Editor, hence I cannot share examples with you.
But I would check this, for example:
https://datatables.net/forums/discussion/49228
rows.add() or row.add() are probably what you want to use.
Making columns searchable or not searchable:
https://datatables.net/forums/discussion/comment/155254/#Comment_155254