5.3. DataTables

DataTable es un plugin JQuery que proporciona herramientas muy útiles a la hora de mostrar contenido en una página Web mediante una tabla HTML. Entre sus funcionalidades están:

  1. Paginación de registros.
  2. Filtro de búsqueda.
  3. Ordenación de datos por columna.

DataTable posee una API sencilla de utilizar y modificar, además, la documentación en la página es bastante explícita y con ejemplos muy claros del uso de cada funcionalidad. Una particularidad de este plugin es que puede ser adaptado al estilo del framework Bootstrap, del cual ya he hablado anteriormente.

Abordaremos aquí el uso de este plugin desde el lado servidor con PHP y MySQL

El código base HTML será:

<!DOCTYPE html>
<html lang='es'>
<head>
  <meta charset="UTF-8">
  <title>DataTables</title>
  <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" rel='stylesheet'>
  <link href="https://cdn.datatables.net/1.10.10/css/jquery.dataTables.min.css" rel="stylesheet">
</head>
<body>
    <div class="container">
    <div id='Etiquetas'>
	  <div class="container-fluid">
        <table id="plagas" class="table table-striped table-bordered">
          <caption class="text-center"><h3>Plaguicidas autorizados en México</h3></caption>
          <thead>
            <tr>
              <th>Grupo</th>
              <th>Nombre Comercial</th>
              <th>Ingrediente</th>
              <th>Registro</th>
              <th>Vigencia</th>			  
            </tr>
          </thead>

          <tfoot>
            <tr>
              <th>Grupo</th>
              <th>Nombre Comercial</th>
              <th>Ingrediente</th>
              <th>Registro</th>
              <th>Vigencia</th>			  
            </tr>
          </tfoot>
        </table>
      </div>
    </div> <!-- Etiquetas -->
    <div class="row">
      <div class="col-md-3">H. Veracruz, Ver. México</div>
      <div class="col-md-6">© José Evaristo Pacheco Velasco. Dr. Juan Antonio Villanueva Jiménez</div>
      <div class="col-md-3">2016</div>
    </div>
  </div> <!-- container -->
  <script src='http://cdnjs.cloudflare.com/ajax/libs/jquery/2.1.3/jquery.min.js'></script>
  <script src='https://maxcdn.bootstrapcdn.com/bootstrap/3.3.1/js/bootstrap.min.js'></script>
  <script src="http://cdn.datatables.net/1.10.2/js/jquery.dataTables.min.js"></script>
  <script src="index.js"></script>
</body>
</html>

Observe que la plantilla base carga jQuey, Bootstrap y datatables todos ellos desde CDN. Además la estructura de una tabla sin datos. Estos serán cargados de un archivo JSON, es un formato ligero para el intercambio de datos. JSON es un subconjunto de la notación literal de objetos de JavaScript que no requiere el uso de XML.

Para cargar este archivo haremos un llamada a MySQL mediante AJAX y PHP

$(document).ready(function() {
  $.extend( $.fn.dataTable.defaults, {
    'language': {
       "url": "https://cdn.datatables.net/plug-ins/9dcbecd42ad/i18n/Spanish.json"
    },
  	'pagingType': 'full',
    retrieve: true,
  } ); 
  
  $('#plagas').dataTable( {
	'bProcessing': true,
	'bServerSide': true,
	'sAjaxSource': 'plagas.php'
  } );
} );

Ahora observe el siguiente código PHP, cuyo objetivo es extraer los datos de la tabla y regresarlos en formato JSON. De este código solo tendras que cambiar las columnas a mostrar y la consulta SQl,

El ejemplo muestra el catálogo de plaguicidas tomado de un documento oficial elaborado por las Secretarías que Integran a la Comisión Intersecretarial para el Control del Proceso y Uso de Plaguicidas, Fertilizantes y Sustancias Tóxicas (CICOPLAFEST). Con el objetivo exclusivo de mostrar un ejemplo original del uso de DataTables, que difiera de los clásicos ejemplos en la red


<?php
  mb_internal_encoding('UTF-8');
  $aColumns = array( 'grupo','nombreComercial', 'ingrediente', 'registro','vigencia' );

  /* Columna indexada (Mejora el desempeño de la cardinalidad de la tabla ) */
  $sIndexColumn = "id";
	
  // DB table to use
  $sTable = 'etiquetas';
  
  // Información para la conexión con la BD
  $gaSql['user']     = 'login';
  $gaSql['password'] = 'password';
  $gaSql['db']       = 'bd';
  $gaSql['server']   = 'localhost';
  $gaSql['port']     = 3306; // 3306 is the default MySQL port
	
	
  /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
   * If you just want to use the basic configuration for DataTables with PHP server-side, there is
   * no need to edit below this line
 */
	
  /** MySQL connection  */
  
  $gaSql['link'] =  mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password']  )
                    or die( 'Could not open connection to server' );
	
  mysql_select_db( $gaSql['db'], $gaSql['link'] ) or die( 'Could not select database '. $gaSql['db'] );
	
	
  /** Paginación */
  $sLimit = "";
  if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' ) {
	 $sLimit = 'LIMIT '.mysql_real_escape_string( $_GET['iDisplayStart'] ).', '.
                mysql_real_escape_string( $_GET['iDisplayLength'] );
  }
	
  /** Ordering */
  if ( isset( $_GET['iSortCol_0'] ) ) {
    $sOrder = "ORDER BY  ";
    for ( $i=0 ; $i<intval( $_GET['iSortingCols'] ) ; $i++ ) {
      if( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == 'true' ) {
        $sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ].' '.mysql_real_escape_string( $_GET['sSortDir_'.$i] ) .', ';
      }
    }
  	
    $sOrder = substr_replace( $sOrder, "", -2 );
    if( $sOrder == 'ORDER BY' ) { $sOrder = '';}
  }
	
	
  /** Filtering
    * 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, but concerned about efficiency
    * on very large tables, and MySQL's regex functionality is very limited
  */
  $sWhere = '';
  if( $_GET['sSearch'] != '' ) {
    $sWhere = "WHERE (";
    for( $i=0 ; $i<count($aColumns) ; $i++ ) {
      $sWhere.= $aColumns[$i]." LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ";
	}
	$sWhere = substr_replace( $sWhere, "", -3 );
	$sWhere .= ')';
  }
	
  /* Individual column filtering */
  for ( $i=0 ; $i<count($aColumns) ; $i++ ) {
    if( $_GET['bSearchable_'.$i] == 'true' && $_GET['sSearch_'.$i] != '' ) {
      if( $sWhere == '' ) {
        $sWhere = "WHERE ";
      } else {
          $sWhere .= " AND ";
      }
      $sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string($_GET['sSearch_'.$i])."%' ";
    }
  }
	
  /* * SQL queries
	 * Get data to display  */
  $sQuery = 'SELECT SQL_CALC_FOUND_ROWS '.str_replace(' , ', ' ', implode(', ', $aColumns))."
            FROM $sTable
            $sWhere $sOrder $sLimit";
  $rResult = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
	
  /* Data set length after filtering */
  $sQuery = 'SELECT FOUND_ROWS()';
  $rResultFilterTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
  $aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
  $iFilteredTotal = $aResultFilterTotal[0];
	
  /* Total data set length */
  $sQuery = 'SELECT COUNT('.$sIndexColumn.") FROM  $sTable";
  $rResultTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
  $aResultTotal = mysql_fetch_array($rResultTotal);
  $iTotal       = $aResultTotal[0];
	
  /** Output **/

  $output = array(
              'sEcho' => intval($_GET['sEcho']),
              'iTotalRecords' => $iTotal,
              'iTotalDisplayRecords' => $iFilteredTotal,
              'aaData' => array()
  );

  while ( $aRow = mysql_fetch_array( $rResult ) ) {
    $row = array();
    for ( $i=0 ; $i<count($aColumns) ; $i++ ) {
      if ( $aColumns[$i] == 'version' ) {
        /* Special output formatting for 'version' column */
        $row[] = ($aRow[ $aColumns[$i] ]=='0') ? '-' : $aRow[ $aColumns[$i] ];
      } else if ( $aColumns[$i] != ' ' ) {
        /* General output */
        $row[] = $aRow[ $aColumns[$i] ];
      }
    }
    $output['aaData'][] = $row;
  }
  
  echo json_encode( $output );
?>