Hospital OPD Management System Project Using Web Development & Data Analytics (Part 1 – Complete Web App Setup Guide)

Categories: Data Science Projects

Tags:

Hospital OPD Management System Project Using Web Development & Data Analytics (Part 1 – Complete Web App Setup Guide)

Hospital OPD Management System Using Web Development & Data Analytics – Project Overview

In today’s data-driven healthcare environment, hospitals and clinics need more than just manual registers or basic software. They need structured systems that not only manage patient flow efficiently but also generate actionable insights from OPD data.

SQL Dataset with Sample Data is here: https://github.com/slidescope/hospital-opd-management-system

This article introduces a real-world Hospital OPD Management System project, built step-by-step using Web Development, Databases, and Data Analytics, with guidance from ChatGPT. This is a multi-part project series, and Part 1 focuses entirely on web application setup — the foundation on which analytics and automation will later be built.

Software & Technical Requirements for Hospital OPD Management System – Part 1 (Web App Setup)

To successfully build Part 1 of the Hospital OPD Management System project, it is important to understand the software tools and technical knowledge required. This phase focuses on web application setup, database creation, and server configuration. Since this project is designed as a real-world, scalable system, the tools used are industry-standard and beginner-friendly at the same time.

1. Local Server Environment – XAMPP or AMPPS

The most important software required for this project is a local server environment, which allows you to run PHP and MySQL applications on your own system.

You can use XAMPP or AMPPS, depending on your preference.

Why XAMPP or AMPPS?

Both provide an all-in-one environment that includes:

Apache Server – to run PHP applications
MySQL / MariaDB – to manage the project database
PHP – backend scripting language
phpMyAdmin – web-based database management tool

Using a local server helps you:

Develop and test the OPD system offline
Safely experiment without affecting live systems
Understand real backend-server interactions

For beginners, XAMPP is widely used and easy to install. AMPPS offers additional tools like Softaculous but both work perfectly for this project.

2. Web Browser (Chrome / Edge / Firefox)

A modern web browser is essential for:

Testing application pages
Debugging layout and responsiveness
Checking session handling and form submissions

Browsers like Google Chrome or Microsoft Edge provide developer tools that allow you to inspect HTML, debug CSS, and monitor network requests — extremely useful during development.

3. Code Editor or IDE

You will need a code editor to write and manage your project files.

Recommended editors include:

Visual Studio Code
Sublime Text
PHPStorm (optional, advanced)

A good editor helps with:

Syntax highlighting
Code organization
Faster debugging
Cleaner project structure

Visual Studio Code is highly recommended because of its extensions for PHP, MySQL, Bootstrap, and HTML.

4. Basic Knowledge of Frontend Technologies

To follow Part 1 smoothly, you should have basic working knowledge of:

HTML

Used to create the structure of pages such as:

Login forms
Dashboards
Patient registration pages

CSS

Used for:

Page layout
Styling elements
Improving UI readability

Bootstrap

Bootstrap is used to:

Create responsive layouts
Build clean dashboards quickly
Maintain consistency across pages

Only basic Bootstrap classes are required — no advanced UI design knowledge is necessary.

5. Backend Knowledge – PHP

PHP is the core backend language used in this project.

You should understand:

PHP syntax basics
Form handling
Sessions
include and require
Database connectivity using MySQLi or PDO

PHP handles:

User authentication
Role-based access
Data insertion and retrieval
Dashboard statistics

6. Database Knowledge – MySQL

Since this is a data-centric project, MySQL knowledge is essential.

You should be familiar with:

Creating databases and tables
Primary and foreign keys
Basic SQL queries (SELECT, INSERT, UPDATE)
Relationships between tables

The OPD database is designed to support future data analytics and Power BI integration, making database fundamentals very important.

Final Summary

For Part 1 of the Hospital OPD Management System, you only need:
✔ XAMPP or AMPPS
✔ A modern web browser
✔ A code editor
✔ Basic HTML, CSS, Bootstrap knowledge
✔ Basic PHP and MySQL understanding

This setup ensures you can confidently build the foundation layer of a complete Data Science–ready healthcare system.

Why Build a Hospital OPD Management System Project?

OPD (Outpatient Department) operations generate massive volumes of data every day:

Patient registrations
Doctor consultations
Appointment schedules
Department-wise visits
Daily and monthly patient load

However, most institutions fail to structure this data properly, making analytics impossible later.

This project solves that problem by:

Designing a proper database architecture
Creating role-based web interfaces
Ensuring clean, analytics-ready data collection
Preparing the system for Power BI dashboards and AI insights

Project Architecture Overview

This Hospital OPD Management System is designed as a full-stack web application with analytics in mind.

Technology Stack Used

Frontend: HTML, CSS, Bootstrap
Backend: PHP
Database: MySQL
Server: XAMPP / WAMP (Localhost)
Analytics (Upcoming Parts): Power BI, Data Modeling
AI Support: ChatGPT

What This Article Covers (Part 1 – Web App Setup)

This article corresponds to Part 1 of the video series, which focuses on web development setup only.

⚠️ Data analytics, dashboards, Power BI, and AI automation will be covered in later parts.

Step 1: Project Folder & Server Setup

The first step is setting up a proper local development environment.

Install XAMPP or WAMP
Start Apache and MySQL services
Create a project folder inside htdocs
Organize folders for:
- includes (header, footer, sidebar)
- admin
- doctor
- receptionist
- assets (CSS, JS)

This structure ensures scalability as the project grows.

Step 2: Database Design for OPD System

Database design is the most critical step, especially when analytics is planned.

Key tables created in this phase include:

users (admin, doctors, receptionists)
doctors
patients
appointments
departments
visit_logs

Each table is designed with:

Proper primary keys
Foreign key relationships
Date and time fields for analytics
Clean naming conventions

This ensures the data can later be directly used in Power BI or SQL analytics.

Step 3: Database Connection Using PHP

A centralized database connection file is created using PHP.

Benefits:

Secure connection handling
Reusable across all pages
Easy maintenance

This file is included in all dashboards and CRUD pages.

Step 4: Role-Based Login System

The OPD system is built using role-based authentication, ensuring different access levels.

Roles Implemented

Admin: Full system access
Doctor: View appointments & patient details
Receptionist: Patient registration & appointment booking

Username & password
User role
Redirects users to the correct dashboard

This structure is crucial for real-world hospital systems.

Step 5: Basic Dashboards & Pages

In Part 1, basic dashboards are created for each role:

Admin Dashboard

Total doctors
Total receptionists
System overview cards

Doctor Dashboard

Assigned appointments
Patient visit history (structure only)

Receptionist Dashboard

Patient registration page
Appointment booking interface

At this stage, the focus is on functionality, not advanced UI.

Why Data Analytics Is Not Included Yet

Many projects make the mistake of jumping directly into dashboards without fixing data structure.

In this series:

Part 1: Web app + data collection foundation
Part 2: OPD workflows & real data generation
Part 3: Data cleaning & SQL queries
Part 4: Power BI dashboards & KPIs
Part 5: AI insights using ChatGPT

This approach ensures accurate, meaningful analytics.

Who Should Follow This Project?

This project is ideal for:

Web Development learners
Data Analytics students
Final-year project candidates
Healthcare IT enthusiasts
Professionals building portfolio projects

By the end of the series, you will have:
✔ A working hospital OPD web app
✔ A clean healthcare dataset
✔ Interactive analytics dashboards
✔ A strong real-world portfolio project

Below is the SQL Code that you can copy to create your database

-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Generation Time: Dec 20, 2025 at 09:09 AM
-- Server version: 8.0.39
-- PHP Version: 8.2.4

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: `opd_system`
--

-- --------------------------------------------------------

--
-- Table structure for table `appointments`
--

CREATE TABLE `appointments` (
  `id` int NOT NULL,
  `patient_id` int DEFAULT NULL,
  `doctor_id` int DEFAULT NULL,
  `appointment_date` date DEFAULT NULL,
  `appointment_time` time DEFAULT NULL,
  `status` enum('Pending','Confirmed','Completed') COLLATE utf8mb4_general_ci DEFAULT 'Pending',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `appointments`
--

INSERT INTO `appointments` (`id`, `patient_id`, `doctor_id`, `appointment_date`, `appointment_time`, `status`, `created_at`) VALUES
(1, 1, 2, '2025-12-20', '10:45:00', 'Confirmed', '2025-12-19 13:12:43');

-- --------------------------------------------------------

--
-- Table structure for table `doctors`
--

CREATE TABLE `doctors` (
  `id` int NOT NULL,
  `user_id` int DEFAULT NULL,
  `specialization` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `fee` decimal(10,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `doctors`
--

INSERT INTO `doctors` (`id`, `user_id`, `specialization`, `fee`) VALUES
(2, 3, 'Nephrology', 600.00);

-- --------------------------------------------------------

--
-- Table structure for table `patients`
--

CREATE TABLE `patients` (
  `id` int NOT NULL,
  `name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `age` int DEFAULT NULL,
  `gender` enum('Male','Female','Other') COLLATE utf8mb4_general_ci DEFAULT NULL,
  `phone` varchar(15) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `address` text COLLATE utf8mb4_general_ci,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `patients`
--

INSERT INTO `patients` (`id`, `name`, `age`, `gender`, `phone`, `address`, `created_at`) VALUES
(1, 'Rajat Singh', 23, 'Male', '123', 'Sakavi Ragan', '2025-12-19 13:06:18'),
(2, 'U Mathur', 48, 'Female', '145', 'Sakavi Ragan, Kanpur', '2025-12-19 13:07:48');

-- --------------------------------------------------------

--
-- Table structure for table `prescriptions`
--

CREATE TABLE `prescriptions` (
  `id` int NOT NULL,
  `appointment_id` int DEFAULT NULL,
  `doctor_id` int DEFAULT NULL,
  `patient_id` int DEFAULT NULL,
  `symptoms` text COLLATE utf8mb4_general_ci,
  `diagnosis` text COLLATE utf8mb4_general_ci,
  `medicines` text COLLATE utf8mb4_general_ci,
  `advice` text COLLATE utf8mb4_general_ci,
  `follow_up_date` date DEFAULT NULL,
  `file_path` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int NOT NULL,
  `name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `email` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `password` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `role` enum('admin','doctor','receptionist') COLLATE utf8mb4_general_ci DEFAULT NULL,
  `status` tinyint DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `name`, `email`, `password`, `role`, `status`, `created_at`) VALUES
(1, 'Admin', 'opdadmin@slidescope.com', '$2y$10$q1H5c.Xhz2RQQ65FhCWEXuv699FHselCYaHvAWC4QWQCnhRX9Qd9S', 'admin', 1, '2025-12-17 13:15:50'),
(3, 'Dr S Saxena', 's@test.com', '$2y$10$Jxmk6G.wVjv844cxpXXEpeR2m.HACDfslUNi2xE7YqMsZ5kO1ZDFG', 'doctor', 1, '2025-12-19 12:49:38'),
(4, 'S R Thakur', 'st@test.com', '$2y$10$8UZ5HapLQLcmfg9fAFeyteEgOOLpmBsurmmykX.tvWbwas9INZAAm', 'receptionist', 1, '2025-12-19 12:53:03');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `appointments`
--
ALTER TABLE `appointments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `patient_id` (`patient_id`),
  ADD KEY `doctor_id` (`doctor_id`);

--
-- Indexes for table `doctors`
--
ALTER TABLE `doctors`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `patients`
--
ALTER TABLE `patients`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `prescriptions`
--
ALTER TABLE `prescriptions`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `email` (`email`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `appointments`
--
ALTER TABLE `appointments`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `doctors`
--
ALTER TABLE `doctors`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `patients`
--
ALTER TABLE `patients`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `prescriptions`
--
ALTER TABLE `prescriptions`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `appointments`
--
ALTER TABLE `appointments`
  ADD CONSTRAINT `appointments_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`),
  ADD CONSTRAINT `appointments_ibfk_2` FOREIGN KEY (`doctor_id`) REFERENCES `doctors` (`id`);

--
-- Constraints for table `doctors`
--
ALTER TABLE `doctors`
  ADD CONSTRAINT `doctors_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;
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 */;

Where Does a Hospital OPD Management System Fit in the Complete Data Science Block? (Explained Simply)

When people hear Data Science, they often imagine only charts, machine learning models, or AI predictions. In reality, Data Science is a complete pipeline, and analytics is only one part of it. A Hospital OPD Management System project fits into this pipeline from the very first block, making it a foundational Data Science project, not just a software application.

Let’s break this down clearly.

The Complete Data Science Block (End-to-End)

A full Data Science lifecycle typically consists of these stages:

Problem Identification
Data Generation & Collection
Data Storage & Engineering
Data Cleaning & Transformation
Data Analysis & Visualization
Insights, Decision Support & Automation
AI / Predictive Modeling (Optional but Advanced)

Most beginners jump directly to step 5 or 7. This project deliberately starts where real-world Data Science actually begins.

1. Problem Identification – Healthcare OPD Inefficiency

The problem addressed in this project is very real:

Manual OPD registers
Scattered patient data
No visibility into doctor load or patient flow
No analytics for hospital decision-making

This is the business problem definition, which is the first step in Data Science.

2. Data Generation & Collection – Core Focus of This Project

This is where the Hospital OPD Management System plays a critical role.

Data Science cannot exist without structured data. Hospitals generate OPD data every day, but unless it is captured digitally and logically, analytics is impossible.

This project:

Digitizes patient registrations
Records appointments, visits, departments, and doctors
Captures timestamps, frequencies, and visit patterns

👉 This stage places the project firmly in the “Data Collection” block of Data Science.

Without this block, dashboards and AI models are meaningless.

3. Data Storage & Data Engineering

In Data Science, databases are not “just backend”.

They are part of Data Engineering, which includes:

Database schema design
Relationships between entities
Clean data types and constraints
Scalable table structures

By designing MySQL tables properly in this OPD system, the project:

Ensures analytics-ready data
Avoids future data inconsistencies
Enables SQL-based analysis

👉 This places the project in the Data Engineering layer of the Data Science stack.

4. Data Cleaning & Transformation (Upcoming Project Stage)

Once OPD data starts flowing:

Duplicate entries appear
Missing values exist
Date formats need standardization

This project is designed so that:

Data cleaning can be done using SQL
Structured fields reduce transformation effort
The dataset can be directly connected to BI tools

👉 This stage bridges Web Development → Data Analytics, which is rare in beginner projects.

5. Data Analysis & Visualization (Future Parts)

Only after the system collects real OPD data does Data Analytics begin:

Daily OPD trends
Doctor-wise workload
Peak visiting hours
Department performance
Monthly and yearly comparisons

This is where tools like Power BI come in, using the database built in this project.

6. Insights, Decision Support & AI Layer

The final block is where Data Science delivers value:

Staffing optimization
OPD load forecasting
Patient wait-time reduction
AI-powered summaries using ChatGPT

These are advanced layers that depend entirely on the quality of data captured earlier.

Final Positioning of This Project in Data Science

📌 This Hospital OPD Management System sits at the foundation of the Data Science block
📌 It combines Web Development + Data Engineering + Analytics readiness
📌 It mirrors how Data Science works in real hospitals and enterprises

Instead of being “just a PHP project,” it is actually a Data Science infrastructure project — the kind that real analysts, engineers, and data teams build before analytics even begins.

That’s what makes this project industry-relevant and portfolio-worthy.