How to develop Projects(HMS) Using PHP and Mysql Part 12

Rashmi Mishra
personRashmi Mishra
April 19, 2025
0

 

How to develop Projects (HMS)

Using PHP and Mysql 

Part 12


🏥 Hospital Management System

💡 Module 5: Appointment Management

✅ Purpose:

This module enables hospital staff and patients to book, edit, and manage appointments with doctors.
It ensures smooth coordination between patient visits and doctor schedules.

CREATE TABLE appointments (

    id INT AUTO_INCREMENT PRIMARY KEY,
    patient_id INT,
    doctor_id INT,
    appointment_date DATE,
    appointment_time TIME,
    reason TEXT,
    status ENUM('Scheduled', 'Completed', 'Cancelled'),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (patient_id) REFERENCES patients(id),
    FOREIGN KEY (doctor_id) REFERENCES doctors(id)
);

📂 Project Folder Structure

/hospital_management_system/
├── /appointments/
│   ├── add_appointment.php        # Form to book a new appointment
│   ├── insert_appointment.php     # PHP logic to save appointment data to DB
│   ├── list_appointments.php      # Display all appointments
│   ├── edit_appointment.php       # Edit appointment details
│   └── delete_appointment.php     # Cancel or remove an appointment

💡 Appointment Module — Key Features

✅ 1️⃣ Book Appointment

  • Select Patient Name (Dropdown from patients table).
  • Select Doctor Name (Dropdown from doctors table).
  • Choose:
    • Appointment Date.
    • Appointment Time.
    • Reason for Visit.
  • Auto-set Status to "Scheduled".

✅ 2️⃣ Edit Appointment

  • Modify:
    • Appointment Date and Time.
    • Assigned Doctor.
    • Reason for visit.
    • Status: Scheduled / Completed / Cancelled.

✅ 3️⃣ Delete Appointment

  • Cancel or permanently delete appointments.
  • Usually done when:
    • A patient cancels.
    • A doctor becomes unavailable.
    • Duplicate or incorrect entry.

✅ 4️⃣ View Appointment List

  • Table with:
    • Patient Name.
    • Doctor Name.
    • Date & Time.
    • Status.
  • Features:
    • Filter by Date, Doctor, Patient, or Status.
    • Sort by newest, upcoming, or patient name.
    • Pagination support.

✅ 5️⃣ Appointment Status Update

  • Change status after consultation:
    • Scheduled → Completed
    • Scheduled → Cancelled (if appointment was missed or canceled).

✅ 6️⃣ Optional: Patient Notification System

  • Trigger email or SMS:
    • Appointment confirmation.
    • Appointment reminder.
    • Cancellation alerts.

✅ This module simplifies the doctor-patient meeting system and avoids overlapping or double-booking, especially useful for clinics and hospitals that handle large numbers of patients.

💡 Module 6: Billing and Payments Management

✅ Purpose:

This module allows the hospital to manage patient bills by generating invoices for services, treatments, and medicines. It also tracks payments, outstanding amounts, and billing history.

🗄️ Suggested MySQL Tables — billing and payments

1.   Billing Table

CREATE TABLE billing (
    id INT AUTO_INCREMENT PRIMARY KEY,
    patient_id INT,
    doctor_id INT,
    total_amount DECIMAL(10, 2),
    treatment_cost DECIMAL(10, 2),
    medication_cost DECIMAL(10, 2),
    other_charges DECIMAL(10, 2),
    discount DECIMAL(10, 2),
    final_amount DECIMAL(10, 2),
    billing_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('Unpaid', 'Paid'),
    FOREIGN KEY (patient_id) REFERENCES patients(id),
    FOREIGN KEY (doctor_id) REFERENCES doctors(id)
);

2.   Payments Table

CREATE TABLE payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    billing_id INT,
    payment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    amount_paid DECIMAL(10, 2),
    payment_method ENUM('Cash', 'Credit Card', 'Debit Card', 'Online'),
    status ENUM('Pending', 'Completed'),
    FOREIGN KEY (billing_id) REFERENCES billing(id)
);

📂 Project Folder Structure

/hospital_management_system/
├── /billing/
│   ├── generate_bill.php         # Form to generate new patient bill
│   ├── view_bill.php             # View a generated bill
│   ├── record_payment.php        # Process payments
│   ├── view_payments.php         # Display payment records
│   ├── update_payment_status.php # Update payment status (Paid/Unpaid)
│   └── billing_history.php       # View billing history for patients

💡 Billing and Payments Module — Key Features

✅ 1️⃣ Generate New Bill

  • Calculate the total bill based on:
    • Doctor’s consultation fee.
    • Treatment costs.
    • Medication charges.
    • Additional hospital charges (e.g., room rent, tests).
    • Apply discounts if applicable.
  • The final amount is auto-calculated after applying discounts.
  • Status: Set to Unpaid by default.
  • Create Bill ID for each transaction.

✅ 2️⃣ Record Payments

  • Payments for a particular bill can be recorded.
    • Specify amount paid, payment method (Cash, Card, etc.), and payment status (Pending/Completed).
  • Link each payment to a billing record using Billing ID.
  • Update the bill status once full payment is made.

✅ 3️⃣ Update Payment Status

  • Automatically update the payment status of the bill based on payment records:
    • Pending: If no payment has been made yet.
    • Completed: If full payment has been made.

✅ 4️⃣ View Billing History

  • View detailed billing history for each patient:
    • Doctor and treatment details.
    • Charges and payment history.
    • Pending and completed payments.
  • This helps keep track of all medical expenses.

✅ 5️⃣ View Payment History

  • Display all payments made for each bill:
    • Payment method (Cash, Card, Online).
    • Payment status (Completed, Pending).
    • Amount paid and remaining balance.

✅ 6️⃣ Generate Invoice

  • Generate downloadable invoices in PDF or HTML format for patient payments.
  • Include:
    • Patient and doctor information.
    • Treatment charges and breakdown.
    • Payment status and amounts.

✅ 7️⃣ Optional: Financial Reporting

  • Track hospital revenue with:
    • Total bill amounts generated.
    • Total payments received.
    • Outstanding amounts.
    • Generate financial reports for hospital management.

✅ This module streamlines the billing process, reduces errors, and ensures a smooth payment cycle for the hospital. It is especially useful for hospitals and clinics managing large volumes of patients and transactions.

💡 Module 7: Doctor Management

✅ Purpose:

This module enables hospital staff to manage doctor records, including details like specialization, schedule, and availability for appointments. It ensures smooth coordination between doctors and patients.

Code in detail

🗄️ Database Table Example (appointments)

CREATE TABLE appointments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    patient_id INT,
    doctor_id INT,
    appointment_date DATE,
    appointment_time TIME,
    reason TEXT,
    status ENUM('Scheduled', 'Completed', 'Cancelled') DEFAULT 'Scheduled',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

📌 1️⃣ add_appointment.php

(Form to book a new appointment)

<?php
// Fetch patients and doctors from the database for dropdowns
$conn = new mysqli("localhost", "root", "", "hospital_db");
$patients = $conn->query("SELECT id, full_name FROM patients");
$doctors = $conn->query("SELECT id, full_name FROM doctors");
?>
 
<h2>Book New Appointment</h2>
<form action="insert_appointment.php" method="POST">
    <label>Patient:</label>
    <select name="patient_id" required>
        <?php while($p = $patients->fetch_assoc()): ?>
            <option value="<?= $p['id']; ?>"><?= $p['full_name']; ?></option>
        <?php endwhile; ?>
    </select><br>
 
    <label>Doctor:</label>
    <select name="doctor_id" required>
        <?php while($d = $doctors->fetch_assoc()): ?>
            <option value="<?= $d['id']; ?>"><?= $d['full_name']; ?></option>
        <?php endwhile; ?>
    </select><br>
 
    <label>Date:</label>
    <input type="date" name="appointment_date" required><br>
 
    <label>Time:</label>
    <input type="time" name="appointment_time" required><br>
 
    <label>Reason:</label>
    <textarea name="reason" required></textarea><br>
 
    <button type="submit">Book Appointment</button>
</form>

📌 2️⃣ insert_appointment.php

(PHP logic to save data)

<?php
$conn = new mysqli("localhost", "root", "", "hospital_db");
 
$patient_id = $_POST['patient_id'];
$doctor_id = $_POST['doctor_id'];
$appointment_date = $_POST['appointment_date'];
$appointment_time = $_POST['appointment_time'];
$reason = $_POST['reason'];
 
$sql = "INSERT INTO appointments (patient_id, doctor_id, appointment_date, appointment_time, reason) 
        VALUES ('$patient_id', '$doctor_id', '$appointment_date', '$appointment_time', '$reason')";
 
if ($conn->query($sql) === TRUE) {
    echo "Appointment booked successfully!";
} else {
    echo "Error: " . $conn->error;
}
$conn->close();
?>
<a href="list_appointments.php">View Appointments</a>

📌 3️⃣ list_appointments.php

(Display all appointments)

<?php
$conn = new mysqli("localhost", "root", "", "hospital_db");
$sql = "SELECT a.id, p.full_name AS patient_name, d.full_name AS doctor_name, 
               a.appointment_date, a.appointment_time, a.reason, a.status 
        FROM appointments a 
        JOIN patients p ON a.patient_id = p.id 
        JOIN doctors d ON a.doctor_id = d.id";
$result = $conn->query($sql);
?>
 
<h2>All Appointments</h2>
<table border="1">
    <tr>
        <th>ID</th>
        <th>Patient</th>
        <th>Doctor</th>
        <th>Date</th>
        <th>Time</th>
        <th>Reason</th>
        <th>Status</th>
        <th>Actions</th>
    </tr>
    <?php while($row = $result->fetch_assoc()): ?>
        <tr>
            <td><?= $row['id']; ?></td>
            <td><?= $row['patient_name']; ?></td>
            <td><?= $row['doctor_name']; ?></td>
            <td><?= $row['appointment_date']; ?></td>
            <td><?= $row['appointment_time']; ?></td>
            <td><?= $row['reason']; ?></td>
            <td><?= $row['status']; ?></td>
            <td>
                <a href="edit_appointment.php?id=<?= $row['id']; ?>">Edit</a> |
                <a href="delete_appointment.php?id=<?= $row['id']; ?>" onclick="return confirm('Are you sure?');">Delete</a>
            </td>
        </tr>
    <?php endwhile; ?>
</table>

📌 4️⃣ edit_appointment.php

(Edit appointment details)

<?php
$conn = new mysqli("localhost", "root", "", "hospital_db");
$id = $_GET['id'];
$result = $conn->query("SELECT * FROM appointments WHERE id = $id");
$appointment = $result->fetch_assoc();
?>
 
<h2>Edit Appointment</h2>
<form action="update_appointment.php" method="POST">
    <input type="hidden" name="id" value="<?= $appointment['id']; ?>">
    <label>Date:</label>
    <input type="date" name="appointment_date" value="<?= $appointment['appointment_date']; ?>" required><br>
 
    <label>Time:</label>
    <input type="time" name="appointment_time" value="<?= $appointment['appointment_time']; ?>" required><br>
 
    <label>Reason:</label>
    <textarea name="reason" required><?= $appointment['reason']; ?></textarea><br>
 
    <label>Status:</label>
    <select name="status" required>
        <option <?= $appointment['status'] == 'Scheduled' ? 'selected' : ''; ?>>Scheduled</option>
        <option <?= $appointment['status'] == 'Completed' ? 'selected' : ''; ?>>Completed</option>
        <option <?= $appointment['status'] == 'Cancelled' ? 'selected' : ''; ?>>Cancelled</option>
    </select><br>
 
    <button type="submit">Update Appointment</button>
</form>

📌 update_appointment.php

(Handles the update — bonus!)

<?php
$conn = new mysqli("localhost", "root", "", "hospital_db");
$id = $_POST['id'];
$date = $_POST['appointment_date'];
$time = $_POST['appointment_time'];
$reason = $_POST['reason'];
$status = $_POST['status'];
 
$sql = "UPDATE appointments SET 
            appointment_date = '$date',
            appointment_time = '$time',
            reason = '$reason',
            status = '$status' 
        WHERE id = $id";
 
if ($conn->query($sql) === TRUE) {
    echo "Appointment updated successfully!";
} else {
    echo "Error updating: " . $conn->error;
}
$conn->close();
?>
<a href="list_appointments.php">Back to Appointments</a>

📌 5️⃣ delete_appointment.php

(Cancel or remove an appointment)

<?php
$conn = new mysqli("localhost", "root", "", "hospital_db");
$id = $_GET['id'];
$sql = "DELETE FROM appointments WHERE id = $id";
 
if ($conn->query($sql) === TRUE) {
    echo "Appointment deleted successfully!";
} else {
    echo "Error deleting: " . $conn->error;
}
$conn->close();
?>
<a href="list_appointments.php">Back to Appointments</a>

🗄️ Example Billing Table Schema

CREATE TABLE bills (
    id INT AUTO_INCREMENT PRIMARY KEY,
    patient_id INT,
    total_amount DECIMAL(10,2),
    payment_status ENUM('Paid', 'Unpaid') DEFAULT 'Unpaid',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
CREATE TABLE payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    bill_id INT,
    payment_amount DECIMAL(10,2),
    payment_date DATE,
    method VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

1️⃣ generate_bill.php

(Form to generate new patient bill)

<?php
$conn = new mysqli("localhost", "root", "", "hospital_db");
$patients = $conn->query("SELECT id, full_name FROM patients");
?>
 
<h2>Generate Patient Bill</h2>
<form action="insert_bill.php" method="POST">
    <label>Patient:</label>
    <select name="patient_id" required>
        <?php while($p = $patients->fetch_assoc()): ?>
            <option value="<?= $p['id']; ?>"><?= $p['full_name']; ?></option>
        <?php endwhile; ?>
    </select><br>
 
    <label>Total Amount:</label>
    <input type="number" name="total_amount" step="0.01" required><br>
 
    <button type="submit">Generate Bill</button>
</form>

🔹 insert_bill.php

(Insert bill into database)

<?php
$conn = new mysqli("localhost", "root", "", "hospital_db");
$patient_id = $_POST['patient_id'];
$total_amount = $_POST['total_amount'];
 
$sql = "INSERT INTO bills (patient_id, total_amount) VALUES ('$patient_id', '$total_amount')";
 
if ($conn->query($sql) === TRUE) {
    echo "Bill generated successfully!";
} else {
    echo "Error: " . $conn->error;
}
$conn->close();
?>
<a href="view_bill.php">View Bills</a>

2️⃣ view_bill.php

(View generated bills)

<?php
$conn = new mysqli("localhost", "root", "", "hospital_db");
$result = $conn->query("SELECT b.id, p.full_name, b.total_amount, b.payment_status, b.created_at 
                        FROM bills b JOIN patients p ON b.patient_id = p.id");
?>
 
<h2>Generated Bills</h2>
<table border="1">
    <tr>
        <th>Bill ID</th>
        <th>Patient</th>
        <th>Total Amount</th>
        <th>Status</th>
        <th>Date</th>
        <th>Action</th>
    </tr>
    <?php while($row = $result->fetch_assoc()): ?>
        <tr>
            <td><?= $row['id']; ?></td>
            <td><?= $row['full_name']; ?></td>
            <td><?= $row['total_amount']; ?></td>
            <td><?= $row['payment_status']; ?></td>
            <td><?= $row['created_at']; ?></td>
            <td>
                <a href="record_payment.php?bill_id=<?= $row['id']; ?>">Record Payment</a> |
                <a href="update_payment_status.php?bill_id=<?= $row['id']; ?>">Update Status</a>
            </td>
        </tr>
    <?php endwhile; ?>
</table>

3️⃣ record_payment.php

(Payment form)

<?php
$bill_id = $_GET['bill_id'];
?>
 
<h2>Record Payment for Bill #<?= $bill_id; ?></h2>
<form action="save_payment.php" method="POST">
    <input type="hidden" name="bill_id" value="<?= $bill_id; ?>">
    <label>Amount:</label>
    <input type="number" name="payment_amount" step="0.01" required><br>
 
    <label>Date:</label>
    <input type="date" name="payment_date" required><br>
 
    <label>Method:</label>
    <input type="text" name="method" required><br>
 
    <button type="submit">Record Payment</button>
</form>

🔹 save_payment.php

(Save payment to database)

<?php
$conn = new mysqli("localhost", "root", "", "hospital_db");
 
$bill_id = $_POST['bill_id'];
$amount = $_POST['payment_amount'];
$date = $_POST['payment_date'];
$method = $_POST['method'];
 
$sql = "INSERT INTO payments (bill_id, payment_amount, payment_date, method) 
        VALUES ('$bill_id', '$amount', '$date', '$method')";
 
if ($conn->query($sql) === TRUE) {
    echo "Payment recorded successfully!";
} else {
    echo "Error: " . $conn->error;
}
$conn->close();
?>
<a href="view_payments.php">View Payments</a>

4️⃣ view_payments.php

(List of payment records)

<?php
$conn = new mysqli("localhost", "root", "", "hospital_db");
$result = $conn->query("SELECT p.id, p.bill_id, p.payment_amount, p.payment_date, p.method, p.created_at 
                        FROM payments p");
?>
 
<h2>All Payments</h2>
<table border="1">
    <tr>
        <th>ID</th>
        <th>Bill ID</th>
        <th>Amount</th>
        <th>Date</th>
        <th>Method</th>
        <th>Recorded At</th>
    </tr>
    <?php while($row = $result->fetch_assoc()): ?>
        <tr>
            <td><?= $row['id']; ?></td>
            <td><?= $row['bill_id']; ?></td>
            <td><?= $row['payment_amount']; ?></td>
            <td><?= $row['payment_date']; ?></td>
            <td><?= $row['method']; ?></td>
            <td><?= $row['created_at']; ?></td>
        </tr>
    <?php endwhile; ?>
</table>

5️⃣ update_payment_status.php

(Manually update payment status)

<?php
$conn = new mysqli("localhost", "root", "", "hospital_db");
$bill_id = $_GET['bill_id'];
 
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $status = $_POST['status'];
    $conn->query("UPDATE bills SET payment_status='$status' WHERE id=$bill_id");
    echo "Status updated!";
} else {
    $bill = $conn->query("SELECT payment_status FROM bills WHERE id=$bill_id")->fetch_assoc();
}
?>
 
<h2>Update Payment Status</h2>
<form method="POST">
    <label>Status:</label>
    <select name="status">
        <option value="Paid" <?= ($bill['payment_status'] == 'Paid') ? 'selected' : ''; ?>>Paid</option>
        <option value="Unpaid" <?= ($bill['payment_status'] == 'Unpaid') ? 'selected' : ''; ?>>Unpaid</option>
    </select><br>
    <button type="submit">Update</button>
</form>

6️⃣ billing_history.php

(View all billing records by patient)

<?php
$conn = new mysqli("localhost", "root", "", "hospital_db");
$result = $conn->query("SELECT p.full_name, b.id, b.total_amount, b.payment_status, b.created_at 
                        FROM bills b JOIN patients p ON b.patient_id = p.id 
                        ORDER BY p.full_name, b.created_at DESC");
?>
 
<h2>Billing History</h2>
<table border="1">
    <tr>
        <th>Patient</th>
        <th>Bill ID</th>
        <th>Total Amount</th>
        <th>Status</th>
        <th>Date</th>
    </tr>
    <?php while($row = $result->fetch_assoc()): ?>
        <tr>
            <td><?= $row['full_name']; ?></td>
            <td><?= $row['id']; ?></td>
            <td><?= $row['total_amount']; ?></td>
            <td><?= $row['payment_status']; ?></td>
            <td><?= $row['created_at']; ?></td>
        </tr>
    <?php endwhile; ?>
</table>

 


Tags

You Might Like

Show more
php-hms

Post a Comment

0Comments

Post a Comment (0)
Share to other apps
Copy Post Link