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

Rashmi Mishra
personRashmi Mishra
April 19, 2025
0

 

How to develop Projects(OJPS)

 Using PHP and Mysql 

Part 12


7️⃣ Job Application Management and Employer Dashboard

🎯 Purpose:

To enhance the functionality for employers and job seekers by providing a detailed management interface, tracking the status of applications, and enabling employers to manage their job postings more effectively.

✅ Functionalities to Implement:

🔹 For Employers:

  • Manage Job Posts:
    • View all posted jobs with the option to edit or delete.
    • Track the number of applications per job posting.
  • Review Applications:
    • Employers can see a list of applicants with the status of their application (Pending, Reviewed, Selected, Rejected).
    • Option to change the status of each applicant (e.g., from Pending to Reviewed).
    • View resumes and cover letters uploaded by job seekers.
  • Search Applications:
    • Filter applications by status or job position.

🔹 For Job Seekers:

  • Track Application Status:
    • Job seekers can log in to their dashboard to see the status of their applications (Pending, Reviewed, Selected, Rejected).
  • Update Profile:
    • Job seekers can update their resume, cover letter, and contact details.

📁 File/Folder Structure:

/jobs/

├── employer_dashboard.php # Employer's dashboard to manage job posts and view applications.

├── view_applications.php # Page to review job applications.

├── update_application_status.php # Update the status of job applications.

├── job_seeker_dashboard.php # Job seeker's dashboard to track application status.

├── update_profile.php # Job seeker profile update form.

🧮 Suggested Database Changes:

1.   users table (for storing job seeker profiles)

Add the following fields:

ALTER TABLE users ADD COLUMN resume VARCHAR(255);
ALTER TABLE users ADD COLUMN cover_letter TEXT;

2.   job_application_status (to track application status updates)

Create a new table for application status history:

CREATE TABLE job_application_status (
    id INT AUTO_INCREMENT PRIMARY KEY,
    application_id INT NOT NULL,
    status ENUM('Pending', 'Reviewed', 'Selected', 'Rejected') DEFAULT 'Pending',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (application_id) REFERENCES applications(id)
);

This will allow employers to track the history of each application and see how its status has evolved over time.

✅ Additional Considerations:

  • Notifications: Add email or in-app notifications to alert job seekers when their application status changes.
  • Data Validation: Implement strong form validation for file uploads (resumes, cover letters) and ensure proper user input for job posting details.
  • Security: Ensure that user data is protected, and implement appropriate access control to prevent unauthorized actions (e.g., a job seeker shouldn't be able to edit or delete job posts).
  • User Interface (UI): Consider using a clean, modern UI with clear categories for job seekers to filter by job status or job type. Also, provide a clean dashboard for employers to view applications in a streamlined manner.

8️⃣ Job Recommendations and Notifications

🎯 Purpose:

To improve user engagement by recommending relevant jobs to job seekers based on their preferences and previous interactions. Additionally, notifications will help keep job seekers and employers informed about important events such as application status changes, new job postings, etc.

✅ Functionalities to Implement:

🔹 For Job Seekers:

  • Job Recommendations:
    • Recommend jobs to job seekers based on their profile, search history, and previous applications.
    • Use filters such as job type, location, salary, and category to personalize recommendations.
  • Notifications:
    • Notify job seekers when a job they applied for changes status (e.g., "Reviewed", "Selected", "Rejected").
    • Notify job seekers of new jobs matching their preferences.
    • Send reminders to job seekers about incomplete applications or expired job postings.

🔹 For Employers:

  • Notifications:
    • Notify employers when a new application is received.
    • Notify employers when a job posting is about to expire or has expired.
    • Remind employers to review pending applications.

📁 File/Folder Structure:

/jobs/

├── job_recommendations.php # Page to display personalized job recommendations for job seekers.

├── notifications.php # Page to view all notifications for job seekers and employers.

├── notification_settings.php # Page to manage notification preferences for job seekers and employers.

├── send_notifications.php # Backend script for sending notifications to users.

🧮 Suggested Database Changes:

1.   job_recommendations table (for storing job recommendations for job seekers)

CREATE TABLE job_recommendations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    seeker_id INT NOT NULL,
    job_id INT NOT NULL,
    recommended_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (seeker_id) REFERENCES users(id),
    FOREIGN KEY (job_id) REFERENCES jobs(id)
);

2.   notifications table (for storing notifications)

CREATE TABLE notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    message TEXT NOT NULL,
    status ENUM('Unread', 'Read') DEFAULT 'Unread',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

3.   user_preferences table (for storing notification preferences)

CREATE TABLE user_preferences (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    notify_new_jobs BOOLEAN DEFAULT TRUE,
    notify_status_changes BOOLEAN DEFAULT TRUE,
    notify_recommendations BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

 Code in detail:

ure! Below is a simple beginner-friendly PHP code for each file in your /jobs/ folder — matching your structure and purpose.

employer_dashboard.php

<?php
include '../db_connection.php';
session_start();
$employer_id = $_SESSION['user_id'];
 
$sql = "SELECT * FROM jobs WHERE employer_id = $employer_id";
$result = mysqli_query($conn, $sql);
?>
<h2>Your Posted Jobs</h2>
<table border="1">
    <tr><th>Title</th><th>Posted On</th><th>Actions</th></tr>
    <?php while($row = mysqli_fetch_assoc($result)): ?>
    <tr>
        <td><?php echo $row['title']; ?></td>
        <td><?php echo $row['posted_at']; ?></td>
        <td>
            <a href="view_applications.php?job_id=<?php echo $row['id']; ?>">View Applications</a> |
            <a href="edit_job.php?id=<?php echo $row['id']; ?>">Edit</a> |
            <a href="delete_job.php?id=<?php echo $row['id']; ?>">Delete</a>
        </td>
    </tr>
    <?php endwhile; ?>
</table>

view_applications.php

<?php
include '../db_connection.php';
session_start();
$job_id = $_GET['job_id'];
 
$sql = "SELECT a.*, u.name FROM applications a JOIN users u ON a.seeker_id = u.id WHERE a.job_id = $job_id";
$result = mysqli_query($conn, $sql);
?>
<h2>Applications for Job ID: <?php echo $job_id; ?></h2>
<table border="1">
    <tr><th>Applicant</th><th>Resume</th><th>Status</th><th>Action</th></tr>
    <?php while($row = mysqli_fetch_assoc($result)): ?>
    <tr>
        <td><?php echo $row['name']; ?></td>
        <td><a href="../uploads/<?php echo $row['resume']; ?>" target="_blank">Download</a></td>
        <td><?php echo $row['status']; ?></td>
        <td>
            <form method="post" action="update_application_status.php">
                <input type="hidden" name="application_id" value="<?php echo $row['id']; ?>">
                <select name="status">
                    <option value="Pending">Pending</option>
                    <option value="Reviewed">Reviewed</option>
                    <option value="Selected">Selected</option>
                    <option value="Rejected">Rejected</option>
                </select>
                <button type="submit">Update</button>
            </form>
        </td>
    </tr>
    <?php endwhile; ?>
</table>

update_application_status.php

<?php
include '../db_connection.php';
if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $application_id = $_POST['application_id'];
    $status = $_POST['status'];
 
    $update = "UPDATE applications SET status = '$status' WHERE id = $application_id";
    $history = "INSERT INTO job_application_status (application_id, status) VALUES ($application_id, '$status')";
 
    if (mysqli_query($conn, $update) && mysqli_query($conn, $history)) {
        echo "Application status updated successfully.";
    } else {
        echo "Error: " . mysqli_error($conn);
    }
}
?>
<a href="javascript:history.back()">Go Back</a>

job_seeker_dashboard.php

<?php
include '../db_connection.php';
session_start();
$seeker_id = $_SESSION['user_id'];
 
$sql = "SELECT a.*, j.title FROM applications a JOIN jobs j ON a.job_id = j.id WHERE seeker_id = $seeker_id";
$result = mysqli_query($conn, $sql);
?>
<h2>My Applications</h2>
<table border="1">
    <tr><th>Job Title</th><th>Status</th><th>Applied On</th></tr>
    <?php while($row = mysqli_fetch_assoc($result)): ?>
    <tr>
        <td><?php echo $row['title']; ?></td>
        <td><?php echo $row['status']; ?></td>
        <td><?php echo $row['applied_at']; ?></td>
    </tr>
    <?php endwhile; ?>
</table>

update_profile.php

<?php
include '../db_connection.php';
session_start();
$user_id = $_SESSION['user_id'];
 
if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $resume = $_FILES['resume']['name'];
    $cover_letter = $_POST['cover_letter'];
    move_uploaded_file($_FILES['resume']['tmp_name'], "../uploads/$resume");
 
    $sql = "UPDATE users SET resume = '$resume', cover_letter = '$cover_letter' WHERE id = $user_id";
    if (mysqli_query($conn, $sql)) {
        echo "Profile updated successfully!";
    } else {
        echo "Error: " . mysqli_error($conn);
    }
}
?>
<form method="POST" enctype="multipart/form-data">
    <label>Resume:</label>
    <input type="file" name="resume" required><br><br>
    <label>Cover Letter:</label><br>
    <textarea name="cover_letter" rows="5" cols="40" required></textarea><br><br>
    <button type="submit">Update Profile</button>
</form>

✅ Database Changes Recap:

1️⃣ For Users Table:

ALTER TABLE users ADD COLUMN resume VARCHAR(255);
ALTER TABLE users ADD COLUMN cover_letter TEXT;

2️⃣ For Application Status History Table:

CREATE TABLE job_application_status (
    id INT AUTO_INCREMENT PRIMARY KEY,
    application_id INT NOT NULL,
    status ENUM('Pending', 'Reviewed', 'Selected', 'Rejected') DEFAULT 'Pending',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (application_id) REFERENCES applications(id)
);

job_recommendations.php

<?php
include '../db_connection.php';
session_start();
$seeker_id = $_SESSION['user_id'];
 
$sql = "SELECT jr.*, j.title, j.description 
        FROM job_recommendations jr 
        JOIN jobs j ON jr.job_id = j.id 
        WHERE jr.seeker_id = $seeker_id
        ORDER BY jr.recommended_at DESC";
 
$result = mysqli_query($conn, $sql);
?>
<h2>Recommended Jobs for You</h2>
<ul>
<?php while($row = mysqli_fetch_assoc($result)): ?>
    <li>
        <strong><?php echo $row['title']; ?></strong><br>
        <?php echo $row['description']; ?><br>
        Recommended on: <?php echo $row['recommended_at']; ?>
    </li>
<?php endwhile; ?>
</ul>

notifications.php

<?php
include '../db_connection.php';
session_start();
$user_id = $_SESSION['user_id'];
 
$sql = "SELECT * FROM notifications WHERE user_id = $user_id ORDER BY created_at DESC";
$result = mysqli_query($conn, $sql);
?>
<h2>Your Notifications</h2>
<ul>
<?php while($row = mysqli_fetch_assoc($result)): ?>
    <li style="color:<?php echo $row['status'] == 'Unread' ? 'red' : 'gray'; ?>">
        <?php echo $row['message']; ?> 
        <small>[<?php echo $row['created_at']; ?>]</small>
    </li>
<?php endwhile; ?>
</ul>

notification_settings.php

<?php
include '../db_connection.php';
session_start();
$user_id = $_SESSION['user_id'];
 
if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $new_jobs = isset($_POST['notify_new_jobs']) ? 1 : 0;
    $status_changes = isset($_POST['notify_status_changes']) ? 1 : 0;
    $recommendations = isset($_POST['notify_recommendations']) ? 1 : 0;
 
    $check = mysqli_query($conn, "SELECT id FROM user_preferences WHERE user_id = $user_id");
    if (mysqli_num_rows($check) > 0) {
        $sql = "UPDATE user_preferences SET 
                notify_new_jobs = $new_jobs,
                notify_status_changes = $status_changes,
                notify_recommendations = $recommendations
                WHERE user_id = $user_id";
    } else {
        $sql = "INSERT INTO user_preferences (user_id, notify_new_jobs, notify_status_changes, notify_recommendations) 
                VALUES ($user_id, $new_jobs, $status_changes, $recommendations)";
    }
    mysqli_query($conn, $sql);
    echo "Preferences updated!";
}
 
$pref_result = mysqli_query($conn, "SELECT * FROM user_preferences WHERE user_id = $user_id");
$pref = mysqli_fetch_assoc($pref_result);
?>
<form method="POST">
    <label><input type="checkbox" name="notify_new_jobs" <?php if($pref['notify_new_jobs']) echo 'checked'; ?>> Notify about New Jobs</label><br>
    <label><input type="checkbox" name="notify_status_changes" <?php if($pref['notify_status_changes']) echo 'checked'; ?>> Notify about Status Changes</label><br>
    <label><input type="checkbox" name="notify_recommendations" <?php if($pref['notify_recommendations']) echo 'checked'; ?>> Notify about Recommendations</label><br>
    <button type="submit">Save Preferences</button>
</form>

send_notifications.php

<?php
include '../db_connection.php';
 
function sendNotification($user_id, $message) {
    global $conn;
    $stmt = $conn->prepare("INSERT INTO notifications (user_id, message) VALUES (?, ?)");
    $stmt->bind_param("is", $user_id, $message);
    $stmt->execute();
    $stmt->close();
}
 
// Example usage:
sendNotification(1, "A new job has been posted that matches your profile!");
sendNotification(2, "Your job application status has been updated!");
?>

✅ Database Table Scripts

1️⃣ Job Recommendations Table

CREATE TABLE job_recommendations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    seeker_id INT NOT NULL,
    job_id INT NOT NULL,
    recommended_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (seeker_id) REFERENCES users(id),
    FOREIGN KEY (job_id) REFERENCES jobs(id)
);

2️⃣ Notifications Table

CREATE TABLE notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    message TEXT NOT NULL,
    status ENUM('Unread', 'Read') DEFAULT 'Unread',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

3️⃣ User Preferences Table

CREATE TABLE user_preferences (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    notify_new_jobs BOOLEAN DEFAULT TRUE,
    notify_status_changes BOOLEAN DEFAULT TRUE,
    notify_recommendations BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

💡 With this setup:

  • Job seekers see personalized job recommendations.
  • Both employers and seekers get notification alerts.
  • Users can customize their notification preferences.
  • Admin or a script can send notifications dynamically.

 


Tags

You Might Like

Show more
PHP-OJPS

Post a Comment

0Comments

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