Datatables, HTML, Javascript, mysql, PHP

Membuat CRUD Dengan JQuery Tabledit + Datatables ServerSide + Bootstrap dan PDO

30 Agustus 202030 Agustus 2020 by admin initekno

Initekno.com – Gak kerasa sudah lama tidak posting lagi dalam blog ini, Hey.. apa kabar. semoga baik ya. Kali ini saya akan membagikan tutorial bagus nih, yaitu jquery tabledit. Jaman sekarang pengolahan data musti serba cepat. Kebiasaan bekerja menggunakan excel membuat sebagian orang malas untuk berurusan dengan form html yang dibuat secara umum.

Bayangkan untuk melakukan ubah data, dan entri data saja sebuah sistem dalam platform web masih ada yang menggunakan cara manual, yaitu tambah data muncul form, bapus data muncul alert box.. Semua itu serba lambat. Bagaimana jika kita melakukan ubah data dari beribu-ribu data. Mungkin bisa mencret kita.

Membuat Tabel

Ok, dari permasalahan diatas, ada baiknya kita langsung koding saja. Tapi seperti biasa buat terlebih dahulu design tabelnya seperti berikut ini :

Tahap 1 : yaitu masuk terlebih dahulu kedalam program kerja mysql

Tahap 2 : Cek database yang sudah kamu buat dan gunakan.

disini saya menggunakan nama database “initekno_learn” dan tabel “mahasiswa”. Kemudian buat struktur table seperti berikut ini:

Menulis Baris Kode

Setelah selesai membuat data, silahkan isi table yang kamu buat, setelah itu mari kita koding. Buka teks editor visual studio code dan buat struktur file seperti berikut ini :

initekno_learn
- mahasiswa
  - conn.php
  - edit.php
  - fetch.php
  - index.php

Struktur folder diatas menggambarkan dengan jelas bahwa project web ini bernama initekno_learn. Berupa folder yang harus kamu letakkan pada folder c:->xampp->htdocs

Sekarang ketik kode koneksi seperti berikut ini

<?php
//conn.php
$dsn = 'mysql:dbname=initekno_learn;host=localhost';
$user = 'root'; //user mysql kamu
$password = ''; //isi dengan password mysql kamu

try {
    $connect = new PDO($dsn, $user, $password);
    $connect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    echo 'Koneksi Bermasalah!: ' . $e->getMessage();
}

?>

<?php

//conn.php

$dsn = 'mysql:dbname=initekno_learn;host=localhost';

$user = 'root'; //user mysql kamu

$password = ''; //isi dengan password mysql kamu

try {

$connect = new PDO($dsn, $user, $password);

$connect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

} catch (PDOException $e) {

echo 'Koneksi Bermasalah!: ' . $e->getMessage();

}

Ketik edit.php

<?php
//edit.php
//panggil koneksi
include 'conn.php';
 
if ($_POST['action'] == 'edit') {
    $data = [
        //':nim' => $_POST['nim'],
        //':namam' => $_POST['namam'],
        //':jnsm' => $_POST['jnsm'],
        //':jurusanm' => $_POST['jurusanm'],
        //':kelas' => $_POST['kelas'],
        ':kehadiran' => $_POST['kehadiran'],
        ':tugasm' => $_POST['tugasm'],
        ':utsm' => $_POST['utsm'],
        ':uasm' => $_POST['uasm'],
        ':id' => $_POST['id']
    ];
 
    $query = "
 UPDATE mahasiswa 
 SET kehadiran = :kehadiran,
 tugasm = :tugasm,
 utsm = :utsm,
 uasm = :uasm
 WHERE id = :id
 ";
    $statement = $connect->prepare($query);
    $statement->execute($data);
    echo json_encode($_POST);
}
 
if ($_POST['action'] == 'delete') {
    $query =
        "
 DELETE FROM mahasiswa 
 WHERE id = '" .
        $_POST["id"] .
        "'
 ";
    $statement = $connect->prepare($query);
    $statement->execute();
    echo json_encode($_POST);
}
 
?>

<?php

//edit.php

//panggil koneksi

include 'conn.php';

if ($_POST['action'] == 'edit') {

$data = [

//':nim' => $_POST['nim'],

//':namam' => $_POST['namam'],

//':jnsm' => $_POST['jnsm'],

//':jurusanm' => $_POST['jurusanm'],

//':kelas' => $_POST['kelas'],

':kehadiran' => $_POST['kehadiran'],

':tugasm' => $_POST['tugasm'],

':utsm' => $_POST['utsm'],

':uasm' => $_POST['uasm'],

':id' => $_POST['id']

];

$query = "

UPDATE mahasiswa

SET kehadiran = :kehadiran,

tugasm = :tugasm,

utsm = :utsm,

uasm = :uasm

WHERE id = :id

$statement = $connect->prepare($query);

$statement->execute($data);

echo json_encode($_POST);

}

if ($_POST['action'] == 'delete') {

$query =

DELETE FROM mahasiswa

WHERE id = '" .

$_POST["id"] .

$statement = $connect->prepare($query);

$statement->execute();

echo json_encode($_POST);

}

Ketik kode fetch.php

<?php
//fetch.php
//panggil koneksi
include 'conn.php';
$column = ["id", "nim", "namam", "jnsm", "jurusanm","kelas","kehadiran","tugasm","utsm","uasm"];

$query = "SELECT * FROM mahasiswa ";

if (isset($_POST["search"]["value"])) {
    $query .=
        '
     WHERE nim LIKE "%' .
        $_POST["search"]["value"] .
        '%" 
    OR namam LIKE "%' .
        $_POST["search"]["value"] .
        '%" 
    OR jnsm LIKE "%' .
        $_POST["search"]["value"] .
        '%" 
    OR jurusanm LIKE "%' .
        $_POST["search"]["value"] .
        '%" 
    OR kelas LIKE "%' .
        $_POST["search"]["value"] .
        '%"
    OR kehadiran LIKE "%' .
        $_POST["search"]["value"] .
        '%"
    OR tugasm LIKE "%' .
        $_POST["search"]["value"] .
        '%"
    OR utsm LIKE "%' .
        $_POST["search"]["value"] .
        '%"
    OR uasm LIKE "%' .
        $_POST["search"]["value"] .
        '%"
 ';
}
 
if (isset($_POST["order"])) {
    $query .= 'ORDER BY ' . $column[$_POST['order']['0']['column']] . ' ' . $_POST['order']['0']['dir'] . ' ';
} else {
    $query .= 'ORDER BY id ASC ';
}
$query1 = '';
 
if ($_POST["length"] != -1) {
    $query1 = 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}
 
$statement = $connect->prepare($query);
$statement->execute();
$number_filter_row = $statement->rowCount();
$statement = $connect->prepare($query . $query1);
$statement->execute();
$result = $statement->fetchAll();

$data = [];
foreach ($result as $row) {
    $sub_array = [];
    $sub_array[] = $row['id'];
    $sub_array[] = $row['nim'];
    $sub_array[] = $row['namam'];
    $sub_array[] = $row['jnsm'];
    $sub_array[] = $row['jurusanm'];
    $sub_array[] = $row['kelas'];
    $sub_array[] = $row['kehadiran'];
    $sub_array[] = $row['tugasm'];
    $sub_array[] = $row['utsm'];
    $sub_array[] = $row['uasm'];
    $data[] = $sub_array;
}
 
function count_all_data($connect)
{
    $query = "SELECT * FROM mahasiswa";
    $statement = $connect->prepare($query);
    $statement->execute();
    return $statement->rowCount();
}
 
$output = [
    'draw' => intval($_POST['draw']),
    'recordsTotal' => count_all_data($connect),
    'recordsFiltered' => $number_filter_row,
    'data' => $data,
];
 
echo json_encode($output);
 
?>

<?php

//fetch.php

//panggil koneksi

include 'conn.php';

$column = ["id", "nim", "namam", "jnsm", "jurusanm","kelas","kehadiran","tugasm","utsm","uasm"];

$query = "SELECT * FROM mahasiswa ";

if (isset($_POST["search"]["value"])) {

$query .=

WHERE nim LIKE "%' .

$_POST["search"]["value"] .

'%"

OR namam LIKE "%' .

$_POST["search"]["value"] .

'%"

OR jnsm LIKE "%' .

$_POST["search"]["value"] .

'%"

OR jurusanm LIKE "%' .

$_POST["search"]["value"] .

'%"

OR kelas LIKE "%' .

$_POST["search"]["value"] .

'%"

OR kehadiran LIKE "%' .

$_POST["search"]["value"] .

'%"

OR tugasm LIKE "%' .

$_POST["search"]["value"] .

'%"

OR utsm LIKE "%' .

$_POST["search"]["value"] .

'%"

OR uasm LIKE "%' .

$_POST["search"]["value"] .

'%"

}

if (isset($_POST["order"])) {

$query .= 'ORDER BY ' . $column[$_POST['order']['0']['column']] . ' ' . $_POST['order']['0']['dir'] . ' ';

} else {

$query .= 'ORDER BY id ASC ';

}

$query1 = '';

if ($_POST["length"] != -1) {

$query1 = 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];

}

$statement = $connect->prepare($query);

$statement->execute();

$number_filter_row = $statement->rowCount();

$statement = $connect->prepare($query . $query1);

$statement->execute();

$result = $statement->fetchAll();

$data = [];

foreach ($result as $row) {

$sub_array = [];

$sub_array[] = $row['id'];

$sub_array[] = $row['nim'];

$sub_array[] = $row['namam'];

$sub_array[] = $row['jnsm'];

$sub_array[] = $row['jurusanm'];

$sub_array[] = $row['kelas'];

$sub_array[] = $row['kehadiran'];

$sub_array[] = $row['tugasm'];

$sub_array[] = $row['utsm'];

$sub_array[] = $row['uasm'];

$data[] = $sub_array;

}

function count_all_data($connect)

{

$query = "SELECT * FROM mahasiswa";

$statement = $connect->prepare($query);

$statement->execute();

return $statement->rowCount();

}

$output = [

'draw' => intval($_POST['draw']),

'recordsTotal' => count_all_data($connect),

'recordsFiltered' => $number_filter_row,

'data' => $data,

];

echo json_encode($output);

Terakhir yaitu index.php

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>MAHASISWA AKADEMIK</title>
        <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/3.3.6/css/bootstrap.min.css" />
        <link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css" />

        <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/2.2.3/jquery.min.js"></script>
        <script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
        <script src="https://cdn.datatables.net/1.10.12/js/dataTables.bootstrap.min.js"></script>
		
        <script src="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/3.3.6/js/bootstrap.min.js"></script>
        <script src="https://markcell.github.io/jquery-tabledit/assets/js/tabledit.min.js"></script>
        
</head>
<body>
        <div class="container">
		<br>
		<div class="panel-group">
			<div class="panel panel-primary">
			<div class="panel-heading"><b>NILAI MAHASISWA</b></div>
			<div class="panel-body">
				<div class="table-responsive">
                        <table id="dataku" class="table table-bordered">
                            <thead>
                                <tr>
                                    <th>#</th>
                                    <th>NIM</th>
                                    <th>NAMA</th>
                                    <th>JNSKEL</th>
                                    <th>JURUSAN</th>
									<th>KELAS</th>
                                    <th>KEHADIRAN</th>
                                    <th>TUGAS</th>
                                    <th>UTS</th>
                                    <th>UAS</th>
                                </tr>
                            </thead>
                            <tbody></tbody>
                        </table>
                    </div>
			</div>
		</div>
		</div><!--AKHIR PANEL GROUP-->
		
        </div> <!--AKHIR CONTAINER-->
        </body>
</html>

        <script type="text/javascript" language="javascript">
            $(document).ready(function () {
                
                var dataTable = $("#dataku").DataTable({
					"language":{
						"url":"https://cdn.datatables.net/plug-ins/1.10.21/i18n/Indonesian.json",
						"sEmptyTable":"Tidads"
					},
                    processing: true,
                    serverSide: true,
                    order: [],
                    ajax: {
                        url: "fetch.php",
                        type: "POST",
                    },
                });
 
                $("#dataku").on("draw.dt", function () {
                    $("#dataku").Tabledit({
                        url: "edit.php",
                        dataType: "json",
                        columns: {
                            identifier: [0, "id"],
                            editable: [
                                //[1, "nim"],
                                //[2, "namam"],
                                //[3, "jnsm", '{"1":"L","2":"P"}'],
								//[4, "jurusanm"],
                                //[5, "kelas"],
                                [6, "kehadiran"],
                                [7, "tugasm"],
                                [8, "utsm"],
                                [9, "uasm"],
                            ],
                        },
                        restoreButton: false,
                        onSuccess: function (data, textStatus, jqXHR) {
                            if (data.action == "delete") {
                                $("#" + data.id).remove();
                                $("#dataku").DataTable().ajax.reload();
                            }
                        },
                    });
                });
            });
        </script>

<!DOCTYPE html>

<head>

<title>MAHASISWA AKADEMIK</title>

</head>

<body>

<br>

<div class="panel-heading"><b>NILAI MAHASISWA</b></div>

<thead>

<tr>

<th>JNSKEL</th>

<th>JURUSAN</th>

<th>KELAS</th>

<th>KEHADIRAN</th>

<th>TUGAS</th>

</tr>

</thead>

</table>

</div>

</div>

</div>

</body>

</html>

$(document).ready(function () {

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

"language":{

"url":"https://cdn.datatables.net/plug-ins/1.10.21/i18n/Indonesian.json",

"sEmptyTable":"Tidads"

processing: true,

serverSide: true,

order: [],

ajax: {

url: "fetch.php",

type: "POST",

});

$("#dataku").on("draw.dt", function () {

$("#dataku").Tabledit({

url: "edit.php",

dataType: "json",

columns: {

identifier: [0, "id"],

editable: [

//[1, "nim"],

//[2, "namam"],

//[3, "jnsm", '{"1":"L","2":"P"}'],

//[4, "jurusanm"],

//[5, "kelas"],

[6, "kehadiran"],

[7, "tugasm"],

[8, "utsm"],

[9, "uasm"],

restoreButton: false,

onSuccess: function (data, textStatus, jqXHR) {

if (data.action == "delete") {

$("#" + data.id).remove();

$("#dataku").DataTable().ajax.reload();

}

});

</script>

Pastikan kamu terkoneksi dengan jaringan internet, karena semua komponen css dan javascript akan berbentuk link url yang otomatis mendownload.

Sekarang coba jalankan pada browser project yang kamu buat. Maka kamu akan mendapatkan hasil seperti berikut ini :

Diatas ketika kamu melakukan edit, maka otomatis langsung terbentuk form dalam datatables kita. ketika kamu klik simpan maka data langsung akan terupdate.

Sekarang coba kamu kembangkan fiturnya lebih lagi. berikan fitur tambah data. Karena saya tidak membuat fitur tambah data untuk kasus ini.

Sekian, semoga bermanfaat buat kamu yang lagi belajar web programming.

Initekno.com

Membuat CRUD Dengan JQuery Tabledit + Datatables ServerSide + Bootstrap dan PDO

Membuat Tabel

Menulis Baris Kode

Tinggalkan Balasan Batalkan balasan

Initekno.com

Informasi

Kategori

Arsip Blog