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
bro, ada contoh ndak kalau pakai mysqli dan ada tabelnya ada join 2 tabel.
ReplyDeleteAda bro, tapi jadinya lebih condong ke Server Side Processing, saya akan posting.. mohon diingatkan kembali ya :)
Deletemana linknya? belum ada?
Deletemohon dong min, yg mysqli join table, situs sudah saya bookmarknih...
Deletebagus tutorialnya broch. udh saya jalankan work, tp,, saya coba dg mengambil dari database lain tidak mau? padahal saya udh rubah koneksi dan $stabel_nya, mohon bimbingannya
ReplyDeleteperhatikan nama tablenya bro.. coba di teliti lagi..
DeleteThis comment has been removed by the author.
ReplyDeleteGood Blok, Thanks
ReplyDeletehttp://blog.binadarma.ac.id/novrihadinata
sangat membantu saya thanks
ReplyDelete