Hey there! Let's break down the most practical ways to add automatic deletion of expired job postings to your site. I’ll cover three solid approaches, each with pros, cons, and code snippets that play nicely with your existing setup.

1. Database-Level Scheduled Event (e.g., MySQL Event Scheduler) #

This is my top pick if you don’t need to run extra business logic (like sending notifications) when deleting expired jobs. It’s efficient, runs directly in your database, and doesn’t rely on your app server.

How to Set It Up: #

First, make sure your MySQL event scheduler is enabled:

-- Check if scheduler is active
SHOW VARIABLES LIKE 'event_scheduler';

-- If it's OFF, turn it on (requires database admin permissions)
SET GLOBAL event_scheduler = ON;

Then create a recurring event to delete expired jobs (e.g., every day at 1 AM):

CREATE EVENT delete_expired_jobs
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 01:00:00' -- Set your preferred start time
DO
DELETE FROM jobs WHERE expiry_date < NOW();

Pros & Cons: #

• ✅ Super efficient, no app server overhead
• ✅ Runs even if your site is idle
• ❌ Can’t easily trigger custom logic (like email alerts) before deletion
• ❌ Requires database admin access to enable the scheduler

2. App-Level Cron Job + PHP Script #

Use this if you need to run additional tasks when deleting jobs—like logging deletions, emailing admins, or updating related records. It’s flexible and integrates with your app’s business logic.

Step 1: Create a Cleanup Script #

Make a standalone PHP file (e.g., cleanup_expired_jobs.php) in your site directory:

<?php
// Load your database connection
require_once 'path/to/your/db_connection.php';

// Optional: Fetch expired jobs first for logging/notifications
$expiredJobsQuery = "SELECT id, title FROM jobs WHERE expiry_date < NOW()";
$expiredJobsResult = mysqli_query($conn, $expiredJobsQuery);

// Log or notify about upcoming deletions
while ($job = mysqli_fetch_assoc($expiredJobsResult)) {
    error_log("Auto-deleting expired job: " . $job['title'] . " (ID: " . $job['id'] . ")");
    // Example: Send email to admin
    // mail('admin@yourdomain.com', 'Expired Job Deleted', "Job '" . $job['title'] . "' was automatically removed.");
}

// Execute the deletion
$deleteQuery = "DELETE FROM jobs WHERE expiry_date < NOW()";
if (mysqli_query($conn, $deleteQuery)) {
    echo "Successfully deleted " . mysqli_affected_rows($conn) . " expired jobs.";
} else {
    error_log("Error deleting expired jobs: " . mysqli_error($conn));
}

mysqli_close($conn);
?>

Step 2: Configure the Cron Job #

Add a Cron job on your server to run this script daily (adjust the time and paths to match your setup):

# Run every day at 1 AM, log output to a file
0 1 * * * /usr/bin/php /var/www/your-site/cleanup_expired_jobs.php >> /var/log/job-cleanup.log 2>&1

Pros & Cons: #

• ✅ Full control to add custom logic before/after deletion
• ✅ Works with any PHP app setup
• ❌ Relies on your server’s Cron service (needs to be running)
• ❌ Requires access to server settings to configure Cron

3. Lazy Deletion (Delete on Query) #

This is a simple, low-effort option for small sites with low traffic. Every time you fetch jobs to display, you first delete expired entries before returning the valid ones.

Modify Your Existing getJobs Function: #

function getJobs($conn) {
    // First, clean up expired jobs
    $deleteExpired = "DELETE FROM jobs WHERE expiry_date < NOW()";
    mysqli_query($conn, $deleteExpired);

    // Then fetch active jobs
    $query = "SELECT id, title, details, post_date, expiry_date 
              FROM jobs 
              WHERE expiry_date >= NOW() 
              ORDER BY post_date DESC";
    $result = mysqli_query($conn, $query);

    $jobs = [];
    while ($row = mysqli_fetch_assoc($result)) {
        $jobs[] = $row;
    }
    return $jobs;
}

Pros & Cons: #

• ✅ No extra setup (no Cron, no database events)
• ✅ Easy to implement with your existing code
• ❌ Can slow down page loads if deletion takes time (bad for high-traffic sites)
• ❌ Expired jobs will linger until someone visits the jobs page

Which Should You Choose? #

• Go with Database Events if you just need simple, hands-off deletion with no extra logic.
• Use Cron + PHP Script if you need to run custom tasks (notifications, logs) when jobs are deleted.
• Stick to Lazy Deletion if you have a small site and want minimal setup effort.

内容的提问来源于stack exchange，提问作者Richard Marks