Beranda » jQuery » PHP » Membuat Table Cantik dan mudah dengan PHP dan Datatables

Membuat Table Cantik dan mudah dengan PHP dan Datatables

Membuat table di PHP sebenarnya tidaklah sulit, tapi juga cukup merepotkan karena tidak sedikit coding yang harus kita ketik, tapi sekarang membuat table di php tidaklah serumit itu lagi karena sudah ada yang namanya Datatables, untuk cari tau apa itu datatables silahkan masuk ke webnya.

Dengan Datatables kita bisa membuat table yang cantik juga menarik serta sangat simple, juga ringan. Saya akan membahas tutorial mengenai pembuatan table menggunakan datatables dengan engine php.

Mari mulai:
Pertama kita harus membuat database dulu di phpmyadmin terserah nama databasenya apa saja"tentunya saya tidak perlu lagi menjelaskan apa itu database dan phpmyadmin".. saya menggunakan xampp sebagai server.

Setelah database dibuat, maka buatlah table dengan nama table "test" supaya sama saja agar tidak terjadi error nantinya. Berikut detail table yang saya buat:

Buatlah file koneksi.php untuk memanggil koneksi dari database yang kita buat ke file php nantinya.

<?php
/* Database connection information */
 $gaSql['user']       = "wirecard_test";
 $gaSql['password']   = "T01Nbbl,n]F!";
 $gaSql['db']         = "wirecard_wireca";
 $gaSql['server']     = "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
  */
 
 /* 
  * 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'] );
 

?>

Lalu kita buat file index.php nya untuk menampilkan tabel berikut kodenya:

index.php

<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Data Karyawan</title>

<script  src="js/jquery.js" type="text/javascript"></script>
        <script src="js/jquery.dataTables.js" type="text/javascript"></script>
  <script src="js/dataTables.responsive.js"></script>
        <style type="text/css">
    @import "css/jquery.dataTables_themeroller.css";
    @import "css/jquery.dataTables.css";
    @import "css/jquery-ui-1.8.4.custom.css";
        </style>
  <style>
.ui-widget {
    font-size: 10px;
}
        </style>
<body class="ui-widget">

<p>Kembali ke Tutorial</p>

<div class="container">

      <!-- Main component for a primary marketing message or call to action -->
      <div class="">
        <div class="">
          <table id="example" class="display" width="100%" cellspacing="0">
          <thead>
            <tr>
     <th>Nama</th>
     <th>Alamat</th>
     <th>Jenis Kelamin</th>
     <th>Email</th>
            <th>HP</th> 
            </tr>
         </thead>
         <tfoot>
            <tr>
            <th>Nama</th>
     <th>Alamat</th>
     <th>Jenis Kelamin</th>
     <th>Email</th>
     <th>HP</th>
            </tr>
        </tfoot>
        </table>
      </div>
    </div> 
</div>
<!---->

<?php include "hits.php"; ?>
</body></html>
<script type="text/javascript">
$(document).ready(function() {
 $('#example').dataTable( {
   "sPaginationType":"full_numbers",
  "bProcessing": true,
  "bServerSide": true,
   "bJQueryUI":true,
   "aaSorting":[[1, "asc"]],
   "sAjaxSource": "source.php",
  "iDisplayLength": 10,
     "aLengthMenu": [[10,25, 50, 100, -1], [10,25, 50, 100, "All"]] 
 } );
} );
</script>

file PHP yang kedua yaitu source.php

source.php

<?php
require_once('lib/koneksi.php');
 /*
  * Script:    DataTables server-side Table cantik
  * Copyright: 2015 - Dedy Saputra
  */
 
 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
  * Easy set variables
  */
 
 /* Array of database columns which should be read and sent back to DataTables. Use a space where
  * you want to insert a non-database field (for example a counter or static image)
  */

 $aColumns = array( 'nama', 'alamat', 'jenis_kelamin','email','hp');
 
 
 /* Indexed column (used for fast and accurate table cardinality) */
 $sIndexColumn = "id";
 
 /* DB table to use */
 $sTable = "test";
 
 
 /* 
  * Paging
  */
 $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 );
?>

Tidak perlu saya jelaskan satu perstu codenya ya.. silahkan dipahami sendiri dengan melihat demo dan source codenya.
Setelah buat file diatas yaitu koneksi.php, index.php dan source.php silahkan jalankan