在特定条件下停止执行cron作业

Question

I have a DB with 2 tables:

subscribers:
 _____________________________
|  id    |  name  |   email   |
|________|________|___________|
|  1     |  John  |   [e-mail] |
|  ..    |   ..   |     ..    |
|  5000  |  Mark  |   [e-mail] |
|________|________|___________|

last_id:
 _______
|  id   |
|_______|
| NULL  |
|_______|

Every day I run a cron job to send all the emails at once, But The script is timing out after 100 sec.

So instead I'm thinking of sending about 200 emails per time:

/* Get the id from last time is script was executed */
$last_id = "SELECT `id` FROM `last_id`";

/* Select the next 200 subscribers starting from the last id */
$query= "SELECT * FROM `subscribers` WHERE `id` > ". $last_id . " LIMIT 200";

/* Send 200 emails */
..

/* Get the last id of these 200 subscribers */
$last = ;

/* Update the value of `id` in `last_id` table */
$query= "UPDATE `last_id` SET `id` = ". $last;

The cron job runs this script every 10 minutes, But after sending all the emails the script would be executed over and over without sending any emails.

So how to stop executing this cron job after all the emails are sent, When last_id = 5000 and start it again the next day?

Answer 1

We had an exercise like this at school once, we sorted it by adding a 'send date' column to the subscriber table. You select with a limit X and a date < then 'now', upon sending you update the records of these subscribers with the 'now' date.

The query wont select anything if all are send. so the cron will run but doesn't do anything.

And the next day, it will start with again as the select will return the 'unsend' records.

And if you don't want to use the dates, you can use a bool or tinyint, select where 'send' is 0 and set them to 1 after sending. And have another cron update all the records back to 0 run once daily/or whatever increment you want to send these emails.