Manage Cron Jobs with Codeigniter 3.0 and MySQL

Managing cron jobs in CodeIgniter 3.0 directly through PHP and MySQL provides more flexibility compared to traditional cPanel-based cron jobs. Instead of manually adding multiple scheduled tasks in cPanel, we can store them in a MySQL database and execute them dynamically using a single cron job. This approach allows better visibility, easier modifications, and centralized task scheduling.

In this guide, we’ll walk through setting up a cron job manager in CodeIgniter 3.0, so all your scheduled tasks are controlled within your application.

CodeIgniter 3: Manage Cron Jobs Using PHP and MySQL

Automating tasks in web applications can significantly enhance efficiency and reliability. In CodeIgniter 3.0, integrating cron jobs with MySQL allows developers to schedule tasks like database backups, email notifications, and data cleanups seamlessly.

Storing scheduled tasks in a MySQL database allows for better visibility and control. Additionally, ensuring that your queries run efficiently can prevent database slowdowns when executing cron jobs. If your application runs frequent background tasks, it’s a good idea to optimize MySQL queries for better cron job execution.

Step 1: MySQL Database Setup

First, create a cron jobs table in MySQL to store the scheduled tasks. Each job will have a unique ID, a command to execute, an interval in seconds, and timestamps for tracking execution. Run the following SQL query to create the database structure:

CREATE TABLE `cron` (
`id` int(5) NOT NULL,
`name` varchar(100) DEFAULT NULL,
`command` varchar(255) NOT NULL,
`interval_sec` int(10) NOT NULL,
`last_run_at` datetime DEFAULT NULL,
`next_run_at` datetime DEFAULT NULL,
`is_active` tinyint(1) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Explanation: This MySQL table stores all scheduled cron jobs, allowing you to manage and track automated tasks directly in the database. Here’s what each column does:

• id – A unique identifier for each cron job.
• name – A descriptive label for the task (optional).
• command – The actual PHP script or shell command that runs.
• interval_sec – The time interval (in seconds) before the job runs again.
• last_run_at – The last time this job was executed.
• next_run_at – The scheduled time for the next execution.
• is_active – A flag (1 for active, 0 for disabled) to control job execution.

Step 2: Create a Codeigniter Library to Run the Cron Jobs

Now, we need a custom CodeIgniter library to process scheduled tasks. This script will:

• Fetch active cron jobs from the database
• Execute them using shell commands
• Update the next run time based on the interval

Create a new file named application/libraries/CronRunner.php and add the following code:

<?php
defined('BASEPATH') or exit('No direct script access allowed');

class CronRunner
{
    private $CI;

    public function __construct()
    {
        $this->CI =& get_instance();
    }

    private function calculateNextRun($obj)
    {
        return (time() + $obj->interval_sec);
    }

    public function run()
    {
        $query = $this->CI->db->where('is_active', 1)
            ->where('now() >= next_run_at OR next_run_at IS NULL', '', false)
            ->from('cron')->get();

        if ($query->num_rows() > 0) {
            $env = getenv('CI_ENV');
            foreach ($query->result() as $row) {
                $cmd = "export CI_ENV={$env} && {$row->command}";
                $this->CI->db->set('next_run_at', 'FROM_UNIXTIME('.$this->calculateNextRun($row).')', false)
                    ->where('id', $row->id)->update('cron');
                $output = shell_exec($cmd);
                $this->CI->db->set('last_run_at', 'now()', false)
                    ->where('id', $row->id)->update('cron');
            }
        }
    }
}

The CronRunner library in CodeIgniter 3 scans the MySQL cron jobs table, finds active tasks where next_run_at is due, executes the PHP command or shell script, and updates next_run_at based on the defined interval. It also logs execution by updating last_run_at, ensuring jobs run at the correct time.

Step 3: Create a Codeigniter Controller to Invoke the Cron Job

Next, create a controller that triggers cron jobs. This controller ensures that scheduled tasks only run from the command line (CLI) and not through a web request, preventing unauthorized execution. Add the following code to a new file named application/controllers/Cron.php:

<?php  
if (!defined('BASEPATH')) {  
    exit('No direct script access allowed');  
}  

class Cron extends MY_Controller  
{  
    public function __construct()  
    {  
        parent::__construct();  
        if (!$this->input->is_cli_request()) {  
            show_error('Direct access is not allowed');  
        }  
    }  

    public function run()  
    {  
        $this->load->library('core/CronRunner');  
        $cron = new CronRunner();  
        $cron->run();  
    }  
}  

The is_cli_request() function restricts execution to CLI mode, preventing users from triggering cron jobs via a browser.

Step 4: Setting Up the Main Cron Job in Linux (or CPanel)

To automate the execution of stored cron jobs in MySQL, set up a single cron job in your Linux server. This will act as a cron job dispatcher, checking the database for pending tasks and executing them. Open your terminal and edit your crontab file using:

crontab -e  

Add the following line to run the cron job every minute:

* * * * * /usr/bin/php /path_to_your_project/index.php cron run  

This ensures that Codeigniter’s cron controller runs frequently, checking for jobs that need execution.

If you don’t have command-line access, you can do this in cPanel by going to Advanced > Cron Jobs in your hosting dashboard.

Step 5: Adding Jobs to the Database

To add scheduled tasks, insert records into the cron table with the command to execute and the desired interval. For example, to run a script for clearing temporary files every 5 minutes, use:

INSERT INTO cron (name, command, interval_sec, is_active)  
VALUES ('Clear Temp Files', 'php /path_to_project/clear_temp.php', 300, 1);  

You can add multiple jobs, and the cron manager will execute them automatically based on their schedule.

Real-World Example: Automating Scheduled Tasks in CodeIgniter

Automating recurring tasks like sending emails, cleaning up old data, or generating reports can improve efficiency in any web application. With CodeIgniter 3, you can store these scheduled tasks in a MySQL database and let a cron job manager handle execution automatically.

Example #1: Sending Abandoned Cart Email Reminders

Imagine running an eCommerce website where customers often add items to their cart but leave without completing their purchase. Instead of manually sending reminder emails, you can set up an automated task that runs every day and emails these customers.

To schedule this task, run the following SQL Query to insert arecord into the cron jobs table:

INSERT INTO cron (name, command, interval_sec, is_active) VALUES ('Send Abandoned Cart Emails', 'php /path_to_project/send_cart_email.php', 86400, 1);

Explanation:

• 86400 seconds = 24 hours, so the job runs once daily.
• The script send_cart_email.php will process all abandoned carts and send personalized reminder emails.
• The cron job manager will execute this script automatically every day.

Example #2: Cleaning Up Old Database Records

If your application stores logs, temporary files, or expired sessions, you don’t want them cluttering your database forever. You can schedule a data cleanup task to remove old records every 7 days.

To automate this, add the following job to the cron table:

INSERT INTO cron (name, command, interval_sec, is_active)
VALUES ('Delete Old Logs', 'php /path_to_project/delete_old_logs.php', 604800, 1);

• 604800 seconds = 7 days, so the script runs weekly.
• The script delete_old_logs.php will remove outdated log entries, keeping the database optimized.
• The cron job manager will handle execution automatically.

Why Use a Database-Driven Cron Job Manager?

✅ Centralized Management – Easily add, modify, or disable tasks in the database without editing system crontab files.
✅ More Control – Track execution timestamps (last_run_at and next_run_at) to monitor job activity.
✅ Better Debugging – If a job fails, logs and timestamps help identify the issue quickly.
✅ Scalability – Easily add new tasks without modifying cPanel or Linux cron settings.

This approach ensures smooth automation of repetitive processes while keeping cron job execution organized and efficient in CodeIgniter 3 applications.

FAQs