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.
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]);
}
}