Time triggered email sending

daarius

hi guys,
looking for a way to send email, triggered by a specific timestamp in the database.

e.g. a row in the database has a datetime column. if that datetime matches the now() then i would like to send an email with some info in it.

now, dont want a cron job, cos i will have to be running the cron every min, round the clock. that wont be exact anyway, as teh seconds can be missed out!

either looking for some php shell that has concurrent permanent connection with a database and keeps reading the table. OR, more preferred will be a mail() function with delayed delivery time given. e.g. some sort headers set in it which tells the sendmail to delivery the mail on specific time. till then keep it in its queue. this way, i can send delayed delivery mails once a day in bulk.

thanks

Kudose

How about a script with the time limit set to 0 (set_time_limit(0) runs forever) and a loop like

set_time_limit(0);
while(1){
  $sql = 'SELECT something FROM table WHERE datetime=NOW()';
  // loop results
    // send email
}

Weedpacket

If you know in advance when the email is to be sent (suggested by your idea of queueing the mail for later sending), then you could look at using at(1) instead of cron(8). at is for doing something at a particular time.

Note also that sendmail can be configured to queue outgoing mails and can then be told to process the queue either on a regular basis or on demand.

daarius

the first example above can miss results. e.g. if gets some resultsets that are huge in number, it will processing them and send mails. this could take a minute or more. by the time it comes back for a second loop it would have missed some mails that were suppose to be sent few seconds after the first resultsets it got last time... hope you understood me?

only option i can think of is having a script run "every minute" by a cron. forgetting about the seconds, this solution can work, as it will get all the resultsets for each minute. doesnt matter that it will take more than a minute to complete one job, because cron opens up new thread each time it is executed.

for the sendmail queue settings, the above example wont work as there is no set time for the mails to be sent generally. every email can have different time for sending.

thanks for help
ali

Kudose

Why do they need to be sent at a specific second, if you don't mind sharing? This is the first time I've heard of someone actually wanting to do that.

daarius

lets say its a Poll. but with a fixed time when the voting starts. There are many polls in the database, every one of them start on different time. I am not worried about seconds. but accuracy to per minute is must. so all the registrants receive email saying "the voting has started for so and so Poll"...

the reason exact time for sending mail is important because every Poll has a limited time to vote. e.g. a poll opens only for 1 hour. so as soon as that time reaches of when the poll opens all the people must be voted as there is only 1 hour to contribute.

now just for the sake of example. if there was a poll that only runs for 10 mins. and there are 20,000 email recipients. when the time comes for that minute of poll starting. i will start sending emails. this can take 5 mins or so lets say. now when this job is finished and the script loops back again to check another Poll wit now(), that could have missed other polls which started in last 5 mins.

so therefore, the above example of a shell script running forever wouldnt work.

i think the cron job running every minute will do the trick here. as every minute it will open a new thread incase last one is still running.