Doctor Appointment Management System with PHP & MySQL

In our previous tutorial we have developed Library Management System with PHP & MySQL. In this tutorial we will develop Online Doctor Appointment Management System with PHP and MySQL.

Online Doctor Appointment Management System is a web application that allows administrator to manage doctors, manage patients and manage appointments.

Here we will develop a live example of Online Doctor Appointment Management System and cover following.

The Administrator will do the following:

Manage Doctors.
Manage Patients.
Manage Appointment.
Manage Users

So let’s implement Online Doctor Appointment Management System. The file structure are:

doctor-appointment-management-system
- config
  - database.php
- class
  - User.php
  - Doctor.php
  - Patient.php
  - Appointment.php
  - Specialization.php
- js
  - doctor.js
  - patient.js
  - appointment.js
  - specialization.js
  - user.js
- index.php
- dashboard.php
- doctor.php
- patient.php
- appointment.php
- specialization.php
- user.php

Step1: MySQL Database Tables

First we will create MySQL database tables for our system.

We will create table hms_appointments to store appointment details.

CREATE TABLE `hms_appointments` (
  `id` int(11) NOT NULL,
  `patient_id` int(11) NOT NULL,
  `specialization_id` int(11) NOT NULL,
  `doctor_id` int(11) NOT NULL,
  `consultancy_fee` int(11) NOT NULL,
  `appointment_date` varchar(255) NOT NULL,
  `appointment_time` varchar(255) NOT NULL,
  `created` datetime NOT NULL DEFAULT current_timestamp(),
  `status` enum('Active','Cancelled','Completed','') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

we will create table hms_doctor to store doctor details.

CREATE TABLE `hms_doctor` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `address` text NOT NULL,
  `mobile` varchar(255) NOT NULL,
  `fee` int(11) NOT NULL,
  `specialization` int(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

we will create table hms_specialization to store doctor specialization details.

CREATE TABLE `hms_specialization` (
  `id` int(11) NOT NULL,
  `specialization` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

we will create table hms_patients to store patient details.

CREATE TABLE `hms_patients` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `gender` varchar(255) NOT NULL,
  `mobile` varchar(255) NOT NULL,
  `address` text NOT NULL,
  `age` int(11) NOT NULL,
  `medical_history` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

we will create table hms_slots to store time slots details of doctor availibility.

CREATE TABLE `hms_slots` (
  `id` int(11) NOT NULL,
  `slots` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

we will create table hms_users to store user login details.

CREATE TABLE `hms_users` (
  `id` int(11) UNSIGNED NOT NULL,
  `first_name` varchar(255) DEFAULT NULL,
  `last_name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `password` varchar(64) NOT NULL,
  `role` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Step2: Manage Doctors

We will create doctor.php file and create HTML to manage doctors details.

<div class="container-fluid">	
	<div class="row row-offcanvas row-offcanvas-left">		
		<div class="col-md-9 col-lg-10 main"> 
		<h2>Manage Doctor</h2> 
		<div class="panel-heading">
		<div class="row">
			<div class="col-md-10">
				<h3 class="panel-title"></h3>
			</div>
			<div class="col-md-2" align="right">
				<button type="button" id="addDoctor" class="btn btn-success" title="Add Doctor"><span class="glyphicon glyphicon-plus">Add</span></button>
			</div>
		</div>
	</div>
	<table id="doctorListing" class="table table-bordered table-striped">
		<thead>
			<tr>
				<th>#</th>
				<th>Name</th>					
				<th>Address</th>					
				<th>Mobile</th>
				<th>Fee</th>
				<th>Specialization</th>	
				<th></th>
				<th></th>	
				<th></th>					
			</tr>
		</thead>
	</table>
</div>

In doctor.js file, we will initialize DataTable and amake ajax request to doctor_action.php with action listDoctors to list doctors record.

var doctorRecords = $('#doctorListing').DataTable({
	"lengthChange": false,
	"processing":true,
	"serverSide":true,		
	"bFilter": false,
	'serverMethod': 'post',		
	"order":[],
	"ajax":{
		url:"doctor_action.php",
		type:"POST",
		data:{action:'listDoctors'},
		dataType:"json"
	},
	"columnDefs":[
		{
			"targets":[0, 6, 7, 8],
			"orderable":false,
		},
	],
	"pageLength": 10
});

In doctor_action.php file, we will include class Doctor.php and created object. Then we will check for action listDoctors and call method listDoctors() from class Doctor.php using object.

include_once 'class/Doctor.php';
$doctor = new Doctor();

if(!empty($_POST['action']) && $_POST['action'] == 'listDoctors') {
	$doctor->listDoctors();
}

now we will implement method listDoctors() in class Doctor.php to list doctors record.

public function listDoctors(){
		
	$sqlWhere = '';
	if($_SESSION["role"] == 'doctor') { 
		$sqlWhere = " WHERE doctor.id = '".$_SESSION["userid"]."'";
	}	
	
	$sqlQuery = "SELECT doctor.id, doctor.name, doctor.address, doctor.mobile, doctor.fee, specialization.specialization
	FROM ".$this->doctorTable." doctor
	LEFT JOIN ".$this->specializationTable." specialization ON specialization.id = doctor.specialization
	$sqlWhere ";
	
	if(!empty($_POST["search"]["value"])){
		$sqlQuery .= ' AND (doctor.id LIKE "%'.$_POST["search"]["value"].'%" ';
		$sqlQuery .= ' OR doctor.name LIKE "%'.$_POST["search"]["value"].'%" ';			
		$sqlQuery .= ' OR doctor.mobile LIKE "%'.$_POST["search"]["value"].'%" ';
		$sqlQuery .= ' OR doctor.address LIKE "%'.$_POST["search"]["value"].'%" ';
		$sqlQuery .= ' OR doctor.fee LIKE "%'.$_POST["search"]["value"].'%" ';
		$sqlQuery .= ' OR doctor.specialization LIKE "%'.$_POST["search"]["value"].'%") ';								
	}
	
	if(!empty($_POST["order"])){
		$sqlQuery .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
	} else {
		$sqlQuery .= 'ORDER BY id DESC ';
	}
	
	if($_POST["length"] != -1){
		$sqlQuery .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
	}
	
	$stmt = $this->conn->prepare($sqlQuery);
	$stmt->execute();
	$result = $stmt->get_result();	
	
	$stmtTotal = $this->conn->prepare("SELECT * FROM ".$this->doctorTable." $sqlWhere " );
	$stmtTotal->execute();
	$allResult = $stmtTotal->get_result();
	$allRecords = $allResult->num_rows;
	
	$displayRecords = $result->num_rows;
	$records = array();		
	while ($doctor = $result->fetch_assoc()) { 				
		$rows = array();			
		$rows[] = $doctor['id'];
		$rows[] = ucfirst($doctor['name']);
		$rows[] = $doctor['address'];		
		$rows[] = $doctor['mobile'];	
		$rows[] = $doctor['fee'];	
		$rows[] = $doctor['specialization'];						
		$rows[] = '<button type="button" name="view" id="'.$doctor["id"].'" class="btn btn-info btn-xs view"><span class="glyphicon glyphicon-file" title="View">View</span></button>';			
		$rows[] = '<button type="button" name="update" id="'.$doctor["id"].'" class="btn btn-warning btn-xs update"><span class="glyphicon glyphicon-edit" title="Edit">Edit</span></button>';
		$rows[] = '<button type="button" name="delete" id="'.$doctor["id"].'" class="btn btn-danger btn-xs delete" ><span class="glyphicon glyphicon-remove" title="Delete">Delete</span></button>';
		$records[] = $rows;
	}
	
	$output = array(
		"draw"	=>	intval($_POST["draw"]),			
		"iTotalRecords"	=> 	$displayRecords,
		"iTotalDisplayRecords"	=>  $allRecords,
		"data"	=> 	$records
	);
	
	echo json_encode($output);
}

we will also check for actions addDoctor and updateDoctor and call method insert() and update() to add and update doctor records.

if(!empty($_POST['action']) && $_POST['action'] == 'addDoctor') {	
	$doctor->name = $_POST["name"];
    $doctor->fee = $_POST["fee"];
    $doctor->specialization = $_POST["specialization"];
	$doctor->mobile = $_POST["mobile"];
	$doctor->address = $_POST["address"];
	$doctor->email = $_POST["email"];	
	$doctor->insert();
}
if(!empty($_POST['action']) && $_POST['action'] == 'updateDoctor') {
	$doctor->id = $_POST["id"];
	$doctor->name = $_POST["name"];
    $doctor->fee = $_POST["fee"];
    $doctor->specialization = $_POST["specialization"];
	$doctor->mobile = $_POST["mobile"];
	$doctor->address = $_POST["address"];
	$doctor->email = $_POST["email"];	
	$doctor->update();
}

we will implement method insert() and update() in class Doctor.php to add and update doctors details.

public function insert(){
		
	if($this->name) {

		$stmt = $this->conn->prepare("
		INSERT INTO ".$this->doctorTable."(`name`, `email`, `mobile`, `address`, `fee`,`specialization`)
		VALUES(?,?,?,?,?,?)");
	
		$this->name = htmlspecialchars(strip_tags($this->name));
		$this->email = htmlspecialchars(strip_tags($this->email));
		$this->mobile = htmlspecialchars(strip_tags($this->mobile));
		$this->address = htmlspecialchars(strip_tags($this->address));	
		$this->fee = htmlspecialchars(strip_tags($this->fee));	
		$this->specialization = htmlspecialchars(strip_tags($this->specialization));
				
		$stmt->bind_param("ssssis", $this->name, $this->email, $this->mobile, $this->address, $this->fee, $this->specialization);
		
		if($stmt->execute()){
			return true;
		}		
	}
}

public function update(){
	
	if($this->id) {		
					
		$stmt = $this->conn->prepare("
			UPDATE ".$this->doctorTable." 
			SET name= ?, email = ?, mobile = ?, address = ?, fee = ?, specialization = ? 
			WHERE id = ?");
 
		$this->id = htmlspecialchars(strip_tags($this->id));
		$this->name = htmlspecialchars(strip_tags($this->name));
		$this->email = htmlspecialchars(strip_tags($this->email));
		$this->mobile = htmlspecialchars(strip_tags($this->mobile));
		$this->address = htmlspecialchars(strip_tags($this->address));	
		$this->fee = htmlspecialchars(strip_tags($this->fee));	
		$this->specialization = htmlspecialchars(strip_tags($this->specialization));			
		
		$stmt->bind_param("ssssisi", $this->name, $this->email, $this->mobile, $this->address, $this->fee, $this->specialization, $this->id);
		
		if($stmt->execute()){
			return true;
		}
		
	}	
}

Step3: Manage Patients

We will create patient.php and create html to manage patients.

<div class="container-fluid">	
	<div class="row row-offcanvas row-offcanvas-left">			
		<div class="col-md-9 col-lg-10 main"> 
		<h2>Manage Patient</h2> 
	<div class="panel-heading">
		<div class="row">
			<div class="col-md-10">
				<h3 class="panel-title"></h3>
			</div>
			<?php if($_SESSION["role"] != 'patient') { ?>
			<div class="col-md-2" align="right">
				<button type="button" id="addPatient" class="btn btn-success" title="Add Patient"><span class="glyphicon glyphicon-plus">Add</span></button>
			</div>
			<?php } ?>
		</div>
	</div>
	<table id="patientListing" class="table table-bordered table-striped">
		<thead>
			<tr>
				<th>#</th>
				<th>Name</th>					
				<th>Gender</th>	
				<th>Age</th>	
				<th>Email</th>
				<th>Mobile</th>
				<th>Address</th>
				<th>Medical History</th>
				<th></th>
				<th></th>	
				<th></th>					
			</tr>
		</thead>
	</table>
</div>

In patient.js file, we will initialize DataTable and make ajax request to patient_action.php with action listPatient to list patient records.

var patientRecords = $('#patientListing').DataTable({
	"lengthChange": false,
	"processing":true,
	"serverSide":true,		
	"bFilter": false,
	'serverMethod': 'post',		
	"order":[],
	"ajax":{
		url:"patient_action.php",
		type:"POST",
		data:{action:'listPatient'},
		dataType:"json"
	},
	"columnDefs":[
		{
			"targets":[0, 8, 9, 10],
			"orderable":false,
		},
	],
	"pageLength": 10
});

In patient_action.php, we will check for action listPatient and call method listPatients() from class Patient.php using object.

include_once 'class/Patient.php';
$patient = new Patient();

if(!empty($_POST['action']) && $_POST['action'] == 'listPatient') {
	$patient->listPatients();
}

We will implement method listPatients() in class Patient.php list pateint records.

public function listPatients(){

	$sqlWhere = '';
	if($_SESSION["role"] == 'patient') { 
		$sqlWhere = "WHERE id = '".$_SESSION["userid"]."'";
	}		
	$sqlQuery = "SELECT * FROM ".$this->patientTable." $sqlWhere";
	if(!empty($_POST["search"]["value"])){
		$sqlQuery .= ' AND (name LIKE "%'.$_POST["search"]["value"].'%" ';			
		$sqlQuery .= ' OR email LIKE "%'.$_POST["search"]["value"].'%" ';
		$sqlQuery .= ' OR gender LIKE "%'.$_POST["search"]["value"].'%" ';
		$sqlQuery .= ' OR mobile LIKE "%'.$_POST["search"]["value"].'%" ';
		$sqlQuery .= ' OR address LIKE "%'.$_POST["search"]["value"].'%" ';				
		$sqlQuery .= ' OR age LIKE "%'.$_POST["search"]["value"].'%") ';							
	}
	
	if(!empty($_POST["order"])){
		$sqlQuery .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
	} else {
		$sqlQuery .= 'ORDER BY id DESC ';
	}
	
	if($_POST["length"] != -1){
		$sqlQuery .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
	}
	
	$stmt = $this->conn->prepare($sqlQuery);
	$stmt->execute();
	$result = $stmt->get_result();	
	
	$stmtTotal = $this->conn->prepare("SELECT * FROM ".$this->patientTable." $sqlWhere");
	$stmtTotal->execute();
	$allResult = $stmtTotal->get_result();
	$allRecords = $allResult->num_rows;
	
	$displayRecords = $result->num_rows;
	$records = array();		
	while ($patient = $result->fetch_assoc()) { 				
		$rows = array();			
		$rows[] = $patient['id'];
		$rows[] = ucfirst($patient['name']);
		$rows[] = $patient['gender'];
		$rows[] = $patient['age'];
		$rows[] = $patient['email'];
		$rows[] = $patient['mobile'];	
		$rows[] = $patient['address'];			
		$rows[] = $patient['medical_history'];			
		$rows[] = '<button type="button" name="view" id="'.$patient["id"].'" class="btn btn-info btn-xs view"><span class="glyphicon glyphicon-file" title="View">View</span></button>';			
		$rows[] = '<button type="button" name="update" id="'.$patient["id"].'" class="btn btn-warning btn-xs update"><span class="glyphicon glyphicon-edit" title="Edit">Edit</span></button>';
		if($_SESSION["role"] != 'patient') {
			$rows[] = '<button type="button" name="delete" id="'.$patient["id"].'" class="btn btn-danger btn-xs delete" ><span class="glyphicon glyphicon-remove" title="Delete">Delete</span></button>';
		} else {
			$rows[] = '';
		}
		$records[] = $rows;
	}
	
	$output = array(
		"draw"	=>	intval($_POST["draw"]),			
		"iTotalRecords"	=> 	$displayRecords,
		"iTotalDisplayRecords"	=>  $allRecords,
		"data"	=> 	$records
	);
	
	echo json_encode($output);
}

Step4: Manage Appointment

We will create appointment.php file and create HTML to manage appointments.

<div class="container-fluid">	
	<div class="row row-offcanvas row-offcanvas-left">			
		<div class="col-md-9 col-lg-10 main"> 
		<h2>Manage Appointment</h2> 
	<div class="panel-heading">
		<div class="row">
			<div class="col-md-10">
				<h3 class="panel-title"></h3>
			</div>
			<?php if($user->isAdmin()) { ?>
			<div class="col-md-2" align="right">
				<button type="button" id="createAppointment" class="btn btn-success" title="Create Appointment"><span class="glyphicon glyphicon-plus">Add</span></button>
			</div>
			<?php } ?>
		</div>
	</div>
	<table id="appointmentListing" class="table table-bordered table-striped">
		<thead>
			<tr>
				<th>#</th>
				<th>Patient</th>
				<th>Doctor</th>					
				<th>Specialization</th>	
				<th>Fee</th>	
				<th>Apointment Time</th>
				<th>Apointment Date</th>
				<th>Status</th>
				<th></th>
				<th></th>	
				<th></th>					
			</tr>
		</thead>
	</table>
</div>

In appointment.js file, we will initialize DataTable and make ajax request to appointment_action.php with action listAppointment to list appointment records.

var appointmentRecords = $('#appointmentListing').DataTable({
	"lengthChange": false,
	"processing":true,
	"serverSide":true,		
	"bFilter": false,
	'serverMethod': 'post',		
	"order":[],
	"ajax":{
		url:"appointment_action.php",
		type:"POST",
		data:{action:'listAppointment'},
		dataType:"json"
	},
	"columnDefs":[
		{
			"targets":[0, 8, 9, 10],
			"orderable":false,
		},
	],
	"pageLength": 10
});

In appointment_action.php, we will inlcude class Appointment.php and create object of it. We will check for action listAppointment and call method listAppointment() from appointment class.

include_once 'class/Appointment.php';

$appointment = new Appointment();

if(!empty($_POST['action']) && $_POST['action'] == 'listAppointment') {
	$appointment->listAppointment();
}

We will implement method listAppointment() in class Appointment.php to list and manage appointment records.

public function listAppointment(){
		
	$sqlWhere = '';
	if($_SESSION["role"] == 'patient') { 
		$sqlWhere = "WHERE a.patient_id = '".$_SESSION["userid"]."'";
	}	
	
	$sqlQuery = "SELECT a.id, d.name as doctor_name, s.specialization, a.consultancy_fee, appointment_date, a.appointment_time, a.created, a.status, p.name as patient_name, p.id as patient_id, slot.slots
		FROM ".$this->appointmentTable." a 
		LEFT JOIN ".$this->doctorTable." d ON a.doctor_id = d.id
		LEFT JOIN ".$this->patientsTable." p ON a.patient_id = p.id
		LEFT JOIN ".$this->slotsTable." slot ON slot.id = a.appointment_time
		LEFT JOIN ".$this->specializationTable." s ON a.specialization_id = s.id $sqlWhere ";
		
		
	if(!empty($_POST["search"]["value"])){
		$sqlQuery .= ' AND (a.id LIKE "%'.$_POST["search"]["value"].'%" ';
		$sqlQuery .= ' OR d.name LIKE "%'.$_POST["search"]["value"].'%" ';			
		$sqlQuery .= ' OR s.specialization LIKE "%'.$_POST["search"]["value"].'%" ';
		$sqlQuery .= ' OR a.consultancy_fee LIKE "%'.$_POST["search"]["value"].'%" ';
		$sqlQuery .= ' OR a.appointment_date LIKE "%'.$_POST["search"]["value"].'%" ';
		$sqlQuery .= ' OR a.appointment_time LIKE "%'.$_POST["search"]["value"].'%") ';					
	}
	
	if(!empty($_POST["order"])){
		$sqlQuery .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
	} else {
		$sqlQuery .= 'ORDER BY a.id DESC ';
	}
	
	if($_POST["length"] != -1){
		$sqlQuery .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
	}		
	$stmt = $this->conn->prepare($sqlQuery);
	$stmt->execute();
	$result = $stmt->get_result();	
	
	$stmtTotal = $this->conn->prepare("SELECT * FROM ".$this->appointmentTable." as a $sqlWhere");
	$stmtTotal->execute();
	$allResult = $stmtTotal->get_result();
	$allRecords = $allResult->num_rows;
	
	$displayRecords = $result->num_rows;
	$records = array();		
	while ($appointment = $result->fetch_assoc()) { 				
		$rows = array();			
		$rows[] = $appointment['id'];
		$rows[] = ucfirst($appointment['patient_name']);
		$rows[] = ucfirst($appointment['doctor_name']);
		$rows[] = $appointment['specialization'];
		$rows[] = $appointment['consultancy_fee'];
		$rows[] = $appointment['slots'];
		$rows[] = $appointment['appointment_date'];	
		$rows[] = $appointment['status'];					
		$rows[] = '<button type="button" name="view" id="'.$appointment["id"].'" class="btn btn-info btn-xs view"><span class="glyphicon glyphicon-file" title="View">View</span></button>';
		if($_SESSION["role"] == 'admin' || $_SESSION["role"] == 'patient') { 
			$rows[] = '<button type="button" name="update" id="'.$appointment["id"].'" class="btn btn-warning btn-xs update"><span class="glyphicon glyphicon-edit" title="Edit">Edit</span></button>';
			$rows[] = '<button type="button" name="delete" id="'.$appointment["id"].'" class="btn btn-danger btn-xs delete" ><span class="glyphicon glyphicon-remove" title="Delete">Delete</span></button>';
		} else {
			$rows[] = '';
			$rows[] = '';
			$rows[] = '';
		}
		$records[] = $rows;
	}
	
	$output = array(
		"draw"	=>	intval($_POST["draw"]),			
		"iTotalRecords"	=> 	$displayRecords,
		"iTotalDisplayRecords"	=>  $allRecords,
		"data"	=> 	$records
	);
	
	echo json_encode($output);
}

We have already implemented methods to manage appointment functionality. The project complete source code is available in download project file.

You can view the live demo from the Demo link and can download the complete project files with database tables from the Download link below.

Demo Download

3 thoughts on “Doctor Appointment Management System with PHP & MySQL”

Ali says:

2 July, 2022 at 7:51 pm

Another great work! Thank you for your sharing.
Jon says:

12 July, 2022 at 9:15 am

Thank you for the tutorial. Although the admin demo user doesn’t appear to work.
1. PHPZAG Team says:
  
  23 July, 2022 at 7:47 am
  
  I am checking this and update you. thanks!

Comments are closed.

Step1: MySQL Database Tables

Step2: Manage Doctors

Step3: Manage Patients

Step4: Manage Appointment

3 thoughts on “Doctor Appointment Management System with PHP & MySQL”

Latest Tutorials

Popular Tags