Penerapan SQL dalam PHP
sekolah.sql
-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Jun 03, 2025 at 06:09 AM
-- Server version: 10.4.32-MariaDB
-- PHP Version: 8.2.12
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Database: `sekolah`
--
-- --------------------------------------------------------
--
-- Table structure for table `guru`
--
CREATE TABLE `guru` (
`nip` int(11) NOT NULL,
`nama` char(50) DEFAULT NULL,
`alamat` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
--
-- Dumping data for table `guru`
--
INSERT INTO `guru` (`nip`, `nama`, `alamat`) VALUES
(101, 'Pak Joko', 'Jakarta'),
(102, 'Bu Sari', 'Bekasi');
-- --------------------------------------------------------
--
-- Table structure for table `siswa`
--
CREATE TABLE `siswa` (
`nis` int(11) NOT NULL,
`nama` char(50) DEFAULT NULL,
`nip` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
--
-- Dumping data for table `siswa`
--
INSERT INTO `siswa` (`nis`, `nama`, `nip`) VALUES
(1, 'Budi Karta', 101),
(2, 'Anton', 102),
(3, 'Kurnia', 101);
-- --------------------------------------------------------
--
-- Table structure for table `users`
--
CREATE TABLE `users` (
`id` int(11) NOT NULL,
`username` varchar(50) NOT NULL,
`password` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
--
-- Dumping data for table `users`
--
INSERT INTO `users` (`id`, `username`, `password`) VALUES
(1, 'admin', '0192023a7bbd73250516f069df18b500');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `guru`
--
ALTER TABLE `guru`
ADD PRIMARY KEY (`nip`);
--
-- Indexes for table `siswa`
--
ALTER TABLE `siswa`
ADD PRIMARY KEY (`nis`),
ADD KEY `nip` (`nip`);
--
-- Indexes for table `users`
--
ALTER TABLE `users`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
--
-- Constraints for dumped tables
--
--
-- Constraints for table `siswa`
--
ALTER TABLE `siswa`
ADD CONSTRAINT `siswa_ibfk_1` FOREIGN KEY (`nip`) REFERENCES `guru` (`nip`);
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
Buat file db.php
<?php
$koneksi = mysqli_connect("localhost", "root", "", "sekolah");
if (!$koneksi) {
die("Koneksi gagal: " . mysqli_connect_error());
}
?>
Buat file index.php
<?php
session_start();
if (!isset($_SESSION['login'])) {
header("Location: login.php");
exit;
}
include 'db.php';
$cari = isset($_GET['cari']) ? $_GET['cari'] : '';
?>
<!DOCTYPE html>
<html>
<head>
<title>Data Siswa & Guru</title>
<link rel="stylesheet" href="assets/css/bootstrap.min.css">
</head>
<body class="container mt-4">
<h3>Data Siswa & Guru Pembimbing</h3>
<a href="tambah_siswa.php" class="btn btn-success mb-2">+ Tambah Siswa</a>
<a href="tambah_guru.php" class="btn btn-secondary mb-2">+ Tambah Guru</a>
<a href="cetak_pdf.php" class="btn btn-danger mb-2" target="_blank">Cetak
PDF</a>
<a href="logout.php" class="btn btn-outline-dark mb-2 float-end">Logout</a>
<form method="GET" class="mb-2">
<input type="text" name="cari" placeholder="Cari nama siswa..." value="<?=
$cari ?>" class="form-control" />
</form>
<table class="table table-bordered table-striped">
<thead class="table-dark">
<tr>
<th>NIS</th>
<th>Nama Siswa</th>
<th>Nama Guru</th>
<th>Alamat Guru</th>
<th>Aksi</th>
</tr>
</thead>
<tbody>
<?php
$sql = "SELECT siswa.nis, siswa.nama AS nama_siswa, guru.nama AS
nama_guru, guru.alamat
FROM siswa
JOIN guru ON siswa.nip = guru.nip
WHERE siswa.nama LIKE '%$cari%'";
$query = mysqli_query($koneksi, $sql);
while ($row = mysqli_fetch_assoc($query)) {
?>
<tr>
<td><?= $row['nis'] ?></td>
<td><?= $row['nama_siswa'] ?></td>
<td><?= $row['nama_guru'] ?></td>
<td><?= $row['alamat'] ?></td>
<td>
<a href="edit_siswa.php?nis=<?= $row['nis'] ?>" class="btn btn-
warning btn-sm">Edit</a>
<a href="hapus_siswa.php?nis=<?= $row['nis'] ?>" class="btn btn-
danger btn-sm" onclick="return confirm('Hapus data ini?')">Hapus</a>
</td>
</tr>
<?php } ?>
</tbody>
</table>
</body>
</html>
Buat file login.php
<?php
session_start();
include 'db.php';
if (isset($_POST['login'])) {
$username = $_POST['username'];
$password = md5($_POST['password']);
$query = mysqli_query($koneksi, "SELECT * FROM users WHERE
username='$username' AND password='$password'");
if (mysqli_num_rows($query) > 0) {
$_SESSION['login'] = true;
header("Location: index.php");
exit; // Penting: tambahkan exit setelah header redirect
} else {
$error = "Username atau password salah!";
}
}
?>
<!DOCTYPE html>
<html>
<head>
<title>Login</title>
<link
href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css"
rel="stylesheet" integrity="sha384-
QWTKZyjpPEjISv5WaRU9OFeRpok6YctnYmDr5pNlyT2bRjXh0JMhjY6hW+ALEwIH"
crossorigin="anonymous">
</head>
<body class="bg-light"> <div class="container d-flex justify-content-center
align-items-center min-vh-100">
<div class="row">
<div class="col md-8"> <div class="card p-4 shadow-sm"> <h3
class="text-center mb-4">Login</h3>
<?php if (isset($error)) echo "<div class='alert alert-
danger'>$error</div>"; ?>
<form method="POST">
<div class="mb-3">
<label for="username" class="form-
label">Username</label>
<input type="text" name="username" id="username"
class="form-control" required>
</div>
<div class="mb-3">
<label for="password" class="form-
label">Password</label>
<input type="password" name="password"
id="password" class="form-control" required>
</div>
<div class="d-grid gap-2"> <button class="btn btn-
primary" name="login">Login</button>
</div>
</form>
</div>
</div>
</div>
</div>
<script
src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min
.js" integrity="sha384-
YvpcrYf0tY3lHB60NNkmXc5s9fDVZLESaAA55NDzOxhy9GkcSanxUaRkZqS6jC5/JbU"
crossorigin="anonymous"></script>
</body>
</html>
Buat file logout.php
<?php
session_start();
session_destroy();
header("Location: login.php");
?>
Buat file tambah_siswa.php
<?php include 'db.php'; ?>
<!DOCTYPE html>
<html>
<head>
<title>Tambah Siswa</title>
<link rel="stylesheet" href="assets/css/bootstrap.min.css">
</head>
<body class="container mt-4">
<h3>Form Tambah Siswa</h3>
<form method="POST" action="">
<div class="mb-3">
<label>NIS</label>
<input type="number" name="nis" class="form-control" required>
</div>
<div class="mb-3">
<label>Nama Siswa</label>
<input type="text" name="nama" class="form-control" required>
</div>
<div class="mb-3">
<label>Guru Pembimbing</label>
<select name="nip" class="form-control" required>
<option value="">-- Pilih Guru --</option>
<?php
$res = mysqli_query($koneksi, "SELECT * FROM guru");
while ($g = mysqli_fetch_assoc($res)) {
echo "<option value='$g[nip]'>$g[nama]</option>";
}
?>
</select>
</div>
<button class="btn btn-primary" name="simpan">Simpan</button>
<a href="index.php" class="btn btn-secondary">Kembali</a>
</form>
<?php
if (isset($_POST['simpan'])) {
$nis = $_POST['nis'];
$nama = $_POST['nama'];
$nip = $_POST['nip'];
mysqli_query($koneksi, "INSERT INTO siswa (nis, nama, nip) VALUES ('$nis',
'$nama', '$nip')");
echo "<script>alert('Data siswa
disimpan!');window.location='index.php';</script>";
}
?>
</body>
</html>
Buat file edit_siswa.php
<?php include 'db.php'; ?>
<!DOCTYPE html>
<html>
<head>
<title>Tambah Siswa</title>
<link rel="stylesheet" href="assets/css/bootstrap.min.css">
</head>
<body class="container mt-4">
<h3>Form Tambah Siswa</h3>
<form method="POST" action="">
<div class="mb-3">
<label>NIS</label>
<input type="number" name="nis" class="form-control" required>
</div>
<div class="mb-3">
<label>Nama Siswa</label>
<input type="text" name="nama" class="form-control" required>
</div>
<div class="mb-3">
<label>Guru Pembimbing</label>
<select name="nip" class="form-control" required>
<option value="">-- Pilih Guru --</option>
<?php
$res = mysqli_query($koneksi, "SELECT * FROM guru");
while ($g = mysqli_fetch_assoc($res)) {
echo "<option value='$g[nip]'>$g[nama]</option>";
}
?>
</select>
</div>
<button class="btn btn-primary" name="simpan">Simpan</button>
<a href="index.php" class="btn btn-secondary">Kembali</a>
</form>
<?php
if (isset($_POST['simpan'])) {
$nis = $_POST['nis'];
$nama = $_POST['nama'];
$nip = $_POST['nip'];
mysqli_query($koneksi, "INSERT INTO siswa (nis, nama, nip) VALUES ('$nis',
'$nama', '$nip')");
echo "<script>alert('Data siswa
disimpan!');window.location='index.php';</script>";
}
?>
</body>
</html>
Buat file hapus_siswa.php
<?php
include 'db.php';
$nis = $_GET['nis'];
mysqli_query($koneksi, "DELETE FROM siswa WHERE nis='$nis'");
header("Location: index.php");
?>
Buat file tambah_guru.php
<?php include 'db.php'; ?>
<!DOCTYPE html>
<html>
<head>
<title>Tambah Guru</title>
<link rel="stylesheet" href="assets/css/bootstrap.min.css">
</head>
<body class="container mt-4">
<h3>Form Tambah Guru</h3>
<form method="POST" action="">
<div class="mb-3">
<label>NIP</label>
<input type="number" name="nip" class="form-control" required>
</div>
<div class="mb-3">
<label>Nama Guru</label>
<input type="text" name="nama" class="form-control" required>
</div>
<div class="mb-3">
<label>Alamat</label>
<input type="text" name="alamat" class="form-control" required>
</div>
<button class="btn btn-primary" name="simpan">Simpan</button>
<a href="index.php" class="btn btn-secondary">Kembali</a>
</form>
<?php
if (isset($_POST['simpan'])) {
$nip = $_POST['nip'];
$nama = $_POST['nama'];
$alamat = $_POST['alamat'];
mysqli_query($koneksi, "INSERT INTO guru (nip, nama, alamat) VALUES ('$nip',
'$nama', '$alamat')");
echo "<script>alert('Data guru
disimpan!');window.location='index.php';</script>";
}
?>
</body>
</html>
Buat file cetak_pdf.php
<?php
define('FPDF_FONTPATH', 'font/'); // <--- Tambahkan ini
require('fpdf.php');
include 'db.php';
$pdf = new FPDF();
$pdf->AddPage();
$pdf->SetFont('Arial','B',14);
$pdf->Cell(0,10,'Data Siswa dan Guru Pembimbing',0,1,'C');
$pdf->Ln(5);
$pdf->SetFont('Arial','B',10);
$pdf->Cell(20,8,'NIS',1);
$pdf->Cell(50,8,'Nama Siswa',1);
$pdf->Cell(50,8,'Nama Guru',1);
$pdf->Cell(60,8,'Alamat Guru',1);
$pdf->Ln();
$sql = "SELECT siswa.nis, siswa.nama AS nama_siswa, guru.nama AS nama_guru,
guru.alamat
FROM siswa
JOIN guru ON siswa.nip = guru.nip";
$query = mysqli_query($koneksi, $sql);
$pdf->SetFont('Arial','',10);
while ($row = mysqli_fetch_assoc($query)) {
$pdf->Cell(20,8,$row['nis'],1);
$pdf->Cell(50,8,$row['nama_siswa'],1);
$pdf->Cell(50,8,$row['nama_guru'],1);
$pdf->Cell(60,8,$row['alamat'],1);
$pdf->Ln();
}
$pdf->Output();
?>
Buat file fpdf.php
Jika folder font/ belum ada, kamu bisa unduh dari https://www.fpdf.org
Download terkait fpdf.php dengan file asli dari fpdf.org agar fitur PDF berfungsi sempurna
Pilih untuk download file v1.86 (2023-06-25) ZIP
Kemudian extract file zipnya, akan terlihat isi foldernya sebagai berikut :
Kemudian copy folder font:
• Folder font/ ada dalam direktori yang sama dengan fpdf.php
• File seperti arial.php, helvetica.php, dll. berada dalam folder font/
Berikut struktur direktori yang ada terkait project diatas :
Berikut tampilan login.php :
Berikut tampilan index.php jika login berhasil :
Berikut tampilan tambah_siswa.php
Berikut tampilan edit_siswa.php
Berikut tampilan cetak_pdf.php
Silahkan kerjakan project, kemudian buat didalam hosting Anda masing-masing.
