How to Add Patient Appointment Booking to Your Hospital Management System in PHP and MySQL
The Hospital Management System from Codezips manages patients and doctors. This tutorial adds the most-requested missing feature: a patient appointment booking module with time slot selection, double-booking prevention using a composite unique key, and a doctor dashboard showing today’s appointments — all in plain PHP and MySQL.
The most common question about the Hospital Management System project is: “How do I add appointments?” Patients need to book a specific doctor on a specific date at a specific time. The system needs to prevent two patients from booking the same doctor at the same time. And doctors need a view showing who they are seeing today and when.
This tutorial builds all three parts. The approach is deliberately simple — no external calendar library, no JavaScript framework — just clean PHP, MySQL, and the Bootstrap grid for layout. You can have this running in your existing project in about two hours.
Interactive Demo — Try the Slot Picker
This is exactly what the booking form will look like. Green slots are available; red are already booked. Click a green slot to select it:
📅 Appointment Slot Picker — Dr. Sarah Ahmed, Cardiology
Step 1 — Database Tables for Appointments
Add these two tables to your existing hospital database. The appointment_slots table defines when each doctor is available. The appointments table records booked appointments with a composite unique key that prevents double-booking at the database level.
SQL Run in phpMyAdmin — adds appointment tables to your hospital project
-- Table 1: Define which time slots each doctor offers each day of week
-- day_of_week: 1=Monday ... 7=Sunday (PHP date('N') format)
CREATE TABLE IF NOT EXISTS `doctor_slots` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`doctor_id` INT UNSIGNED NOT NULL,
`day_of_week` TINYINT NOT NULL COMMENT '1=Mon 2=Tue ... 7=Sun',
`slot_time` TIME NOT NULL COMMENT 'e.g. 09:00:00',
`duration_min`INT DEFAULT 30 COMMENT 'Appointment duration in minutes',
UNIQUE KEY uq_doctor_slot (`doctor_id`, `day_of_week`, `slot_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- Table 2: Booked appointments
-- UNIQUE KEY on (doctor_id, appt_date, slot_time) is the double-booking guard
CREATE TABLE IF NOT EXISTS `appointments` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`patient_id` INT UNSIGNED NOT NULL,
`doctor_id` INT UNSIGNED NOT NULL,
`appt_date` DATE NOT NULL,
`slot_time` TIME NOT NULL,
`reason` VARCHAR(500) DEFAULT NULL,
`status` ENUM('pending','confirmed','cancelled','completed') DEFAULT 'pending',
`notes` TEXT DEFAULT NULL COMMENT 'Doctor notes after appointment',
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-- THE KEY CONSTRAINT: one doctor can only have one booking per time slot per day
UNIQUE KEY uq_no_double_book (`doctor_id`, `appt_date`, `slot_time`),
CONSTRAINT fk_appt_patient FOREIGN KEY (`patient_id`)
REFERENCES `patients`(`id`) ON DELETE CASCADE,
CONSTRAINT fk_appt_doctor FOREIGN KEY (`doctor_id`)
REFERENCES `doctors`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- Seed some example slots for doctor ID 1 (Mon-Fri, 9am-12pm every 30 mins)
-- Adapt to match your doctors table IDs
INSERT INTO `doctor_slots` (`doctor_id`, `day_of_week`, `slot_time`) VALUES
(1,1,'09:00'),(1,1,'09:30'),(1,1,'10:00'),(1,1,'10:30'),(1,1,'11:00'),(1,1,'11:30'),
(1,2,'09:00'),(1,2,'09:30'),(1,2,'10:00'),(1,2,'10:30'),(1,2,'11:00'),(1,2,'11:30'),
(1,3,'09:00'),(1,3,'09:30'),(1,3,'10:00'),(1,3,'10:30'),(1,3,'11:00'),(1,3,'11:30'),
(1,4,'09:00'),(1,4,'09:30'),(1,4,'10:00'),(1,4,'10:30'),(1,4,'11:00'),(1,4,'11:30'),
(1,5,'09:00'),(1,5,'09:30'),(1,5,'10:00'),(1,5,'10:30'),(1,5,'11:00'),(1,5,'11:30');UNIQUE KEY uq_no_double_book (doctor_id, appt_date, slot_time), two patients clicking “Book” at exactly the same moment for the same doctor/time would both succeed. The PHP check runs before the INSERT, but in a race condition both checks pass before either INSERT completes. The database-level unique key is the only guarantee — it will reject one of the concurrent INSERTs with a duplicate key error no matter what.
Step 2 — Get Available Slots for a Doctor on a Date
This PHP function returns all time slots a doctor offers on a given date, with each slot marked as “available” or “booked”. Used to populate the booking form.
PHP appointments/get_slots.php — returns available slots as JSON (AJAX endpoint)
<?php
require_once '../config/dbconnection.php';
header('Content-Type: application/json');
$doctor_id = (int)($_GET['doctor_id'] ?? 0);
$appt_date = $_GET['appt_date'] ?? ''; // Format: YYYY-MM-DD
if (!$doctor_id || !preg_match('/^\d{4}-\d{2}-\d{2}$/', $appt_date)) {
echo json_encode(['error' => 'Invalid parameters']); exit();
}
// Get day of week for the requested date (1=Monday ... 7=Sunday)
$dow = (int)date('N', strtotime($appt_date));
// Get all slots this doctor offers on that day of week
$slots_stmt = mysqli_prepare($conn,
"SELECT slot_time FROM doctor_slots
WHERE doctor_id = ? AND day_of_week = ?
ORDER BY slot_time ASC");
mysqli_stmt_bind_param($slots_stmt, 'ii', $doctor_id, $dow);
mysqli_stmt_execute($slots_stmt);
$all_slots = mysqli_fetch_all(mysqli_stmt_get_result($slots_stmt), MYSQLI_ASSOC);
// Get already-booked slots for this doctor on this date
$booked_stmt = mysqli_prepare($conn,
"SELECT slot_time FROM appointments
WHERE doctor_id = ? AND appt_date = ?
AND status NOT IN ('cancelled')"); // Cancelled slots become available again
mysqli_stmt_bind_param($booked_stmt, 'is', $doctor_id, $appt_date);
mysqli_stmt_execute($booked_stmt);
$booked_rows = mysqli_fetch_all(mysqli_stmt_get_result($booked_stmt), MYSQLI_ASSOC);
// Build a lookup of booked times for fast O(1) checking
$booked_times = array_column($booked_rows, 'slot_time');
// Build the response with availability status for each slot
$result = [];
foreach ($all_slots as $slot) {
$result[] = [
'time' => $slot['slot_time'],
'display' => date('g:i A', strtotime($slot['slot_time'])), // "9:00 AM"
'available' => !in_array($slot['slot_time'], $booked_times)
];
}
echo json_encode(['slots' => $result, 'date' => $appt_date]);Step 3 — Book an Appointment (PHP Form Processing)
PHP appointments/book.php — form + processing with duplicate guard
<?php
require_once '../auth/auth_check.php';
require_once '../config/dbconnection.php';
$error = '';
$success = '';
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
$patient_id = (int)($_POST['patient_id'] ?? 0);
$doctor_id = (int)($_POST['doctor_id'] ?? 0);
$appt_date = trim($_POST['appt_date'] ?? '');
$slot_time = trim($_POST['slot_time'] ?? '');
$reason = trim($_POST['reason'] ?? '');
// Basic validation
if (!$patient_id || !$doctor_id
|| !preg_match('/^\d{4}-\d{2}-\d{2}$/', $appt_date)
|| !preg_match('/^\d{2}:\d{2}(:\d{2})?$/', $slot_time)) {
$error = 'Please fill in all required fields correctly.';
} elseif ($appt_date < date('Y-m-d')) {
$error = 'Appointment date cannot be in the past.';
} else {
// PHP-level duplicate check (fast, before the INSERT attempt)
$chk = mysqli_prepare($conn,
"SELECT id FROM appointments
WHERE doctor_id=? AND appt_date=? AND slot_time=?
AND status NOT IN ('cancelled') LIMIT 1");
mysqli_stmt_bind_param($chk, 'iss',
$doctor_id, $appt_date, $slot_time);
mysqli_stmt_execute($chk);
mysqli_stmt_store_result($chk);
if (mysqli_stmt_num_rows($chk) > 0) {
$error = 'This time slot has just been booked by another patient.
Please select a different time.';
} else {
// Insert — MySQL UNIQUE KEY is the final safety net for race conditions
$ins = mysqli_prepare($conn,
"INSERT INTO appointments
(patient_id, doctor_id, appt_date, slot_time, reason, status)
VALUES (?, ?, ?, ?, ?, 'pending')");
mysqli_stmt_bind_param($ins, 'iisss',
$patient_id, $doctor_id,
$appt_date, $slot_time, $reason);
if (mysqli_stmt_execute($ins)) {
$success = 'Appointment booked successfully! Status: Pending confirmation.';
} elseif (mysqli_errno($conn) === 1062) {
// Error 1062 = Duplicate entry (MySQL unique key violation)
$error = 'This time slot was just taken. Please choose another.';
} else {
$error = 'Booking failed. Please try again.';
error_log(mysqli_error($conn));
}
}
}
}
?>HTML Appointment booking form
<!-- After the PHP block above, include this HTML form -->
<div class="card shadow-sm">
<div class="card-header bg-primary text-white">📅 Book an Appointment</div>
<div class="card-body">
<?php if ($error): ?>
<div class="alert alert-danger"><?= htmlspecialchars($error) ?></div>
<?php endif ?>
<?php if ($success): ?>
<div class="alert alert-success"><?= htmlspecialchars($success) ?></div>
<?php endif ?>
<form method="POST">
<div class="row g-3">
<div class="col-md-6">
<label class="form-label">Select Doctor</label>
<select name="doctor_id" id="doctorSelect" class="form-select"
onchange="loadSlots()" required>
<option value="">— Choose Doctor —</option>
<?php
$docs = mysqli_query($conn, "SELECT id, name, specialization FROM doctors ORDER BY name");
while ($d = mysqli_fetch_assoc($docs)):
?>
<option value="<?= $d['id'] ?>">
Dr. <?= htmlspecialchars($d['name']) ?> — <?= htmlspecialchars($d['specialization']) ?>
</option>
<?php endwhile ?>
</select>
</div>
<div class="col-md-6">
<label class="form-label">Appointment Date</label>
<input type="date" name="appt_date" id="apptDate" class="form-control"
min="<?= date('Y-m-d', strtotime('+1 day')) ?>"
onchange="loadSlots()" required>
</div>
<div class="col-12">
<label class="form-label">Available Time Slots</label>
<div id="slotsContainer" class="text-muted small">
Select a doctor and date to see available slots
</div>
<input type="hidden" name="slot_time" id="slotTimeInput" required>
</div>
<div class="col-md-6">
<label class="form-label">Patient</label>
<select name="patient_id" class="form-select" required>
<option value="">— Select Patient —</option>
<?php
$pats = mysqli_query($conn, "SELECT id, name FROM patients ORDER BY name");
while ($p = mysqli_fetch_assoc($pats)):
?>
<option value="<?= $p['id'] ?>"><?= htmlspecialchars($p['name']) ?></option>
<?php endwhile ?>
</select>
</div>
<div class="col-md-6">
<label class="form-label">Reason for Visit (optional)</label>
<input type="text" name="reason" class="form-control"
placeholder="e.g. Chest pain, follow-up, general checkup">
</div>
<div class="col-12">
<button type="submit" class="btn btn-primary">Book Appointment</button>
</div>
</div>
</form>
</div>
</div>
<script>
function loadSlots() {
const did = document.getElementById('doctorSelect').value;
const date = document.getElementById('apptDate').value;
const container = document.getElementById('slotsContainer');
document.getElementById('slotTimeInput').value = '';
if (!did || !date) return;
container.innerHTML = '<span class="text-muted">Loading slots...</span>';
fetch('/appointments/get_slots.php?doctor_id=' + did + '&appt_date=' + date)
.then(r => r.json())
.then(data => {
if (!data.slots || data.slots.length === 0) {
container.innerHTML = '<span class="text-muted">No slots available on this day.</span>';
return;
}
container.innerHTML = data.slots.map(s => `
<button type="button" class="btn btn-sm me-2 mb-2 ${s.available ? 'btn-outline-success' : 'btn-outline-danger disabled'}"
${s.available ? 'onclick="selectSlot(this,\'' + s.time + '\')"' : 'disabled'}>
${s.display} ${s.available ? '' : '(Booked)'}
</button>`).join('');
});
}
function selectSlot(btn, time) {
document.querySelectorAll('#slotsContainer .btn').forEach(b => b.classList.remove('btn-success','btn-outline-success'));
btn.classList.add('btn-success');
document.getElementById('slotTimeInput').value = time;
}
</script>Step 4 — Doctor’s Dashboard: Today’s Appointments
PHP appointments/doctor_today.php — today’s appointments for logged-in doctor
<?php
require_once '../auth/auth_check.php';
require_once '../config/dbconnection.php';
// Assumes $_SESSION['doctor_id'] is set when a doctor logs in
// Adjust to match your session variable names
$doctor_id = (int)$_SESSION['doctor_id'];
$today = date('Y-m-d');
$stmt = mysqli_prepare($conn,
"SELECT a.id, a.slot_time, a.reason, a.status,
p.name AS patient_name, p.phone AS patient_phone,
p.date_of_birth
FROM appointments a
JOIN patients p ON a.patient_id = p.id
WHERE a.doctor_id = ? AND a.appt_date = ?
AND a.status != 'cancelled'
ORDER BY a.slot_time ASC");
mysqli_stmt_bind_param($stmt, 'is', $doctor_id, $today);
mysqli_stmt_execute($stmt);
$appointments = mysqli_fetch_all(mysqli_stmt_get_result($stmt), MYSQLI_ASSOC);
?>
<h5>Today's Appointments — <?= date('l, d F Y') ?></h5>
<?php if (empty($appointments)): ?>
<div class="alert alert-info">No appointments scheduled for today.</div>
<?php else: ?>
<div class="table-responsive">
<table class="table table-bordered table-hover">
<thead class="table-primary">
<tr><th>Time</th><th>Patient</th><th>Reason</th><th>Status</th><th>Action</th></tr>
</thead>
<tbody>
<?php foreach ($appointments as $a): ?>
<tr>
<td><strong><?= date('g:i A', strtotime($a['slot_time'])) ?></strong></td>
<td><?= htmlspecialchars($a['patient_name']) ?><br>
<small class="text-muted"><?= htmlspecialchars($a['patient_phone'] ?? '—') ?></small></td>
<td><?= htmlspecialchars($a['reason'] ?? 'Not specified') ?></td>
<td>
<span class="badge bg-<?= $a['status']==='confirmed' ? 'success' : ($a['status']==='completed' ? 'primary' : 'warning text-dark') ?>">
<?= ucfirst($a['status']) ?>
</span>
</td>
<td>
<a href="update_status.php?id=<?= $a['id'] ?>&status=confirmed" class="btn btn-sm btn-success">✓ Confirm</a>
<a href="update_status.php?id=<?= $a['id'] ?>&status=completed" class="btn btn-sm btn-primary">✓ Done</a>
</td>
</tr>
<?php endforeach ?>
</tbody>
</table>
</div>
<?php endif ?>Step 5 — Status Update Handler
PHP appointments/update_status.php — change appointment status
<?php
require_once '../auth/auth_check.php';
require_once '../config/dbconnection.php';
$id = (int)($_GET['id'] ?? 0);
$status = $_GET['status'] ?? '';
$allowed = ['pending', 'confirmed', 'cancelled', 'completed'];
if ($id && in_array($status, $allowed)) {
$stmt = mysqli_prepare($conn,
"UPDATE appointments SET status=? WHERE id=?");
mysqli_stmt_bind_param($stmt, 'si', $status, $id);
mysqli_stmt_execute($stmt);
}
header('Location: doctor_today.php'); exit();Appointment Status Flow
Click each status badge to understand when it applies and what transitions are allowed:
Frequently Asked Questions
What if two patients try to book the same slot at exactly the same time?
This is a race condition. The PHP-level check runs before the INSERT, but if two requests arrive simultaneously, both checks pass before either INSERT completes. This is exactly why the UNIQUE KEY constraint on (doctor_id, appt_date, slot_time) is essential — MySQL will allow only one of the two concurrent INSERTs to succeed. The second will fail with error code 1062. The PHP catches this specific error code and shows the user a message asking them to choose another slot. This is the industry-standard two-layer approach used in professional booking systems.
How do I make cancelled slots available for rebooking?
The get_slots.php file already handles this — it filters booked slots with WHERE status NOT IN ('cancelled'). When an appointment is cancelled, its row in the appointments table remains (for records) but with status = ‘cancelled’. The next time a patient loads the slot picker for that doctor and date, the cancelled slot appears as available because it is excluded from the “booked slots” query. No additional code is needed.
How do I add this booking form to the patient’s login area?
If your hospital system has a patient login (separate from admin), include the booking form in the patient dashboard and pre-populate the patient_id with $_SESSION['patient_id'] rather than showing a dropdown. Hide the patient selector from the form entirely for patient-side booking — patients should only be able to book for themselves. The doctor and admin views can still see the full dropdown to book on behalf of a patient.
The base project this tutorial extends
Next extension tutorial in this series
Show appointment statistics on your admin dashboard
Email patients when their appointment is confirmed
Last updated April 2026. Tested on PHP 8.2 / MySQL 8.0. UNIQUE KEY duplicate prevention verified against InnoDB concurrent INSERT behaviour.
