Datatables, Datatables Serverside, mysql, PHP

Cara Membuat Datatables Scroller Join ke 2 Tabel dengan PHP dan MySQL

9 Maret 202010 Maret 2020 by admin initekno

Initekno.com – Sudah lama tidak update blog ini lagi nih. Kali ini kita akan membahas tentang datatables scroller yang dimana komponen table akan tetap hanya saja yang berubah adalah data dari isi table tersebut.

Gampangnya dengan datatables secroller ini berapapun jumlah data akan mudah di tamping dan di load oleh server jika terjadi pengguliran mouse ke atas maupun bawah.

Daftar Tutorial DataTables Join Table, Keseluruhan :

Baik, mari kita mulai dengan menuliskan kode server seperti berikut ini. Tapi sebelumnya buat file baru dan simpan pada direktori website kamu dengan nama data_server.php

<?php
/* database konfigurasi */
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "db_appmegatim1";

$conn = mysqli_connect($servername, $username, $password, $dbname) or die("Connection failed: " . mysqli_connect_error());


$requestData= $_REQUEST;


$columns = array( 
	0 => 'nama_kelas', 
	1 => 'nama_status_input',
	2 => 'code_devisi_tentor'
);


$sql = " SELECT trx_kelas.nama_kelas, status_input.nama_status_input, code_devisi_tentor ";
$sql.= " FROM trx_monitoring";
$sql.= " JOIN trx_kelas ON trx_monitoring.trx_id_kelas=trx_kelas.id_kelas";
$sql.= " JOIN status_input ON trx_monitoring.status_monitoring=status_input.id_status_input";
$query=mysqli_query($conn, $sql) or die("data_server.php: get dataku");
$totalData = mysqli_num_rows($query);
$totalFiltered = $totalData;


//----------------------------------------------------------------------------------
//query sql join 2 tabel
$sql = " SELECT trx_kelas.nama_kelas, status_input.nama_status_input, code_devisi_tentor ";
$sql.= " FROM trx_monitoring";
$sql.= " JOIN trx_kelas ON trx_monitoring.trx_id_kelas=trx_kelas.id_kelas";
$sql.= " JOIN status_input ON trx_monitoring.status_monitoring=status_input.id_status_input";
$sql.= " WHERE 1=1";

if( !empty($requestData['search']['value']) ) {
	//----------------------------------------------------------------------------------
	$sql.=" AND ( nama_kelas LIKE '".$requestData['search']['value']."%' ";    
	$sql.=" OR nama_status_input LIKE '".$requestData['search']['value']."%' ";
	$sql.=" OR code_devisi_tentor LIKE '".$requestData['search']['value']."%' )";
}

//----------------------------------------------------------------------------------
$query=mysqli_query($conn, $sql) or die("data_server.php: get dataku");
$totalFiltered = mysqli_num_rows($query);
$sql.=" ORDER BY ". $columns[$requestData['order'][0]['column']]."   ".$requestData['order'][0]['dir']."  LIMIT ".$requestData['start']." ,".$requestData['length']."   ";	
$query=mysqli_query($conn, $sql) or die("data_server.php: get dataku");

//----------------------------------------------------------------------------------
$data = array();
while( $row=mysqli_fetch_array($query) ) {
	$nestedData=array(); 
	$nestedData[] = $row["nama_kelas"];
	$nestedData[] = $row["nama_status_input"];
	$nestedData[] = $row["code_devisi_tentor"];
	
	$data[] = $nestedData;
}
//----------------------------------------------------------------------------------
$json_data = array(
			"draw"            => intval( $requestData['draw'] ),  
			"recordsTotal"    => intval( $totalData ), 
			"recordsFiltered" => intval( $totalFiltered ), 
			"data"            => $data );
//----------------------------------------------------------------------------------
echo json_encode($json_data);
?>

<?php

/* database konfigurasi */

$servername = "localhost";

$username = "root";

$password = "";

$dbname = "db_appmegatim1";

$conn = mysqli_connect($servername, $username, $password, $dbname) or die("Connection failed: " . mysqli_connect_error());

$requestData= $_REQUEST;

$columns = array(

0 => 'nama_kelas',

1 => 'nama_status_input',

2 => 'code_devisi_tentor'

);

$sql = " SELECT trx_kelas.nama_kelas, status_input.nama_status_input, code_devisi_tentor ";

$sql.= " FROM trx_monitoring";

$sql.= " JOIN trx_kelas ON trx_monitoring.trx_id_kelas=trx_kelas.id_kelas";

$sql.= " JOIN status_input ON trx_monitoring.status_monitoring=status_input.id_status_input";

$query=mysqli_query($conn, $sql) or die("data_server.php: get dataku");

$totalData = mysqli_num_rows($query);

$totalFiltered = $totalData;

//----------------------------------------------------------------------------------

//query sql join 2 tabel

$sql = " SELECT trx_kelas.nama_kelas, status_input.nama_status_input, code_devisi_tentor ";

$sql.= " FROM trx_monitoring";

$sql.= " JOIN trx_kelas ON trx_monitoring.trx_id_kelas=trx_kelas.id_kelas";

$sql.= " JOIN status_input ON trx_monitoring.status_monitoring=status_input.id_status_input";

$sql.= " WHERE 1=1";

if( !empty($requestData['search']['value']) ) {

//----------------------------------------------------------------------------------

$sql.=" AND ( nama_kelas LIKE '".$requestData['search']['value']."%' ";

$sql.=" OR nama_status_input LIKE '".$requestData['search']['value']."%' ";

$sql.=" OR code_devisi_tentor LIKE '".$requestData['search']['value']."%' )";

}

//----------------------------------------------------------------------------------

$query=mysqli_query($conn, $sql) or die("data_server.php: get dataku");

$totalFiltered = mysqli_num_rows($query);

$sql.=" ORDER BY ". $columns[$requestData['order'][0]['column']]." ".$requestData['order'][0]['dir']." LIMIT ".$requestData['start']." ,".$requestData['length']." ";

$query=mysqli_query($conn, $sql) or die("data_server.php: get dataku");

//----------------------------------------------------------------------------------

$data = array();

while( $row=mysqli_fetch_array($query) ) {

$nestedData=array();

$nestedData[] = $row["nama_kelas"];

$nestedData[] = $row["nama_status_input"];

$nestedData[] = $row["code_devisi_tentor"];

$data[] = $nestedData;

}

//----------------------------------------------------------------------------------

$json_data = array(

"draw" => intval( $requestData['draw'] ),

"recordsTotal" => intval( $totalData ),

"recordsFiltered" => intval( $totalFiltered ),

"data" => $data );

//----------------------------------------------------------------------------------

echo json_encode($json_data);

Kode diatas sama dengan tutorial datatables sebelumnya. Jika kamu belum membacanya maka silahkan untuk membacanya di sini :

Cara Membuat DataTables Serverside Join Table dengan PHP MySQL Ajax, Multi Dinamis Atribut

Langkah selanjutnya buatlah sebuah file index.php dan tuliskan kode berikut ini:

<!DOCTYPE html>
<html>
	<title>Initekno - Datatables Scroller</title>
	<head>
		<link rel="stylesheet" type="text/css" href="css/jquery.dataTables.css">
		<script type="text/javascript" language="javascript" src="js/jquery.js"></script>
		<script type="text/javascript" language="javascript" src="js/jquery.dataTables.js"></script>
		<script type="text/javascript" language="javascript" src="js/dataTables.scroller.js"></script>
		<!--google font-->
		<link href="https://www.initekno.com/cloudme.fonts.googleapis.com/css?family=Rubik&display=swap" rel="stylesheet">

		<script type="text/javascript" language="javascript" >
			$(document).ready(function() {
			   var dataTable =  $('#dataku').DataTable( {
				serverSide: true,
				ajax:{
						url :"data_server.php",
						type: "post", 
						error: function(){
							$(".dataku-error").html("");
							$("#dataku").append('<tbody class="dataku-error"><tr><th colspan="3">Tidak ada data untuk ditampilkan</th></tr></tbody>');
							$("#dataku-error-proses").css("display","none");
						}
					},
				dom: "frtiS",
				scrollY: 200,
				deferRender: true,
				scrollCollapse: true,
				scroller: {
				    loadingIndicator: true
				}
			    } );
			} );
		</script>
		<style>
			div.container {
			    margin: 0 auto;
			    max-width:760px;
			}
			div.header {
			    margin: 100px auto;
			    line-height:30px;
			    max-width:760px;
			}
			body {
			    background: #f7f7f7;
			    color: #333;
			    font-family: 'Rubik', sans-serif;
			}
		</style>
	</head>
	<body>
		<div class="header"><h1>Initekno.com - DataTables  Scroller Server side JOIN tabel</h1></div>
		<div class="container">
			<table id="dataku"  cellpadding="0" cellspacing="0" border="0" class="display" width="100%">
				<thead>
					<tr>
						<th>Kode-kelas</th>
						<th>Status Input</th>
						<th>Kode Unit</th>
					</tr>
				</thead>
			</table>
		</div>
	</body>
</html>

<!DOCTYPE html>

<html>

<title>Initekno - Datatables Scroller</title>

<head>

$(document).ready(function() {

var dataTable = $('#dataku').DataTable( {

serverSide: true,

ajax:{

url :"data_server.php",

type: "post",

error: function(){

$(".dataku-error").html("");

$("#dataku").append('<tbody class="dataku-error"><tr><th colspan="3">Tidak ada data untuk ditampilkan</th></tr></tbody>');

$("#dataku-error-proses").css("display","none");

}

dom: "frtiS",

scrollY: 200,

deferRender: true,

scrollCollapse: true,

scroller: {

loadingIndicator: true

}

} );

</script>

<style>

div.container {

margin: 0 auto;

max-width:760px;

}

div.header {

margin: 100px auto;

line-height:30px;

max-width:760px;

}

body {

background: #f7f7f7;

color: #333;

font-family: 'Rubik', sans-serif;

}

</style>

</head>

<body>

<div class="header"><h1>Initekno.com - DataTables Scroller Server side JOIN tabel</h1></div>

<thead>

<tr>

<th>Kode-kelas</th>

<th>Status Input</th>

</tr>

</thead>

</table>

</div>

</body>

</html>

Seperti sebelumnya bahwa index.php berisi beberapa komponen kode seperti css, html dan javascript

Baik, sekarang jangan lupa untuk membuat kerangka tabelnya.

tabel status input

CREATE TABLE `status_input` (
  `id_status_input` int(11) NOT NULL,
  `nama_status_input` varchar(15) COLLATE utf8mb4_bin NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `status_input` (

`id_status_input` int(11) NOT NULL,

`nama_status_input` varchar(15) COLLATE utf8mb4_bin NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

tabel kelas

CREATE TABLE `trx_kelas` (
  `id_kelas` int(11) NOT NULL,
  `nama_kelas` varchar(20) NOT NULL,
  `trx_id_lokasi` int(11) NOT NULL,
  `code_devisi_lokasi` int(11) NOT NULL,
  `trx_id_wilayah` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `trx_kelas` (

`id_kelas` int(11) NOT NULL,

`nama_kelas` varchar(20) NOT NULL,

`trx_id_lokasi` int(11) NOT NULL,

`code_devisi_lokasi` int(11) NOT NULL,

`trx_id_wilayah` int(11) NOT NULL,

`created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tabel monitoring

CREATE TABLE `trx_monitoring` (
  `id_monitoring` int(11) NOT NULL,
  `code_lok` varchar(3) NOT NULL,
  `trx_id_kelas` int(11) NOT NULL,
  `trx_id_pekan` int(11) NOT NULL,
  `status_monitoring` int(11) NOT NULL,
  `code_devisi_tentor` varchar(4) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `trx_monitoring` (

`id_monitoring` int(11) NOT NULL,

`code_lok` varchar(3) NOT NULL,

`trx_id_kelas` int(11) NOT NULL,

`trx_id_pekan` int(11) NOT NULL,

`status_monitoring` int(11) NOT NULL,

`code_devisi_tentor` varchar(4) NOT NULL,

`created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Ketiga komponen tabel diatas sama persis dengan yang ada pada tutorial datatables serverside pertama, dan tabel ini akan saya gunakan untuk tutorial datatables mendatang.

Sekarang kamu akan menemui hasil yang menakjubkan. Silahkan klik demo untuk melihat hasilnya. Jika kamu butuh kode ini untuk dikembangkan, silahkan download sourcenya.

Sekian semoga bermanfaat.

DEMO DOWNLOAD

Initekno.com

Cara Membuat Datatables Scroller Join ke 2 Tabel dengan PHP dan MySQL

Daftar Tutorial DataTables Join Table, Keseluruhan :

Tinggalkan Balasan Batalkan balasan

Initekno.com

Informasi

Kategori

Arsip Blog