PHP Task Scheduler Configured by Site Admins

Question

I'm trying to create a way for administrators on my site to schedule a task similar to how I would setup a cron job on the server to run a particular script. I'd like them to have similar control on when the tasks runs e.g. every day at 14:00 or every week on Thursday at 12:00 etc.

I figured I would have a form which asks them how often they'd like to run the task, which days/weeks etc. This would then be stored in the database. Next I would create a cron job to run a script say every minute. This script would then select all the tasks in the database which should run and execute each one.

I have been looking at task schedulers for this and so far it seems like most of them are built for the web developer to schedule tasks programatically. Instead I'd like to store them in the database and then write an SQL query to select the correct tasks to run. I was wondering what structure I should use to store the schedule in the database and how I would retrieve the correct tasks to run at a particular time?

I'd really appreciate if someone could point me in the right direction.

Answer 1

Here is a simplified explanation and example of how I have seen this implemented in past projects. For brevity, I have left out security considerations, but note that letting users specify commands to run is innately insecure.

Tasks SQL Table

You will need these three columns set up for your execution script to consume. The interval column is a cron string (minute hour day month year). The script_path column is the path where the script will be run. The last_executed column is when that task was last run. The interval and last_executed columns will be used to determine if the task should be executed.

+----+------------+----------------------+---------------------+
| id |  interval  |      script_path     |    last_executed    |
+----+------------+----------------------+---------------------+
| 1  | 5 * * * *  | /path/to/script1.php | 2016-01-01 00:00:00 |
+----+------------+----------------------+---------------------+
| 2  | * 12 * * * | /path/to/script2.php | 2016-01-01 00:00:00 |
+----+------------+----------------------+---------------------+

Task Execution Script

This script will run every minute via a cron job.

#/usr/bin/env php
<?php

// Get tasks from the database
$db = new PDO('dsn', 'username', 'password');
$stmt = $db->prepare('SELECT * FROM `tasks`');
$stmt->execute();
$tasks = $stmt->fetchAll(PDO::FETCH_OBJ);

foreach ($tasks as $task) {
    $timestamp = time();
    $lastExecutedTimestamp = strtotime($task->last_executed);
    // Convert cron expression to timestamp
    $intervalTimestamp = $task->interval;

    // Check if the task should be run.
    if ($timestamp - $lastExecutedTimestamp >= $intervalTimestamp) {
        // Execute task
        // ...

        // Update the task's last_executed time.
        $stmt = $db->prepare('UPDATE `tasks` SET `last_executed` = ? WHERE `id` = ?');
        $stmt->execute([date('Y-m-d H:i:s', $timestamp), $task->id]);
    }
}

Answer 2

Some good thoughts in other answers here. I would also point out that you should give consideration to using PHP's DateTime, DateInterval, DatePeriod, and related classes if you find yourself needing to do more complex date handling (like displaying all scheduled tasks in calendar in GUI admin tool)

You might have a DB table containing task schedule rules that would look something like:

id - unique auto-increment
name - human-readable task name
owner - perhaps forieg key to user tables so you know who owns tasks
interval - An string interval specification as used in DateInterval
start_time - Datetime When rule goes into effect
end_time - Datetime When rule is no longer in effect
script_path - path to script of some sort of command recognized by your applcation
last_execution - Datetime for last time script was triggered
next_execution - Datetime in which you store value calculated to be next execution point
active - maybe a flag to enable/disable a rule
perhaps other admin fields like created_time, error_tracking, etc.

And you could easily build a collection of of DatePeriod objects you can iterate on from each table row. That might look something like:

// have one authoritative now that you use in this script 
$now = DateTime();
$now_sql = $now->format('Y-m-d H:i:s'); 


$sql = <<<EOT

SELECT
    id,
    name,
    interval,
    /* etc */
FROM task_rules
WHERE
    active = 1
    AND
        (IS_NULL(start_time) OR start_time <= '{$now_sql}')
    AND
        (IS_NULL(end_time) OR eend_time > '{$now_sql}')
    /* Add this filter if you are trying to query this table
        for overdue events */
    AND
        next_execution <= '{$now_sql}'
    /* any other filtering you might want to do */
/* Any ORDER BY and LIMIT clauses */

EOT;


$tasks = array();
//logic to read rows from DB
while ($row = /* Your DB fetch mechanism */) {
    // build your task (probably could be its own class,
    // perhaps saturated via DB retrieval process), but this is jist
    $task = new stdClass();
    $task->id = $row->id
    $task->name = $row->name;
    $task->interval = $row->interval;
    $task->start_time = $row->start_time;
    // etc. basically map DB row to an object

    // start building DateTime and related object representations
    // of your tasks
    $task->dateInterval = new DateInterval($task->interval);

    // determine start/end dates for task sequence
    if(empty($task->start_time)) {
        // no defined start date, so build start date from last executed time
        $task->startDateTime = DateTime::createFromFormat(
            'Y-m-d H:i:s',
            $task->last_execution
        );
    } else {
        // start date known, so we want to base period sequence on start date
        $task->startDateTime = DateTime::createFromFormat(
            'Y-m-d H:i:s',
            $task->start_date
        );
    }

    if(empty($task->end_time)) {
        // No defined end. So set artificial end date based on app needs
        // (like we need to show next week, month, year)
       $end_datetime = clone $now;
       $end_datetime->modify(+ 1 month);
       $task->endDateTime = $end_datetime;
    } else {
       $task->endDateTime = DateTime::createFromFormat(
            'Y-m-d H:i:s',
            $task->end_time
        );
    }

    $task->datePeriod = new DatePeriod(
        $task->startDateTime,
        $task->dateInterval,
        $task->endDateTime
    );

    // iterate datePeriod to build array of occurences
    // which is more useful than just working with Traversable
    // interface of datePeriod and allows you to filter out past
    // scheduled occurences
    $task->future_occurrences = [];
    foreach ($task->datePeriod as $occurence) {
        if ($occurence < $now) {
            // this is occcurrence in past, do nothing
            continue;
        }

        $task->future_occurrences[] = $occurence;
    }

    $task->nextDateTime = null;    
    if(count($task->future_occurrences) > 0) {
        $task->nextDateTime = $task->future_occurrences[0];
        $task->next_execution = $task->nextDateTime->format('Y-m-d H:i:s');
    }     

    $tasks[] = $task;
}

Here $tasks would contain an array of objects each representing a single rule along with tangible PHP DateTime, DatePeriod constructs you can use to to execute and/or display tasks.

For example:

// execute all tasks
// just using a simple loop example here
foreach($tasks as $task) {
    $command = 'php ' . $task->script_path;
    exec($command);

    // update DB
    $sql = <<<EOT

UPDATE task_rules
SET
    last_execution = '{$now_sql}',
    next_execution = '{$task->next_execution}'
WHERE id = {$task->id}

EOT;

     // and execute using DB tool of choice
}

Answer 3

The idea is fairly simple and it seems like you've got it pretty well grasped. If you have a defined set of "Tasks" that the administrators can schedule, it's a simple as storing them in a database table along with a timestamp of when they should be run. You will then have a single script (e.g., "job_runner.php") that you schedule (e.g., via cron) to run as often as necessary (this is a business requirement that you must define).

You can define your Jobs for the admin to schedule like-so:

interface JobInterface {
    public function run();
} 

class RunSalesReport implements JobInterface {
    public function run(){ 
        // .. business logic 
    };

    // or maybe just __invoke() would be fine! your call!
}

Your "Task Scheduler" web form would hold a list of Jobs that the admin can schedule to run, for example the list may contain a "Run Sales Report" job that correlates to the aforementioned RunSalesReport class. The server-side handler for the web form would just store the form data in a database table.

The database table could contain just a time_to_run column (to determine when the job should be run) and a job_class column (to hold the class name that should be instantiated/factoried/whatever).

The "job_runner.php" file simply connects to the data layer and finds any "Jobs" that are scheduled to run but haven't been run yet (you can either flag then as "executed" or drop them from the table after they've been run, your call).

// job_runner.php - executed via cron however often you need it to be
// if admin can only schedule jobs on the hour, then run on the hour, etc.
$jobs = $pdo->execute("SELECT * FROM scheduled_jobs WHERE DATE(time_to_run) <= DATE(NOW())");
foreach($pdo->fetchAll($jobs) as $jobRow){
    $jobClassName = $jobRow['job_class'];
    $job = new $jobClassName; // or get from IOC container, your decision
    $job->run();
}