based on this database (Updated Database Structure Database Structure The database for…
- User
based on this database (Updated Database Structure Database Structure The database for the admin dashboard and patient management system consists of the following tables: a. patients id (INT, AUTO_INCREMENT, PRIMARY KEY): Unique identifier for each patient. name (VARCHAR): Full name of the patient. age (INT): Age of the patient. gender (ENUM('Male', 'Female', 'Other')): Gender of the patient. phone (VARCHAR): Phone number of the patient. email (VARCHAR): Email address of the patient. medical_history (TEXT): Medical history of the patient. date_of_birth (DATE): Date of birth of the patient. registration_date (DATE): Date when the patient was registered. registration_datetime (DATETIME): Date and time when the patient was registered. status (ENUM): Current status of the patient (e.g., 'Initial Consultation', 'Treatment in Progress'). b. health_records id (INT, AUTO_INCREMENT, PRIMARY KEY): Unique identifier for each health record. patient_id (INT, FOREIGN KEY REFERENCES patients(id)): ID of the patient associated with this health record. blood_pressure (VARCHAR): Blood pressure reading. weight (DECIMAL): Weight of the patient. bmi (DECIMAL): Body Mass Index of the patient. cholesterol (DECIMAL): Cholesterol level of the patient. prescription (TEXT): Prescriptions given to the patient. treatment_progress (TEXT): Notes on the treatment progress. record_date (DATE): Date of the health record. c. admins id (INT, AUTO_INCREMENT, PRIMARY KEY): Unique identifier for each admin. username (VARCHAR): Admin's username. password (VARCHAR): Hashed password for the admin. email (VARCHAR): Email address of the admin. d. patient_logins id (INT, AUTO_INCREMENT, PRIMARY KEY): Unique identifier for each patient login. patient_id (INT, FOREIGN KEY REFERENCES patients(id)): ID of the patient associated with this login. username (VARCHAR): Username for patient login. password (VARCHAR): Hashed password for patient login. e. invoices id (INT, AUTO_INCREMENT, PRIMARY KEY): Unique identifier for each invoice. patient_id (INT NOT NULL, FOREIGN KEY REFERENCES patients(id)): ID of the patient associated with this invoice. service_name (VARCHAR(255) NOT NULL): Name of the service rendered. service_description (TEXT): Description of the service. cost (DECIMAL(10, 2) NOT NULL): Cost of the service. tax_rate (DECIMAL(5, 2) DEFAULT 0): Tax rate applied to the service. discount (DECIMAL(5, 2) DEFAULT 0): Discount applied to the service. total_amount (DECIMAL(10, 2) NOT NULL): Total amount due after tax and discount. invoice_date (DATE DEFAULT CURRENT_DATE): Date of the invoice. notes (TEXT): Additional notes for the invoice. f. services service_id (INT, AUTO_INCREMENT, PRIMARY KEY): Unique identifier for each service. service_name (VARCHAR(255) NOT NULL): Name of the service. service_price (DECIMAL(10, 2) NOT NULL): Price of the service. service_description (TEXT): Description of the service. created_at (TIMESTAMP DEFAULT CURRENT_TIMESTAMP): Timestamp when the service was created. updated_at (TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP): Timestamp when the service was last updated. g. invoice_services id (INT, AUTO_INCREMENT, PRIMARY KEY): Unique identifier for each entry in the invoice services table. invoice_id (INT NOT NULL, FOREIGN KEY REFERENCES invoices(id)): ID of the associated invoice. service_id (INT NOT NULL, FOREIGN KEY REFERENCES services(service_id)): ID of the associated service. service_name (VARCHAR(255) NOT NULL): Name of the service rendered. cost (DECIMAL(10, 2) NOT NULL): Cost of the service rendered. h. comments id (INT, AUTO_INCREMENT, PRIMARY KEY): Unique identifier for each comment. patient_id (INT NOT NULL, FOREIGN KEY REFERENCES patients(id)): ID of the patient associated with the comment. sender (VARCHAR(10) NOT NULL): Sender of the comment, either 'patient' or 'doctor'. message (TEXT NOT NULL): The message or comment content. timestamp (DATETIME DEFAULT CURRENT_TIMESTAMP): Date and time the comment was made. parent_id (INT NULL DEFAULT NULL): ID of the parent comment (for threaded conversations), if applicable. i. doctor_patient (New Table) id (INT, AUTO_INCREMENT, PRIMARY KEY): Unique identifier for each doctor-patient relationship. doctor_id (INT NOT NULL, FOREIGN KEY REFERENCES doctors(id)): ID of the associated doctor. patient_id (INT NOT NULL, FOREIGN KEY REFERENCES patients(id)): ID of the associated patient. UNIQUE (doctor_id, patient_id): Ensures a unique doctor-patient pair.
- File Paths Here are the file paths for the PHP files mentioned in the previous messages: /your_web_root/ │ ├── admin_dashboard.php // Admin dashboard displaying patient list ├── admin_login.php // Admin login page ├── admin_logout.php // Admin logout functionality ├── create_patient.php // Page for creating a new patient ├── view_patient.php // Page for viewing patient details ├── edit_patient.php // Page for editing patient information ├── delete_patient.php // Page for deleting a patient ├── add_health_record.php // Page for adding health records for a patient ├── edit_health_record.php // Page for editing health records ├── patient_login.php // Patient login page ├── patient_dashboard.php // Patient dashboard displaying their information ├── patient_logout.php // Patient logout functionality ├── db_connect.php // Database connection file ├── index.php // index home ├── update_patient_status.php // update patient st ├── doctors/ │ ├── login.php │ ├── logout.php │ ├── dashboard.php │ ├── view_patient.php │ ├── edit_health_record.php │ ├── add_health_record.php │ └── health_records.php │ └── manage_doctors.php – this file for admin login
├── patients/ │ ├── delete_comment.php │ ├── patient_dashboard.php │ ├── patient_login.php │ ├── send_comment.php │ ├── patient_logout.php ├── invoice/ │ ├── index.php // Page for generating an invoice │ ├── services.php // Page for generating a services │ ├── create_invoice.php
│ ├── edit_invoice.php
│ ├── print_invoice.php
│ ├── view_invoice.php / ) in edit_health_record.php (<?php // doctors/edit_health_record.phpsession_start(); require_once '../db_connect.php';
// Ensure the doctor is logged in if (!isset($_SESSION['doctor_id'])) { header("Location: login.php"); exit(); }
// Get the health record ID _GET['id'] ?? null;
if (!$record_id) { header("Location: dashboard.php"); exit(); }
// Fetch the health record and ensure the doctor has access to it _SESSION['doctor_id'];
pdo->prepare(" SELECT hr.* FROM health_records hr JOIN doctor_patient dp ON hr.patient_id = dp.patient_id WHERE hr.id = ? AND dp.doctor_id = ? "); record_id, $doctor_id]);
stmt->fetch();
if (!$health_record) { // Either the record doesn't exist or the doctor doesn't have access echo "You are not authorized to edit this health record."; exit(); }
health_record['patient_id'];
blood_pressure = weight = bmi = cholesterol = prescription = treatment_progress = record_date = $health_record['record_date'];
if (_SERVER['REQUEST_METHOD'] == 'POST') { // Collect and sanitize inputs blood_pressure = trim(weight = bmi = cholesterol = prescription = trim(treatment_progress = trim(record_date = $_POST['record_date'];
// Validate inputs if (empty($blood_pressure)) { $errors[] = "Blood pressure is required."; } if (!is_numeric($weight) || $weight <= 0) { $errors[] = "Weight must be a positive number."; } if (!is_numeric($bmi) || $bmi <= 0) { $errors[] = "BMI must be a positive number."; } if (!is_numeric($cholesterol) || $cholesterol <= 0) { $errors[] = "Cholesterol level must be a positive number."; } if (empty($prescription)) { $errors[] = "Prescription is required."; } if (empty($treatment_progress)) { $errors[] = "Treatment progress notes are required."; } if (empty($record_date)) { $errors[] = "Record date is required."; } else { // Check if date is valid $d = DateTime::createFromFormat('Y-m-d', $record_date); if (!$d || $d->format('Y-m-d') != $record_date) { $errors[] = "Invalid date format for record date."; } } if (empty($errors)) { // Proceed to update the database $update_stmt = $pdo->prepare(" UPDATE health_records SET blood_pressure = ?, weight = ?, bmi = ?, cholesterol = ?, prescription = ?, treatment_progress = ?, record_date = ? WHERE id = ? "); $update_stmt->execute([$blood_pressure, $weight, $bmi, $cholesterol, $prescription, $treatment_progress, $record_date, $record_id]); if ($update_stmt) { // Record updated successfully header("Location: view_patient.php?id=" . urlencode($patient_id)); exit(); } else { $errors[] = "Failed to update health record. Please try again."; } }} ?>
<div class="section"> <?php if (!empty($errors)): ?> <div class="error-messages"> <p>Please correct the following errors:</p> <ul> <?php foreach ($errors as $error): ?> <li><?= htmlspecialchars($error) ?></li> <?php endforeach; ?> </ul> </div> <?php endif; ?> <form method="POST" action=""> <div class="form-group"> <label for="blood_pressure">Blood Pressure:</label> <input type="text" id="blood_pressure" name="blood_pressure" value="<?= htmlspecialchars($blood_pressure) ?>" required> </div> <div class="form-group"> <label for="weight">Weight (kg):</label> <input type="number" step="0.01" id="weight" name="weight" value="<?= htmlspecialchars($weight) ?>" required> </div> <div class="form-group"> <label for="bmi">BMI:</label> <input type="number" step="0.01" id="bmi" name="bmi" value="<?= htmlspecialchars($bmi) ?>" required> </div> <div class="form-group"> <label for="cholesterol">Cholesterol (mg/dL):</label> <input type="number" step="0.01" id="cholesterol" name="cholesterol" value="<?= htmlspecialchars($cholesterol) ?>" required> </div> <div class="form-group"> <label for="prescription">Prescription:</label> <textarea id="prescription" name="prescription" rows="4" required><?= htmlspecialchars($prescription) ?></textarea> </div> <div class="form-group"> <label for="treatment_progress">Treatment Progress Notes:</label> <textarea id="treatment_progress" name="treatment_progress" rows="4" required><?= htmlspecialchars($treatment_progress) ?></textarea> </div> <div class="form-group"> <label for="record_date">Record Date:</label> <input type="date" id="record_date" name="record_date" value="<?= htmlspecialchars($record_date) ?>" required> </div> <input type="submit" value="Update Health Record"> </form> <a href="view_patient.php?id=<?= htmlspecialchars($patient_id) ?>" class="back-link">Back to Patient Record</a> </div> </div> - Assistant
Certainly! Below is the complete PHP code for
edit_health_record.phptailored to your provided database structure. This script ensures that only authenticated doctors associated with the specific patient can edit the health record. The code includes robust security measures, form validation, and clear error handling.Key Features of the Code:
-
Session Management:
- Starts a session to manage doctor authentication.
- Redirects to the login page if the doctor is not authenticated.
-
Access Control:
- Verifies that the doctor is authorized to edit the specified health record by checking the
doctor_patientrelationship.
- Verifies that the doctor is authorized to edit the specified health record by checking the
-
Input Sanitization and Validation:
- Sanitizes all user inputs to prevent SQL injection and XSS attacks.
- Validates form inputs to ensure data integrity.
-
Database Interaction:
- Uses prepared statements to securely interact with the database.
- Updates the health record upon successful validation.
-
User Feedback:
- Displays error messages if there are validation issues.
- Confirms successful updates and redirects appropriately.
-
User Interface:
- Provides a user-friendly form with pre-filled existing data.
- Includes basic styling for better readability.
Full Code for
edit_health_record.php:<?php // File: doctors/edit_health_record.php session_start(); require_once '../db_connect.php'; // Ensure the doctor is logged in if (!isset($_SESSION['doctor_id'])) { header("Location: login.php"); exit(); } // Get the health record ID from GET parameters $record_id = isset($_GET['id']) ? intval($_GET['id']) : null; if (!$record_id) { header("Location: dashboard.php"); exit(); } $doctor_id = $_SESSION['doctor_id']; $errors = []; // Fetch the health record ensuring the doctor has access $stmt = $pdo->prepare(" SELECT hr.*, p.name AS patient_name FROM health_records hr JOIN patients p ON hr.patient_id = p.id JOIN doctor_patient dp ON hr.patient_id = dp.patient_id WHERE hr.id = ? AND dp.doctor_id = ? "); $stmt->execute([$record_id, $doctor_id]); $health_record = $stmt->fetch(); if (!$health_record) { // Record doesn't exist or doctor doesn't have access echo "You are not authorized to edit this health record or the record does not exist."; exit(); } $patient_id = $health_record['patient_id']; // Initialize form variables with existing values $blood_pressure = $health_record['blood_pressure']; $weight = $health_record['weight']; $bmi = $health_record['bmi']; $cholesterol = $health_record['cholesterol']; $prescription = $health_record['prescription']; $treatment_progress = $health_record['treatment_progress']; $record_date = $health_record['record_date']; if ($_SERVER['REQUEST_METHOD'] == 'POST') { // Collect and sanitize inputs $blood_pressure = trim($_POST['blood_pressure']); $weight = trim($_POST['weight']); $bmi = trim($_POST['bmi']); $cholesterol = trim($_POST['cholesterol']); $prescription = trim($_POST['prescription']); $treatment_progress = trim($_POST['treatment_progress']); $record_date = trim($_POST['record_date']); // Validate inputs if (empty($blood_pressure)) { $errors[] = "Blood pressure is required."; } if ($weight === '' || !is_numeric($weight) || $weight <= 0) { $errors[] = "Weight must be a positive number."; } if ($bmi === '' || !is_numeric($bmi) || $bmi <= 0) { $errors[] = "BMI must be a positive number."; } if ($cholesterol === '' || !is_numeric($cholesterol) || $cholesterol <= 0) { $errors[] = "Cholesterol level must be a positive number."; } if (empty($prescription)) { $errors[] = "Prescription is required."; } if (empty($treatment_progress)) { $errors[] = "Treatment progress notes are required."; } if (empty($record_date)) { $errors[] = "Record date is required."; } else { // Validate date format (YYYY-MM-DD) $d = DateTime::createFromFormat('Y-m-d', $record_date); if (!$d || $d->format('Y-m-d') !== $record_date) { $errors[] = "Invalid date format for record date."; } } if (empty($errors)) { // Proceed to update the database $update_stmt = $pdo->prepare(" UPDATE health_records SET blood_pressure = ?, weight = ?, bmi = ?, cholesterol = ?, prescription = ?, treatment_progress = ?, record_date = ? WHERE id = ? "); try { $update_stmt->execute([ $blood_pressure, $weight, $bmi, $cholesterol, $prescription, $treatment_progress, $record_date, $record_id ]); // Check if the update was successful if ($update_stmt->rowCount()) { // Record updated successfully header("Location: view_patient.php?id=" . urlencode($patient_id) . "&msg=record_updated"); exit(); } else { $errors[] = "No changes were made to the health record."; } } catch (PDOException $e) { // Log the error message in a real-world scenario $errors[] = "Failed to update health record. Please try again."; } } } ?> <!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>Edit Health Record - <?= htmlspecialchars($health_record['patient_name']) ?></title> <!-- Include your CSS files here --> <style> /* Basic styling for demonstration */ body { font-family: Arial, sans-serif; background-color: #f4f4f4; margin: 0; padding: 20px; } .container { max-width: 700px; background: #fff; padding: 20px; margin: auto; border-radius: 5px; box-shadow: 0 0 10px rgba(0, 0, 0, 0.1); } .header { text-align: center; margin-bottom: 30px; } .header h2 { margin: 0; color: #333; } .section { margin-bottom: 20px; } .error-messages { background: #f2dede; color: #a94442; padding: 15px; border-radius: 5px; margin-bottom: 20px; } .error-messages ul { list-style-type: disc; padding-left: 20px; margin: 0; } .form-group { margin-bottom: 20px; } label { display: block; margin-bottom: 8px; color: #555; } input[type="text"], input[type="number"], input[type="date"], textarea { width: 100%; padding: 10px; border: 1px solid #ccc; border-radius: 4px; } textarea { resize: vertical; } input[type="submit"] { background: #5cb85c; color: #fff; border: none; padding: 12px 20px; border-radius: 4px; cursor: pointer; } input[type="submit"]:hover { background: #4cae4c; } .back-link { display: inline-block; margin-top: 20px; text-decoration: none; color: #337ab7; } .back-link:hover { text-decoration: underline; } </style> </head> <body> <div class="container"> <div class="header"> <h2>Edit Health Record for <?= htmlspecialchars($health_record['patient_name']) ?></h2> </div> <div class="section"> <?php if (!empty($errors)): ?> <div class="error-messages"> <p>Please correct the following error(s):</p> <ul> <?php foreach ($errors as $error): ?> <li><?= htmlspecialchars($error) ?></li> <?php endforeach; ?> </ul> </div> <?php endif; ?> <form method="POST" action=""> <div class="form-group"> <label for="blood_pressure">Blood Pressure:</label> <input type="text" id="blood_pressure" name="blood_pressure" value="<?= htmlspecialchars($blood_pressure) ?>" required> </div> <div class="form-group"> <label for="weight">Weight (kg):</label> <input type="number" step="0.01" id="weight" name="weight" value="<?= htmlspecialchars($weight) ?>" required> </div> <div class="form-group"> <label for="bmi">BMI:</label> <input type="number" step="0.01" id="bmi" name="bmi" value="<?= htmlspecialchars($bmi) ?>" required> </div> <div class="form-group"> <label for="cholesterol">Cholesterol (mg/dL):</label> <input type="number" step="0.01" id="cholesterol" name="cholesterol" value="<?= htmlspecialchars($cholesterol) ?>" required> </div> <div class="form-group"> <label for="prescription">Prescription:</label> <textarea id="prescription" name="prescription" rows="4" required><?= htmlspecialchars($prescription) ?></textarea> </div> <div class="form-group"> <label for="treatment_progress">Treatment Progress Notes:</label> <textarea id="treatment_progress" name="treatment_progress" rows="4" required><?= htmlspecialchars($treatment_progress) ?></textarea> </div> <div class="form-group"> <label for="record_date">Record Date:</label> <input type="date" id="record_date" name="record_date" value="<?= htmlspecialchars($record_date) ?>" required> </div> <input type="submit" value="Update Health Record"> </form> <a href="view_patient.php?id=<?= htmlspecialchars($patient_id) ?>" class="back-link">← Back to Patient Record</a> </div> </div> </body> </html>Explanation of the Code:
-
Session Handling and Authentication:
- The script starts by initiating a session using
session_start(). - It checks if the
doctor_idis set in the session to ensure that the doctor is logged in. If not, it redirects the user to thelogin.phppage.
- The script starts by initiating a session using
-
Fetching the Health Record:
- Retrieves the
record_idfrom the GET parameters and ensures it's a valid integer. - Prepares a SQL statement that joins the
health_records,patients, anddoctor_patienttables to verify that the logged-in doctor is associated with the patient whose health record is being edited. - If the health record doesn't exist or the doctor isn't authorized to edit it, an error message is displayed, and the script exits.
- Retrieves the
-
Initializing Form Variables:
- Pre-fills the form fields with existing data from the health record to provide a better user experience.
-
Form Submission Handling:
- Upon form submission (
POSTrequest), the script sanitizes and validates each input:- Ensures required fields are not empty.
- Validates numerical inputs (
weight,bmi,cholesterol) to be positive numbers. - Checks the
record_datefor the correct format (YYYY-MM-DD).
- If validation passes, it updates the
health_recordstable with the new data using a prepared statement to prevent SQL injection. - Upon successful update, the script redirects the doctor to the
view_patient.phppage with a success message. If no changes were made or an error occurs, appropriate error messages are displayed.
- Upon form submission (
-
User Interface:
- The HTML structure provides a clean and responsive form for editing the health record.
- Error messages are displayed prominently to guide the doctor in correcting any issues.
- A "Back to Patient Record" link is provided for easy navigation.
-
Security Measures:
- Prepared Statements: All database interactions use prepared statements to prevent SQL injection attacks.
- Input Sanitization: User inputs are trimmed and passed through
htmlspecialchars()before being displayed to prevent Cross-Site Scripting (XSS) attacks. - Access Control: Only doctors associated with a patient can edit that patient's health records.
Additional Recommendations:
-
CSRF Protection:
- Implement Cross-Site Request Forgery (CSRF) tokens to further secure the form against unauthorized submissions.
-
Password Hashing:
- Ensure that all passwords in the
adminsandpatient_loginstables are securely hashed using algorithms likebcryptorArgon2.
- Ensure that all passwords in the
-
Error Logging:
- Instead of displaying raw error messages, log them to a file or monitoring system to avoid exposing sensitive information to users.
-
Responsive Design:
- Enhance the CSS to make the form more responsive and user-friendly across different devices.
-
Input Constraints:
- Utilize HTML5 input attributes like
min,max, andpatternto enforce input constraints on the client side.
- Utilize HTML5 input attributes like
-
Session Security:
- Regenerate session IDs upon successful login to prevent session fixation attacks.
- Use secure and HTTP-only cookies to store session data.
Example of Enhanced Security with CSRF Protection:
To implement CSRF protection, you can add a hidden token field in your form and verify it upon form submission.
1. Generate and Store CSRF Token:
Add the following code after fetching the health record:
// Generate CSRF token if not set if (empty($_SESSION['csrf_token'])) { $_SESSION['csrf_token'] = bin2hex(random_bytes(32)); } $csrf_token = $_SESSION['csrf_token'];2. Add CSRF Token to the Form:
Include a hidden input field within your
<form>tags:<input type="hidden" name="csrf_token" value="<?= htmlspecialchars($csrf_token) ?>">3. Verify CSRF Token Upon Form Submission:
Before processing the form data, add the following check:
if ($_SERVER['REQUEST_METHOD'] == 'POST') { // Check CSRF token if (!isset($_POST['csrf_token']) || $_POST['csrf_token'] !== $_SESSION['csrf_token']) { $errors[] = "Invalid CSRF token. Please try again."; } else { // Proceed with input collection and validation } }This ensures that the form submission is legitimate and not forged.
By following the above guidelines and utilizing the provided code, you can create a secure and efficient
edit_health_record.phppage that allows authorized doctors to manage patient health records seamlessly. -