Buscar en columnas DataTables. Esta herramienta es casi imprescindible para gestionar la información de base de datos. Por lo tanto, nos ayuda a optimizar las líneas de código en un CRUD en PHP, Lavarel u otro framework.
¿Qué es DataTables?
DataTables es un plugin para jQuery que nos brinda muchas posibilidades de agregar en las tablas HTML5 de tu página Web un aspecto versátil, incorporando las funciones para buscar, ordenar y paginar los resultados de forma rápida y concisa. Además, funciona usando base de datos.
Algunas ventajas
- Alcance e interfaz de usuario receptiva
- Búsqueda y clasificación incorporadas del lado del cliente.
- Carga diferida de datos Ajax
- Paginación (lado cliente / servidor) mediante solicitud Ajax / HTML.
- Admite datos JSON, lo que hace que la página HTML se procese más rápido.
- Leer datos de tablas de objetos
- Mostrar y ocultar detalles sobre un registro
- Filas seleccionables por el usuario (varias filas)
- Canalización de datos (reduzca las llamadas Ajax para la paginación)
- Ayuda a reducir mucho trabajo manual con jQuery Debatable para la representación de datos tabulares.
- Acelere su trabajo de desarrollo (aumente la eficiencia del trabajo) y el código legible y reutilizable de la API de tabla de datos jQuery evita el trabajo repetido y menos mantenimiento.
Algunas desventajas:
- La documentación, los ejemplos de código no están disponibles excepto la documentación oficial del sitio web.
- No es compatible con algunas versiones del navegador.
- Requiere profesionales expertos en JavaScript / jQuery.
- Problema de desplazamiento observado y problema de búsqueda con datos grandes.
- Se encontró lento con datos grandes o voluminosos (es decir, registros de más de 100000)
Además, puede cargar los datos de la tabla a través de JSON, lo que hace que la página HTML se procese más rápido. Otra gran característica de la tabla de datos es que admite la búsqueda y clasificación del lado del cliente. Paginación (lado cliente/servidor) mediante solicitud AJAX/HTML.
Buscar en columnas DataTables (Completo)
Usando este plugin podemos enumerar los resultados estáticos y dinámicos. En un artículo anterior, hemos visto cómo mostrar contenidos dinámicos con DataTables utilizando su procesamiento del lado del servidor.
Para continuar, en este artículo vamos a ver cómo habilitar la búsqueda de columnas en la vista de lista mediante DataTables.
DataTable proporciona las funciones de devolución de llamada de encabezado y pie de página para manipular datos HTML con una interfaz de usuario intuitiva.
Para implementar el procesamiento del lado servidor, hemos usado la clase SSP DataTables para generar la solicitud de datos con los parámetros necesarios. La respuesta se codificará como JSON y se cargará en la interfaz de usuario de DataTable a través de AJAX.
Integración de DataTables en HTML
La biblioteca DataTables se agrega como parte del script fuente descargado. También puede descargar la biblioteca de DataTables desde su repositorio oficial en Github. Este código muestra el código HTML para incluir e inicializar DataTables.
Una referencia de tabla HTML5 se utiliza como un selector de jQuery para inicializar DataTables con las opciones necesarias. Para agregar el complemento, necesitamos seguir los siguientes pasos.
<!DOCTYPE html>
<head>
<script src="vendor/jquery/jquery-3.2.1.min.js"></script>
<link rel="stylesheet" href="vendor/DataTables/jquery.datatables.min.css">
<script src="vendor/DataTables/jquery.dataTables.min.js" type="text/javascript"></script>
<link href="style.css" rel="stylesheet" type="text/css" />
<title>Buscar en columnas DataTables (Completo)</title>
<script>
$(document).ready(function ()
{
$('#tbl-contact thead th').each(function () {
var title = $(this).text();
$(this).html(title+' <input type="text" class="col-search-input" placeholder="Search ' + title + '" />');
});
var table = $('#tbl-contact').DataTable({
"scrollX": true,
"pagingType": "numbers",
"processing": true,
"serverSide": true,
"ajax": "server.php",
order: [[2, 'asc']],
columnDefs: [{
targets: "_all",
orderable: false
}]
});
table.columns().every(function () {
var table = this;
$('input', this.header()).on('keyup change', function () {
if (table.search() !== this.value) {
table.search(this.value).draw();
}
});
});
});
</script>
</head>
<body>
<div class="datatable-container">
<h2>Buscar en columnas DataTables ServerSide(Completo)</h2>
<hr>
<table name="tbl-contact" id="tbl-contact" class="display" cellspacing="0" width="100%">
<thead>
<tr>
<th>Nombres</th>
<th>Apellidos</th>
<th>Dirección</th>
<th>Teléfono</th>
<th>Fecha de Nacimiento</th>
</tr>
</thead>
</table>
</div>
</body>
</html>
Devolución de llamada de encabezado de DataTable para mostrar la búsqueda de columnas
En esta sección, muestra el código de la función de devolución de llamada DataTables. En esta devolución de llamada, el encabezado de tabla se cambia anexando la opción de búsqueda con cada columna.
Con la referencia del campo de entrada de búsqueda en cada encabezado de columna, el evento key-up se asigna para desencadenar la solicitud de búsqueda. Al escribir en el campo de entrada de búsqueda, se filtrarán los datos de la columna DataTables.
<script>
$(document).ready(function ()
{
$('#tbl-contact thead th').each(function () {
var title = $(this).text();
$(this).html(title+' <input type="text" class="col-search-input" placeholder="Search ' + title + '" />');
});
table.columns().every(function () {
var table = this;
$('input', this.header()).on('keyup change', function () {
if (table.search() !== this.value) {
table.search(this.value).draw();
}
});
});
});
</script>
Código PHP para obtener datos para DataTables mediante el procesamiento del lado del servidor
Este es el código que contiene el archivo.php servidor. Este archivo se marcará como la opción de origen al inicializar las DataTables. En este código, la configuración de la base de datos, la tabla, las columnas primary_key las especificaciones se declaran y se utilizan para solicitar los datos dinámicos de las DataTables.
¿Qué es la La clase SSP?
Se usa para capturar el resultado de la base de datos mediante el procesamiento simple o complejo del lado servidor. Los datos resultantes se codificarán en formato JSON y se repetirán en la devolución de llamada AJAX.
<?php
// DB table to use
$table = 'tbl_contact';
// Table's primary key
$primaryKey = 'id';
$columns = array(
array( 'db' => 'first_name', 'dt' => 0 ),
array( 'db' => 'last_name', 'dt' => 1 ),
array( 'db' => 'address', 'dt' => 2 ),
array( 'db' => 'phone', 'dt' => 3,),
array( 'db' => 'date_of_birth','dt' => 4,
'formatter' => function( $d, $row ) {
return date( 'd-m-Y', strtotime($d));
}
)
);
// SQL server connection information
$sql_details = array(
'user' => 'root',
'pass' => 'test',
'db' => 'blog_samples',
'host' => 'localhost'
);
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP
* server-side, there is no need to edit below this line.
*/
require( 'vendor/DataTables/server-side/scripts/ssp.class.php' );
echo json_encode(
SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);
DataTables (Clase de SSP)
Esta es la clase PHP utilizada para el procesamiento del lado del servidor de DataTables. Controla el establecimiento de conexiones de base de datos y el manejo de consultas para el filtrado de datos, la ordenación y muchos propósitos.
<?php
// REMOVE THIS BLOCK - used for DataTables test environment only!
$file = $_SERVER['DOCUMENT_ROOT'].'/datatables/pdo.php';
if ( is_file( $file ) ) {
include( $file );
}
class SSP {
/**
* Create the data output array for the DataTables rows
*
* @param array $columns Column information array
* @param array $data Data from the SQL get
* @return array Formatted data in a row based format
*/
static function data_output ( $columns, $data )
{
$out = array();
for ( $i=0, $ien=count($data) ; $i<$ien ; $i++ ) {
$row = array();
for ( $j=0, $jen=count($columns) ; $j<$jen ; $j++ ) {
$column = $columns[$j];
// Is there a formatter?
if ( isset( $column['formatter'] ) ) {
$row[ $column['dt'] ] = $column['formatter']( $data[$i][ $column['db'] ], $data[$i] );
}
else {
$row[ $column['dt'] ] = $data[$i][ $columns[$j]['db'] ];
}
}
$out[] = $row;
}
return $out;
}
/**
* Database connection
*
* Obtain an PHP PDO connection from a connection details array
*
* @param array $conn SQL connection details. The array should have
* the following properties
* * host - host name
* * db - database name
* * user - user name
* * pass - user password
* @return resource PDO connection
*/
static function db ( $conn )
{
if ( is_array( $conn ) ) {
return self::sql_connect( $conn );
}
return $conn;
}
/**
* Paging
*
* Construct the LIMIT clause for server-side processing SQL query
*
* @param array $request Data sent to server by DataTables
* @param array $columns Column information array
* @return string SQL limit clause
*/
static function limit ( $request, $columns )
{
$limit = '';
if ( isset($request['start']) && $request['length'] != -1 ) {
$limit = "LIMIT ".intval($request['start']).", ".intval($request['length']);
}
return $limit;
}
/**
* Ordering
*
* Construct the ORDER BY clause for server-side processing SQL query
*
* @param array $request Data sent to server by DataTables
* @param array $columns Column information array
* @return string SQL order by clause
*/
static function order ( $request, $columns )
{
$order = '';
if ( isset($request['order']) && count($request['order']) ) {
$orderBy = array();
$dtColumns = self::pluck( $columns, 'dt' );
for ( $i=0, $ien=count($request['order']) ; $i<$ien ; $i++ ) {
// Convert the column index into the column data property
$columnIdx = intval($request['order'][$i]['column']);
$requestColumn = $request['columns'][$columnIdx];
$columnIdx = array_search( $requestColumn['data'], $dtColumns );
$column = $columns[ $columnIdx ];
if ( $requestColumn['orderable'] == 'true' ) {
$dir = $request['order'][$i]['dir'] === 'asc' ?
'ASC' :
'DESC';
$orderBy[] = '`'.$column['db'].'` '.$dir;
}
}
if ( count( $orderBy ) ) {
$order = 'ORDER BY '.implode(', ', $orderBy);
}
}
return $order;
}
/**
* Searching / Filtering
*
* Construct the WHERE clause for server-side processing SQL query.
*
* NOTE this does not match the built-in DataTables filtering which does it
* word by word on any field. It's possible to do here performance on large
* databases would be very poor
*
* @param array $request Data sent to server by DataTables
* @param array $columns Column information array
* @param array $bindings Array of values for PDO bindings, used in the
* sql_exec() function
* @return string SQL where clause
*/
static function filter ( $request, $columns, &$bindings )
{
$globalSearch = array();
$columnSearch = array();
$dtColumns = self::pluck( $columns, 'dt' );
if ( isset($request['search']) && $request['search']['value'] != '' ) {
$str = $request['search']['value'];
for ( $i=0, $ien=count($request['columns']) ; $i<$ien ; $i++ ) {
$requestColumn = $request['columns'][$i];
$columnIdx = array_search( $requestColumn['data'], $dtColumns );
$column = $columns[ $columnIdx ];
if ( $requestColumn['searchable'] == 'true' ) {
$binding = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
$globalSearch[] = "`".$column['db']."` LIKE ".$binding;
}
}
}
// Individual column filtering
if ( isset( $request['columns'] ) ) {
for ( $i=0, $ien=count($request['columns']) ; $i<$ien ; $i++ ) {
$requestColumn = $request['columns'][$i];
$columnIdx = array_search( $requestColumn['data'], $dtColumns );
$column = $columns[ $columnIdx ];
$str = $requestColumn['search']['value'];
if ( $requestColumn['searchable'] == 'true' &&
$str != '' ) {
$binding = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
$columnSearch[] = "`".$column['db']."` LIKE ".$binding;
}
}
}
// Combine the filters into a single string
$where = '';
if ( count( $globalSearch ) ) {
$where = '('.implode(' OR ', $globalSearch).')';
}
if ( count( $columnSearch ) ) {
$where = $where === '' ?
implode(' AND ', $columnSearch) :
$where .' AND '. implode(' AND ', $columnSearch);
}
if ( $where !== '' ) {
$where = 'WHERE '.$where;
}
return $where;
}
static function simple ( $request, $conn, $table, $primaryKey, $columns )
{
$bindings = array();
$db = self::db( $conn );
// Build the SQL query string from the request
$limit = self::limit( $request, $columns );
$order = self::order( $request, $columns );
$where = self::filter( $request, $columns, $bindings );
// Main query to actually get the data
$data = self::sql_exec( $db, $bindings,
"SELECT `".implode("`, `", self::pluck($columns, 'db'))."`
FROM `$table`
$where
$order
$limit"
);
// Data set length after filtering
$resFilterLength = self::sql_exec( $db, $bindings,
"SELECT COUNT(`{$primaryKey}`)
FROM `$table`
$where"
);
$recordsFiltered = $resFilterLength[0][0];
// Total data set length
$resTotalLength = self::sql_exec( $db,
"SELECT COUNT(`{$primaryKey}`)
FROM `$table`"
);
$recordsTotal = $resTotalLength[0][0];
/*
* Output
*/
return array(
"draw" => isset ( $request['draw'] ) ?
intval( $request['draw'] ) :
0,
"recordsTotal" => intval( $recordsTotal ),
"recordsFiltered" => intval( $recordsFiltered ),
"data" => self::data_output( $columns, $data )
);
}
static function complex ( $request, $conn, $table, $primaryKey, $columns, $whereResult=null, $whereAll=null )
{
$bindings = array();
$db = self::db( $conn );
$localWhereResult = array();
$localWhereAll = array();
$whereAllSql = '';
// Build the SQL query string from the request
$limit = self::limit( $request, $columns );
$order = self::order( $request, $columns );
$where = self::filter( $request, $columns, $bindings );
$whereResult = self::_flatten( $whereResult );
$whereAll = self::_flatten( $whereAll );
if ( $whereResult ) {
$where = $where ?
$where .' AND '.$whereResult :
'WHERE '.$whereResult;
}
if ( $whereAll ) {
$where = $where ?
$where .' AND '.$whereAll :
'WHERE '.$whereAll;
$whereAllSql = 'WHERE '.$whereAll;
}
// Main query to actually get the data
$data = self::sql_exec( $db, $bindings,
"SELECT `".implode("`, `", self::pluck($columns, 'db'))."`
FROM `$table`
$where
$order
$limit"
);
// Data set length after filtering
$resFilterLength = self::sql_exec( $db, $bindings,
"SELECT COUNT(`{$primaryKey}`)
FROM `$table`
$where"
);
$recordsFiltered = $resFilterLength[0][0];
// Total data set length
$resTotalLength = self::sql_exec( $db, $bindings,
"SELECT COUNT(`{$primaryKey}`)
FROM `$table` ".
$whereAllSql
);
$recordsTotal = $resTotalLength[0][0];
/*
* Output
*/
return array(
"draw" => isset ( $request['draw'] ) ?
intval( $request['draw'] ) :
0,
"recordsTotal" => intval( $recordsTotal ),
"recordsFiltered" => intval( $recordsFiltered ),
"data" => self::data_output( $columns, $data )
);
}
/**
* Connect to the database
*
* @param array $sql_details SQL server connection details array, with the
* properties:
* * host - host name
* * db - database name
* * user - user name
* * pass - user password
* @return resource Database connection handle
*/
static function sql_connect ( $sql_details )
{
try {
$db = @new PDO(
"mysql:host={$sql_details['host']};dbname={$sql_details['db']}",
$sql_details['user'],
$sql_details['pass'],
array( PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION )
);
}
catch (PDOException $e) {
self::fatal(
"An error occurred while connecting to the database. ".
"The error reported by the server was: ".$e->getMessage()
);
}
return $db;
}
/**
* Execute an SQL query on the database
*
* @param resource $db Database handler
* @param array $bindings Array of PDO binding values from bind() to be
* used for safely escaping strings. Note that this can be given as the
* SQL query string if no bindings are required.
* @param string $sql SQL query to execute.
* @return array Result from the query (all rows)
*/
static function sql_exec ( $db, $bindings, $sql=null )
{
// Argument shifting
if ( $sql === null ) {
$sql = $bindings;
}
$stmt = $db->prepare( $sql );
//echo $sql;
// Bind parameters
if ( is_array( $bindings ) ) {
for ( $i=0, $ien=count($bindings) ; $i<$ien ; $i++ ) {
$binding = $bindings[$i];
$stmt->bindValue( $binding['key'], $binding['val'], $binding['type'] );
}
}
// Execute
try {
$stmt->execute();
}
catch (PDOException $e) {
self::fatal( "An SQL error occurred: ".$e->getMessage() );
}
// Return all
return $stmt->fetchAll( PDO::FETCH_BOTH );
}
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Internal methods
*/
/**
* Throw a fatal error.
*
* This writes out an error message in a JSON string which DataTables will
* see and show to the user in the browser.
*
* @param string $msg Message to send to the client
*/
static function fatal ( $msg )
{
echo json_encode( array(
"error" => $msg
) );
exit(0);
}
/**
* Create a PDO binding key which can be used for escaping variables safely
* when executing a query with sql_exec()
*
* @param array &$a Array of bindings
* @param * $val Value to bind
* @param int $type PDO field type
* @return string Bound key to be used in the SQL where this parameter
* would be used.
*/
static function bind ( &$a, $val, $type )
{
$key = ':binding_'.count( $a );
$a[] = array(
'key' => $key,
'val' => $val,
'type' => $type
);
return $key;
}
/**
* Pull a particular property from each assoc. array in a numeric array,
* returning and array of the property values from each item.
*
* @param array $a Array to get data from
* @param string $prop Property to read
* @return array Array of property values
*/
static function pluck ( $a, $prop )
{
$out = array();
for ( $i=0, $len=count($a) ; $i<$len ; $i++ ) {
$out[] = $a[$i][$prop];
}
return $out;
}
/**
* Return a string from an array or a string
*
* @param array|string $a Array to join
* @param string $join Glue for the concatenation
* @return string Joined string
*/
static function _flatten ( $a, $join = ' AND ' )
{
if ( ! $a ) {
return '';
}
else if ( $a && is_array($a) ) {
return implode( $join, $a );
}
return $a;
}
}
Script de base de datos
Son las líneas SQL que serían las consultas que necesitamos ejecutar en nuestro gestor de base de datos y contiene la tabla de base de datos tbl_contacto, estructura y los registros de datos.
Para continuar, debemos de importar este script mientras configura este ejemplo.
CREATE TABLE `tbl_contacto` ( `id` int(11) NOT NULL, `nombres` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `apellidos` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `direccion` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `telefono` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `fecha_de_naci` date NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Volcado de datos para la tabla `tbl_contacto` -- INSERT INTO `tbl_contacto` (`id`, `nombres`, `apellidos`, `direccion`, `email`, `telefono`, `fecha_de_naci`) VALUES (1, 'Peter', 'Parker', '970 Princee St.\r\n\r\nPiqua, OH 45356', 'peter@parker.com', '4455664455', '1990-04-10'), (2, 'Barry', 'Allen', '976 Livingston Lane, FL 33702', 'barry@allen.com', '2211335566', '1983-02-02'), (3, 'Bruce', 'Banner', '69 Bridge Lane \r\nBrooklyn, NY 11201', 'bruce@banner.com', '7788995566', '1987-04-14'), (4, 'Bruce', 'Wayne', '896 East Smith Store Dr, TX 77566', 'bruce@wayne.com', '8877887744', '1991-11-15'), (5, 'Harvy', 'Dent', '35 Wakehurst Avenue \r\nNoblesville, IN 46060', 'harvy@dent.com', '9988774445', '1990-10-01'), (6, 'Tony', 'Stark', '31 Edgewater Court \r\nMalden, MA 02148', 'tony@stark.com', '8899886655', '1984-10-05'), (7, 'Nick', 'Fury', '70 WakePrin St.\r\n\r\nPiqua, OL 356444', 'nick@fury.com', '9966554488', '1980-01-25'), (8, 'John', 'Mclane', '76 Kevins Lane \r\n\r\nSt. Petersburg, FN 33711', 'john@maclay.com', '7744114411', '2000-11-15'), (9, 'Howard', 'Roark', '88 Golden Lane \r\n\r\nBrooklyn, LS 11204', 'howard@roark.com', '8745554413', '2011-11-15'), (10, 'Peter', 'Keating', '86 Smith Road\r\n\r\nLake Jackson, TQ 77566', 'peter@keating.com', '9089094445', '2013-11-15'); -- -- Índices para tablas volcadas -- -- -- Indices de la tabla `tbl_contacto` -- ALTER TABLE `tbl_contacto` ADD PRIMARY KEY (`id`); -- -- AUTO_INCREMENT de las tablas volcadas -- -- -- AUTO_INCREMENT de la tabla `tbl_contacto` -- ALTER TABLE `tbl_contacto` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;
Conclusión del articulo
Si vas a crear un sistema web escrito en PHP lo recomendable es usar este plugin para optimizar los resultados cuando tu base de datos ya tenga un considerable número de registros.
La implementación es muy sencilla, basta con 4 simples pasos:
- Descargar el Plugin
- Declarar los archivos del plugin en la etiqueta head de nuestro archivo en donde se ubicará el CRUD
- Inicializar el datatables
- Usar el método serverside para trabajar con grandes cantidades de registros.
Espero que esta breve explicación les ayude en sus proyectos web y no se olviden a compartir este artículo en las redes sociales.
