- Teknik Informatika

Membuat CRUD dengan php dan database MySQL (koneksi Mysqli)

Tools yang digunakan :

XAMPP
Text Editor
Internet
Plugin jQuery Validation

Langkah-langkah

membuat folder di dalam htdocs agar bisa diakses pada localhost.
Buka text editor dan buka folder yang telah dibuat.
Download plugin jQuery, Bootstrap 3, font-awesome, jQuery Validation. Copy semua file plugin pada folder assets.

Membuat Database

Akses localhost/phpmyadmin pada browser, kemudian buat database dengan nama mhs. seperti pada gambar berikut :

Buat file koneksi.php

File koneksi.php berfungsi untuk membuat dan mengecek koneksi ke database. Ketikkan code seperti berikut:

Isikan server, username, dan password sesuai konfigurasi yang digunakan. Untuk nama database sesuaikan dengan nama database yang telah dibuat, misal di sini saya menggunakan nama mhs.

Buat file index.php

Ketikkan kode seperti berikut ini:

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<link rel="stylesheet" href="assets/css/bootstrap.min.css">
<link rel="stylesheet" href="assets/font-awesome/css/font-awesome.min.css">
<link rel="stylesheet" type="text/css" href="assets/css/bootstrap.css">
<script type="text/javascript" src="assets/js/jquery.js"></script>
<script type="text/javascript" src="assets/js/bootstrap.js"></script>
<script type="text/javascript" src="assets/js/bootstrap.min.js"></script>
<script type="text/javascript" src="assets/js/jquery.validate.min.js"></script>
<title> Data Mahasiswa </title>
</head>
<body>
<?php
include 'header.php';
?>
<div class="container">
<br>
<h2>Data Mahasiswa</h2>
<hr>
<br>
<div class="container">
<div class="row justify-content-between">
<div class="col-auto .mr-auto">
<a href="tambah.php" class="btn btn-success"><i class="fa fa-plus"></i> Tambah Data</a>
</div>
</div>
</div>
<br>


<div class="table-responsive">
<table class="table table-striped table-bordered table-hover" id="tb-mahasiswa">
<thead>
<tr>
<th>No</th>
<th>Nama</th>
<th>Username</th>
<th>Password</th>
<th>Email</th>
<th>Menu</th>
</tr>
</thead>
<tbody>
<?php
require_once('koneksi.php');

$no = 1;
$koneksiObj = new koneksi();
$koneksi = $koneksiObj->getKoneksi();

if($koneksi->connect_error){
echo "Gagal Koneksi : ". $koneksi->connect_error;
echo "</td></tr>";
}
$query = "select * from mhs order by nama";
$data = $koneksi->query($query);
if($data->num_rows <= 0){
echo "<tr>";
echo "<td colspan='7' class='text-center'><i>Tidak ada data</i></td>";
echo "</tr>";
} else{
$sql = mysqli_query($koneksi, "SELECT * FROM mhs"); // jika tidak ada filter maka tampilkan semua entri
if(mysqli_num_rows($sql) == 0){
echo '<tr><td colspan="14">Data Tidak Ada.</td></tr>'; // jika tidak ada entri di database maka tampilkan 'Data Tidak Ada.'
}else{ // jika terdapat entri maka tampilkan datanya
while($row = mysqli_fetch_assoc($sql)){ // fetch query yang sesuai ke dalam array
echo "<tr>";
echo "<td>".$no++."</td>";
echo "<td class='center'>".$row['nama']."</td>";
echo "<td class='center'>".$row['username']."</td>";
echo "<td class='center'>".$row['password']."</td>";
echo "<td class='center'>".$row['email']."</td>";
echo '<td class="text-center"><a href="edit.php?id='.$row['id'].'" class="btn btn-info btn-sm"><i class="fa fa-pencil"></i> Edit</a>';
echo '
<a href="hapus.php?id='.$row['id'].'" class="btn btn-danger btn-sm delete-link"><i class="fa fa-trash"></i> Hapus</a></td>';
echo "</tr>";
}
}
}
?>
</tbody>
</table>
</div>
</div>

<?php
include 'footer.php';
?>

</body>
</html>

Tampilan index.php

Buat file tambah.php

Berikut kode untuk membuat form tambah data:

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<link rel="stylesheet" href="assets/css/bootstrap.min.css">
<link rel="stylesheet" href="assets/font-awesome/css/font-awesome.min.css">
<link rel="stylesheet" type="text/css" href="assets/css/bootstrap.css">
<script type="text/javascript" src="assets/js/jquery.js"></script>
<script type="text/javascript" src="assets/js/bootstrap.js"></script>
<script type="text/javascript" src="assets/js/bootstrap.min.js"></script>
<script type="text/javascript" src="assets/js/jquery.validate.min.js"></script>
<title> Data Mahasiswa </title>
</head>

<body>
<?php
include ('header.php');
?>


<div class="container">
<br>
<h2>Data Mahasiswa <i class="fa fa-angle-double-right"></i> Tambah Data</h2>
<hr>
<br>

<form id="inputdata" class="form-horizontal" method="post" action="simpan.php">
<div class="form-group">
<label for="nim">Nama</label>
<div>
<input type="text" class="form-control" id="nama" name="nama">
</div>
</div>

<div class="form-group">
<label for="nama">Username</label>
<div>
<input type="text" class="form-control" id="username" name="username">
</div>
</div>

<div class="form-group">
<label for="nama">Password</label>
<div>
<input type="text" class="form-control" id="password" name="password">
</div>
</div>

<div class="form-group">
<label for="nama">Email</label>
<div>
<input type="text" class="form-control" id="email" name="email">
</div>
</div>

<div class="form-group">
<div>
<button type="submit" class="btn btn-success"><i class="fa fa-save"></i> Simpan</button>
<a href="index.php" class="btn btn-danger"><i class="fa fa-ban"></i> Batal</a>
</div>
</div>
</form>
</div>


<?php
include 'footer.php';
?>

<script language="javascript">
$.validator.addMethod("alpha", function(value, element){
return this.optional(element) || value.match(/^[a-zA-Z\s]+$/);
});
$("#inputdata").validate({
rules: {
username: "required",
nama: {
required: true,
alpha: true
},
email: {
required: true,
email: true
},
password: {
required: true,
minlength: 8
}
},
message: {
username: "Masukkan username anda!",
nama: {
required: "Masukkan nama anda!",
alpha: "Inputan hanya diperbolehkan huruf dan spasi!"
},
email: "Masukkan email anda yang valid!",
password: {
required: "Masukkan password anda!",
minlength: "Password minimal 8 karakter!"
}
},
submitHandler: function(form) {
form.submit();
}
});
</script>

</body>

</html>

Pada code di atas terdapat form validation. Sebelum data hasil inputan disimpan, maka perlu dilakukan validasi terlebih dahulu. Tujuannya supaya data yang berhasil disimpan adalah data yang valid sesuai apa yang diharapkan. Ada beberapa yang perlu divalidasi untuk form di atas, seperti :
1. Field nama hanya boleh diisikan huruf alfabet dan spasi.
2. Validasi untuk email.
3. Validasi untuk password.
4. Semua field pada form tersebut tidak boleh kosong.
5. Kesamaan username dan email.

Buat file simpan.php
ketikan kode sebagai berikut :

<link rel="stylesheet" href="assets/css/bootstrap.min.css">
<link rel="stylesheet" href="assets/font-awesome/css/font-awesome.min.css">
<link rel="stylesheet" type="text/css" href="assets/css/bootstrap.css">
<script type="text/javascript" src="assets/js/jquery.js"></script>
<script type="text/javascript" src="assets/js/bootstrap.js"></script>
<script type="text/javascript" src="assets/js/bootstrap.min.js"></script>

<div class="container">
<?php
require_once('koneksi.php');
$koneksiObj = new Koneksi();
$koneksi = $koneksiObj->getKoneksi();

if($koneksi->connect_error){
echo "Gagal Koneksi : ". $koneksi->connect_error;
}

$nama = $_POST['nama'];
$username = $_POST['username'];
$password = $_POST['password'];
$email = $_POST['email'];

if($nama=='' || $username=='' || $password=='' || $email==''){
echo "Data yang dimasukkan salah. Pastikan semua form telah terisi!<br>";
echo '<a href="tambah.php">Kembali</a>';
return;
}

$query1 = "select * from mhs where username='$username' or email='$email'";
$count = $koneksi->query($query1);
if($count->num_rows > 0){
echo "Username atau Email sudah terdaftar, mohon periksa kembali!";
echo '<a href="tambah.php">Kembali</a>';
return;
}

$query = "insert into mhs (nama, username, password, email) values('$nama', '$username', '$password', '$email')";

if($koneksi->query($query)===true){
echo '<br><div class="alert alert-success alert-dismissable">Data '.$nama.' berhasil disimpan. <a href="index.php"> Lihat Data</a></div>';
} else{
echo '<br><div class="alert alert-danger alert-dismissable">Data gagal disimpan! Kembali ke <a href="index.php"> Halaman Awal</a></div>';
}

$koneksi->close();
?>

</div>

Buat file edit.php
Ketikkan code berikut ini pada file edit.php :

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<link rel="stylesheet" href="assets/css/bootstrap.min.css">
<link rel="stylesheet" href="assets/font-awesome/css/font-awesome.min.css">
<link rel="stylesheet" type="text/css" href="assets/css/bootstrap.css">
<script type="text/javascript" src="assets/js/jquery.js"></script>
<script type="text/javascript" src="assets/js/bootstrap.js"></script>
<script type="text/javascript" src="assets/js/bootstrap.min.js"></script>
<script type="text/javascript" src="assets/js/jquery.validate.min.js"></script>
<title> Data Mahasiswa </title>
</head>

<body>
<?php
include ('header.php');
?>

<?php
require_once('koneksi.php');

if($_GET['id']!=null){
$id = $_GET['id'];
$koneksiObj = new Koneksi();
$koneksi = $koneksiObj->getKoneksi();

if($koneksi->connect_error){
echo "Gagal Koneksi : ". $koneksi->connect_error;
}

$query = "select * from mhs where id='$id'";
$data = $koneksi->query($query);
}

if($data->num_rows <= 0){
echo "Data tidak ditemukan!";
} else{
while($row = $data->fetch_assoc()){
$id = $row['id'];
$nama = $row['nama'];
$username = $row['username'];
$password = $row['password'];
$email = $row['email'];
}
}
?>

<div class="container">
<br>
<h2>Data Mahasiswa <i class="fa fa-angle-double-right"></i> Edit Data</h2>
<hr>
<br>


<form id="editdata" class="form-horizontal" method="post" action="update.php">
<input type="hidden" class="form-control" id="id" name="id" value="<?php echo $id;?>">
<div class="form-group">
<label for="nim">Nama</label>
<div>
<input type="text" class="form-control" id="nama" name="nama" value="<?php echo $nama;?>">
</div>
</div>

<div class="form-group">
<label for="nama">Username</label>
<div>
<input type="text" class="form-control" id="username" name="username" value="<?php echo $username;?>">
</div>
</div>

<div class="form-group">
<label for="nama">Password</label>
<div>
<input type="text" class="form-control" id="password" name="password" value="<?php echo $password;?>">
</div>
</div>

<div class="form-group">
<label for="nama">Email</label>
<div>
<input type="text" class="form-control" id="email" name="email" value="<?php echo $email;?>">
</div>
</div>

<div class="form-group">
<div>
<button type="submit" class="btn btn-success"><i class="fa fa-save"></i> Simpan</button>
<a href="index.php" class="btn btn-danger"><i class="fa fa-ban"></i> Batal</a>
</div>
</div>
</form>

</div>

<?php
include 'footer.php';
?>

<script language="javascript">
$.validator.addMethod("alpha", function(value, element){
return this.optional(element) || value.match(/^[a-zA-Z\s]+$/);
});
$("#editdata").validate({
rules: {
username: "required",
nama: {
required: true,
alpha: true
},
email: {
required: true,
email: true
},
password: {
required: true,
minlength: 8
}
},
message: {
username: "Masukkan username anda!",
nama: {
required: "Masukkan nama anda!",
alpha: "Inputan hanya diperbolehkan huruf dan spasi!"
},
email: "Masukkan email anda yang valid!",
password: {
required: "Masukkan password anda!",
minlength: "Password minimal 8 karakter!"
}
},
submitHandler: function(form) {
form.submit();
}
});
</script>

</body>

</html>

Tampilan edit.php

Buat file update.php

Ketikkan code berikut ini pada file update.php:

<?php

require_once('koneksi.php');

$koneksiObj = new Koneksi();

$koneksi = $koneksiObj->getKoneksi();

if($koneksi->connect_error){

echo "Gagal Koneksi : ". $koneksi->connect_error;

}

$id = $_POST['id'];

$nama = $_POST['nama'];

$username = $_POST['username'];

$password = $_POST['password'];

$email = $_POST['email'];

if($id=='' || $nama=='' || $username=='' || $password=='' || $email==''){

echo "Gagal update, pastikan semua kolom di form telah terisi!<br>";

echo '<a href="index.php">kembali</a>';

return;

}

$query1 = "select * from mhs where username='$username' or email='$email'";

$count = $koneksi->query($query1);

if($count->num_rows > 0){

echo "Username atau Email sudah terdaftar, mohon periksa kembali!";

echo '<a href="index.php">Kembali ke halaman awal</a>';

return;

}

$query = "update mhs set nama='$nama', username='$username', password='$password', email='$email' where id='$id'";

if($koneksi->query($query)===true){

echo '<br><div class="alert alert-success alert-dismissable">Data '.$nama.' berhasil diupdate. <a href="index.php"> Lihat Data</a></div>';

} else{

echo '<br><div class="alert alert-danger alert-dismissable">Data gagal diupdate! Kembali ke <a href="index.php"> Halaman Awal</a></div>';

}

$koneksi->close();

</div>

Buat file hapus.php

Ketikkan code berikut ini pada file hapus.php:

<?php

include "koneksi.php";

$koneksiObj = new Koneksi();

$koneksi = $koneksiObj->getKoneksi();

$query = "DELETE FROM mhs WHERE id=" . $_GET["id"];

if($koneksi->query($query) === true) {

echo '<br><div class="alert alert-success alert-dismissable">Data berhasil dihapus <a href="index.php"> Lihat Data</a></div>';

} else {

echo '<br><div class="alert alert-danger alert-dismissable">Data gagal dihapus! Kembali ke <a href="index.php"> Halaman Awal</a></div>';

}

</div>

Source code dapat diunduh di:
https://github.com/adityaaditiya/tugas-web-programing-2

Cari Blog Ini

Arsip Blog

Laporkan Penyalahgunaan

Mengenai Saya

Teknik Informatika

Rabu, 25 September 2019

Tidak ada komentar:

Posting Komentar

Popular

Recent