How To Run a PHP Job Multiple Times in a Minute with Crontab on Ubuntu 20.04

The author selected Girls Who Code to receive a donation as part of the Write for DOnations program.

Introduction

In Linux, you can use the versatile crontab tool to process long-running tasks in the background at specific times. While the daemon is great for running repetitive tasks, it has got one limitation: you can only execute tasks at a minimum time interval of 1 minute.

However, in many applications, to avoid a poor user experience it’s preferable for jobs to execute more frequently. For instance, if you’re using the job-queue model to schedule file-processing tasks on your website, a significant wait will have a negative impact on end users.

Another scenario is an application that uses the job-queue model either to send text messages or emails to clients once they’ve completed a certain task in an application (for example, sending money to a recipient). If users have to wait a minute before the delivery of a confirmation message, they might think that the transaction failed and try to repeat the same transaction.

To overcome these challenges, you can program a PHP script that loops and processes tasks repetitively for 60 seconds as it awaits for the crontab daemon to call it again after the minute. Once the PHP script is called for the first time by the crontab daemon, it can execute tasks in a time period that matches the logic of your application without keeping the user waiting.

In this guide, you will create a sample cron_jobs database on an Ubuntu 20.04 server. Then, you’ll set up a tasks table and a script that executes the jobs in your table in intervals of 5 seconds using the PHP while(...){...} loop and sleep() functions.

Prerequisites

To complete this tutorial, you require the following:

• An Ubuntu 20.04 server set up with a non-root user. Follow our Initial Server Setup with Ubuntu 20.04 guide.

• A LAMP stack set up on your server. Refer to the How To Install Linux, Apache, MySQL, PHP (LAMP) stack on Ubuntu 20.04 guide. For this tutorial, you may skip Step 4 — Creating a Virtual Host for your Website.

Step 1 — Setting Up a Database

In this step, you’ll create a sample database and table. First, SSH to your server and log in to MySQL as root:

sudo mysql -u root -p

Enter your root password for the MySQL server and press ENTER to proceed. Then, run the following command to create a cron_jobs database.

CREATE DATABASE cron_jobs;

Create a non-root user for the database. You’ll need the credentials of this user to connect to the cron_jobs database from PHP. Remember to replace EXAMPLE_PASSWORD with a strong value:

CREATE USER 'cron_jobs_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'EXAMPLE_PASSWORD';
GRANT ALL PRIVILEGES ON cron_jobs.* TO 'cron_jobs_user'@'localhost';           
FLUSH PRIVILEGES;

Next, switch to the cron_jobs database:

USE cron_jobs;

Output
Database changed

Once you’ve selected the database, create a tasks table. In this table, you’ll insert some tasks that will be automatically executed by a cron job. Since the minimum time interval for running a cron job is 1 minute, you’ll later code a PHP script that overrides this setting and instead, execute the jobs in intervals of 5 seconds.

For now, create your tasks table:

CREATE TABLE tasks (
    task_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    task_name VARCHAR(50),          
    queued_at DATETIME,     
    completed_at DATETIME,
    is_processed CHAR(1)    
) ENGINE = InnoDB;

Insert three records to the tasks table. Use the MySQL NOW() function in the queued_at column to record the current date and time when the tasks are queued. Also for the completed_at column, use the MySQL CURDATE() function to set a default time of 00:00:00. Later, as tasks complete, your script will update this column:

INSERT INTO tasks (task_name, queued_at, completed_at, is_processed) VALUES ('TASK 1', NOW(), CURDATE(), 'N');
INSERT INTO tasks (task_name, queued_at, completed_at, is_processed) VALUES ('TASK 2', NOW(), CURDATE(), 'N');
INSERT INTO tasks (task_name, queued_at, completed_at, is_processed) VALUES ('TASK 3', NOW(), CURDATE(), 'N');

Confirm the output after running each INSERT command:

Output
Query OK, 1 row affected (0.00 sec)
...

Make sure the data is in place by running a SELECT statement against the tasks table:

SELECT task_id, task_name, queued_at, completed_at, is_processed FROM tasks;

You will find a list of all tasks:

Output
+---------+-----------+---------------------+---------------------+--------------+
| task_id | task_name | queued_at           | completed_at        | is_processed |
+---------+-----------+---------------------+---------------------+--------------+
|       1 | TASK 1    | 2021-03-06 06:27:19 | 2021-03-06 00:00:00 | N            |
|       2 | TASK 2    | 2021-03-06 06:27:28 | 2021-03-06 00:00:00 | N            |
|       3 | TASK 3    | 2021-03-06 06:27:36 | 2021-03-06 00:00:00 | N            |
+---------+-----------+---------------------+---------------------+--------------+
3 rows in set (0.00 sec)

The time for the completed_at column is set to 00:00:00, this column will update once the tasks are processed by a PHP script that you will create next.

Exit from the MySQL command-line interface:

QUIT;

Output
Bye

Your cron_jobs database and tasks table are now in place and you can now create a PHP script that processes the jobs.

Step 2 — Creating a PHP Script that Runs Tasks After 5 Seconds

In this step, you’ll create a script that uses a combination of the PHP while(...){...} loop and sleep functions to run tasks after every 5 seconds.

Open a new /var/www/html/tasks.php file in the root directory of your web server using nano:

sudo nano /var/www/html/tasks.php

Next, create a new try { block after a <?php tag and declare the database variables that you created in Step 1. Remember to replace EXAMPLE_PASSWORD with the actual password for your database user:

<?php
try {
    $db_name     = 'cron_jobs';
    $db_user     = 'cron_jobs_user';
    $db_password = 'EXAMPLE_PASSWORD';
    $db_host     = 'localhost';

Next, declare a new PDO (PHP Data Object) class and set the attribute ERRMODE_EXCEPTION to catch any PDO errors. Also, switch ATTR_EMULATE_PREPARES to false to let the native MySQL database engine handle emulation. Prepared statements allow you to send the SQL queries and data separately to enhance security and reduce chances of an SQL injection attack:

    $pdo = new PDO('mysql:host=' . $db_host . '; dbname=' . $db_name, $db_user, $db_password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);  
    $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);       

Then, create a new variable named $loop_expiry_time and set it to the current time plus 60 seconds. Then open a new PHP while(time() < $loop_expiry_time) { statement. The idea here is to create a loop that runs until the current time (time()) matches the variable $loop_expiry_time:

[label /var/www/html/tasks.php]       

    $loop_expiry_time = time() + 60;

    while (time() < $loop_expiry_time) { 

Next, declare a prepared SQL statement that retrieves unprocessed jobs from the tasks table:

[label /var/www/html/tasks.php]   

        $data = [];
        $sql  = "select 
                 task_id
                 from tasks
                 where is_processed = :is_processed
                 ";

Execute the SQL command and fetch all rows from the tasks table that have the column is_processed set to N. This means the rows are not processed:

[label /var/www/html/tasks.php]  

        $data['is_processed'] = 'N';  

        $stmt = $pdo->prepare($sql);
        $stmt->execute($data);

Next, loop through the retrieved rows using a PHP while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {...} statement and create another SQL statement. This time around, the SQL command updates the is_processed and completed_at columns for each task processed. This ensures that you don’t process tasks more than one time:

[label /var/www/html/tasks.php]  

        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { 
            $data_update   = [];         
            $sql_update    = "update tasks set 
                              is_processed  = :is_processed,
                              completed_at  = :completed_at
                              where task_id = :task_id                                 
                              ";

            $data_update   = [		          
                             'is_processed' => 'Y',                          
                             'completed_at' => date("Y-m-d H:i:s"),
                             'task_id'      => $row['task_id']                         
                             ];
            $stmt = $pdo->prepare($sql_update);
            $stmt->execute($data_update);
        }

Before you close the first PHP while (time() < $loop_expiry_time) { statement, include a sleep(5); statement to pause the jobs execution for 5 seconds and free up your server resources.

You may change the 5 seconds period depending on your business logic and how fast you want tasks to execute. For instance, if you would like the tasks to be processed 3 times in a minute, set this value to 20 seconds.

Remember to catch any PDO error messages inside a } catch (PDOException $ex) { echo $ex->getMessage(); } block:

[label /var/www/html/tasks.php]         
        sleep(5); 

        }       
		
} catch (PDOException $ex) {
    echo $ex->getMessage(); 
}

Your complete tasks.php file will be as follows:

<?php
try {
    $db_name     = 'cron_jobs';
    $db_user     = 'cron_jobs_user';
    $db_password = 'EXAMPLE_PASSWORD';
    $db_host     = 'localhost';

    $pdo = new PDO('mysql:host=' . $db_host . '; dbname=' . $db_name, $db_user, $db_password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);  
    $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);               

    $loop_expiry_time = time() + 60;

    while (time() < $loop_expiry_time) { 
        $data = [];
        $sql  = "select 
                 task_id
                 from tasks
                 where is_processed = :is_processed
                 ";

        $data['is_processed'] = 'N';             

        $stmt = $pdo->prepare($sql);
        $stmt->execute($data);

        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { 
            $data_update   = [];         
            $sql_update    = "update tasks set 
                              is_processed  = :is_processed,
                              completed_at  = :completed_at
                              where task_id = :task_id                                 
                              ";

            $data_update   = [		          
                             'is_processed' => 'Y',                          
                             'completed_at' => date("Y-m-d H:i:s"),
                             'task_id'      => $row['task_id']                         
                             ];
            $stmt = $pdo->prepare($sql_update);
            $stmt->execute($data_update);
        }   
       
        sleep(5); 

        }       
		
} catch (PDOException $ex) {
    echo $ex->getMessage(); 
}

Save the file by pressing CTRL + X, Y then ENTER.

Once you’ve completed coding the logic in the /var/www/html/tasks.php file, you’ll schedule the crontab daemon to execute the file after every 1 minute in the next step.

Step 3 — Scheduling the PHP Script to Run After 1 Minute

In Linux, you can schedule jobs to run automatically after a stipulated time by entering a command into the crontab file. In this step, you will instruct the crontab daemon to run your /var/www/html/tasks.php script after every minute. So, open the /etc/crontab file using nano:

sudo nano /etc/crontab

Then add the following toward the end of the file to execute the http://localhost/tasks.php after every 1 minute:

...
* * * * * root /usr/bin/wget -O - http://localhost/tasks.php

Save and close the file.

This guide assumes that you have a basic knowledge of how cron jobs work. Consider reading our guide on How to Use Cron to Automate Tasks on Ubuntu.

As earlier indicated, although the cron daemon runs the tasks.php file after every 1 minute, once the file is executed for the first time, it will loop through the open tasks for another 60 seconds. By the time the loop time expires, the cron daemon will execute the file again and the process will continue.

After updating and closing the /etc/crontab file, the crontab daemon should begin executing the MySQL tasks that you inserted in the tasks table immediately. To confirm whether everything is working as expected, you’ll query your cron_jobs database next.

Step 4 — Confirming Job Execution

In this step, you will open your database one more time to check whether the tasks.php file is processing queued jobs when executed automatically by the crontab.

Log back in to your MySQL server as root:

sudo mysql -u root -p

Then, enter your MySQL server’s root password and hit ENTER to proceed. Then, switch to the database:

USE cron_jobs;

Output
Database changed

Run a SELECT statement against the tasks table:

SELECT task_id, task_name, queued_at, completed_at, is_processed FROM tasks;

You will receive output similar to the following. In the completed_at column, tasks have been processed at intervals of 5 seconds. Also, the tasks have been marked as completed since the is_processed column is now set to Y, which means YES.

Output
+---------+-----------+---------------------+---------------------+--------------+
| task_id | task_name | queued_at           | completed_at        | is_processed |
+---------+-----------+---------------------+---------------------+--------------+
|       1 | TASK 1    | 2021-03-06 06:27:19 | 2021-03-06 06:30:01 | Y            |
|       2 | TASK 2    | 2021-03-06 06:27:28 | 2021-03-06 06:30:06 | Y            |
|       3 | TASK 3    | 2021-03-06 06:27:36 | 2021-03-06 06:30:11 | Y            |
+---------+-----------+---------------------+---------------------+--------------+
3 rows in set (0.00 sec)

This confirms that your PHP script is working as expected; you have run tasks in a shorter time interval by overriding the limitation of the 1 minute time period set by the crontab daemon.

Conclusion

In this guide, you’ve set up a sample database on an Ubuntu 20.04 server. Then, you have created jobs in a table and run them at intervals of 5 seconds using the PHP while(...){...} loop and sleep() functions. Use the logic in this tutorial when you’re next implementing a job-queue-based application where tasks need to be run multiple times within a 1 minute time period.

For more PHP tutorials, check out our PHP topic page.