Datatables, Datatables Serverside, mysql, PHP

Cara Membuat DataTables Server Side Join Tabel Side Page Resize dengan PHP MySQL dan Ajax

7 Februari 202010 Maret 2020 by admin initekno

Initekno.com – Kali ini masih pembahasan tentang seputar datatables serverside, cuman bedanya ada sedikit hal yang dirubah, yaitu kamu bisa menarik tabel itu sesuai kemauan kamu, artinya data yang tampil akan menyesuaikan ukuran lewat tabel pada datatables,

Daftar Tutorial DataTables Join Table, Keseluruhan :

Sebelumnya saya sudah membuat tutorial datatables serverside yang pertama, yaitu dasar. Silahkan baca di link artikel bawah ini. Dan sebaiknya kamu baca dahulu bagian datatables serverside yang paling dasar, karena saya menggunakan struktur database dan tabel yang sama.

Cara Membuat DataTables Server Side dengan PHP, MySQL join Tabel dan Ajax

Jika kamu membaca artikel mengenai datatables sebelumnya, kamu akan menjumpai struktur folder yang sederhana. tapi kali ini saya akan menyertakan file javascript external.

Baik, tanpa basa-basi langsung saja buat 1 file PHP, dan simpan dengan nama index.php, tuliskan kode dibawah ini.

<!DOCTYPE html>
<html>
	<title>initekno - DataTables Page Resize ( data server side)</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.pageResize.min.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( {
					pageResize: true,  // page resize di aktifkan
					processing: true,
					serverSide: true,
					ajax: "data_server.php",
				});
						
				var wrapper = $('#resize_wrapper');
				$('#resize_handle').on( 'mousedown', function (e) {
					var mouseStartY = e.pageY;
					var resizeStartHeight = wrapper.height();

					$(document)
						.on( 'mousemove.demo', function (e) {
							var height = resizeStartHeight + (e.pageY - mouseStartY);
							if ( height < 200 ) {
								height = 200;
							}
							wrapper.height( height );
						} )
						.on( 'mouseup.demo', function (e) {
							$(document).off( 'mousemove.demo mouseup.demo' );
						} );

					return false;
				} );
			} );
		</script>
		<style type="text/css">
		div.header {
		    margin: 0 auto;
		    max-width:980px;
		}
		body {
			/*font google*/
	font-family: 'Rubik', sans-serif;
		    background: #f7f7f7;
		    color: #333;
		}
		
		div.container {
			margin: 0 auto;
			width:75%;	
		}

		#resize_wrapper {
			position: relative;
			height: 520px;
			padding: 0.5em 0.5em 1.5em 0.5em;
			border: 1px solid #aaa;
			border-radius: 0.5em;
			background-color: #f9f9f9;
		}

		#resize_handle {
			position: absolute;
			bottom: 0;
			left: 0;
			right: 0;
			height: 1.5em;
			border-bottom-right-radius: 0.5em;
			border-bottom-left-radius: 0.5em;
			text-align: center;
			font-size: 0.8em;
			line-height: 1.5em;
			background-color: black;
			color:#FFF;
			cursor: pointer;
		}

		table.dataTable th,
		table.dataTable td {
			white-space: nowrap;
		}

		div.dataTables_length {
			display: none;
		}
	</style>	
	</head>
	<body>
		<div class="header"><h1>initekno - DataTables Page Resize ( data server side) </h1></div>
		<div class="container">
			<div id="resize_wrapper">
				<table id="dataku"  class="display dataTable" cellspacing="0" style="width:100%;" width="100%" >
					<thead>
						<tr>
							<td>Kelas</td>
							<td>Status</td>
							<td>Tentor</td>
						</tr>
					</thead>
				</table>
				<div id="resize_handle">Tarik untuk menyesuaikan</div>
			</div>
		</div>
	</body>
</html>

100

101

102

103

104

105

106

107

108

109

110

111

112

<!DOCTYPE html>

<html>

<title>initekno - DataTables Page Resize ( data server side)</title>

<head>

$(document).ready(function() {

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

pageResize: true, // page resize di aktifkan

processing: true,

serverSide: true,

ajax: "data_server.php",

});

var wrapper = $('#resize_wrapper');

$('#resize_handle').on( 'mousedown', function (e) {

var mouseStartY = e.pageY;

var resizeStartHeight = wrapper.height();

$(document)

.on( 'mousemove.demo', function (e) {

var height = resizeStartHeight + (e.pageY - mouseStartY);

if ( height < 200 ) {

height = 200;

}

wrapper.height( height );

} )

.on( 'mouseup.demo', function (e) {

$(document).off( 'mousemove.demo mouseup.demo' );

} );

return false;

} );

</script>

div.header {

margin: 0 auto;

max-width:980px;

}

body {

/*font google*/

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

background: #f7f7f7;

color: #333;

}

div.container {

margin: 0 auto;

width:75%;

}

#resize_wrapper {

position: relative;

height: 520px;

padding: 0.5em 0.5em 1.5em 0.5em;

border: 1px solid #aaa;

border-radius: 0.5em;

background-color: #f9f9f9;

}

#resize_handle {

position: absolute;

bottom: 0;

left: 0;

right: 0;

height: 1.5em;

border-bottom-right-radius: 0.5em;

border-bottom-left-radius: 0.5em;

text-align: center;

font-size: 0.8em;

line-height: 1.5em;

background-color: black;

color:#FFF;

cursor: pointer;

}

table.dataTable th,

table.dataTable td {

white-space: nowrap;

}

div.dataTables_length {

display: none;

}

</style>

</head>

<body>

<div class="header"><h1>initekno - DataTables Page Resize ( data server side) </h1></div>

<thead>

<tr>

<td>Kelas</td>

<td>Status</td>

<td>Tentor</td>

</tr>

</thead>

</table>

<div id="resize_handle">Tarik untuk menyesuaikan</div>

</div>

</body>

</html>

Seperti biasa, kamu akan menjumpai struktur file yang bercampur baur layaknya penulisan react native. Jadi ada kode javascript, html dan css didalam file index.php tersebut.

Langkah selanjutnya buat file baru dan berinama dengan data_server.php tuliskan kode dibawah ini:

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "db_mt1webapp";

$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'
);

//----------------------------------------------------------------------------------
//join 2 tabel dan bisa lebih, tergantung kebutuhan
$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;

//----------------------------------------------------------------------------------
$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";


//----------------------------------------------------------------------------------
$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

$servername = "localhost";

$username = "root";

$password = "";

$dbname = "db_mt1webapp";

$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'

);

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

//join 2 tabel dan bisa lebih, tergantung kebutuhan

$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;

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

$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";

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

$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);

file ini nantinya akan kita load pada ajax datatables di file index.php.

var dataTable =  $('#dataku').DataTable( {
					pageResize: true,  // page resize di aktifkan
					processing: true,
					serverSide: true,
					ajax: "data_server.php",
				});

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

pageResize: true, // page resize di aktifkan

processing: true,

serverSide: true,

ajax: "data_server.php",

});

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 coba jalankan project ini pada browser,

Sekian artikel ini jangan lupa untuk mengikuti artikel datatables serverside mendatang, Semoga bermanfaat

Untuk melihat file DEMO dilahkan klik button demo dan untuk mendownload source code klik button DOWNLOAD.

DEMO | DOWNLOAD

Initekno.com

Cara Membuat DataTables Server Side Join Tabel Side Page Resize dengan PHP MySQL dan Ajax

Daftar Tutorial DataTables Join Table, Keseluruhan :

One thought on “Cara Membuat DataTables Server Side Join Tabel Side Page Resize dengan PHP MySQL dan Ajax”

Tinggalkan Balasan Batalkan balasan

Initekno.com

Informasi

Kategori

Arsip Blog