Hi,

I'm writing a script in which a form submission will store data in a mysql db and send three emails. The first email will be sent on form submission. The 2nd 24 hrs later and the 3rd 48 hrs later.

What I've done till now is, used a cron job to run a script. The DB stores the timestamp for the time when the 2nd email must be sent.. i.e. on form submission i store in the MySQL DB

send_1 = date_add(now(),interval 1 day)

and another field which adds two days

send_2 = date_add(now(),interval 2 day)

Now the script which runs every one minute checks if current time is greater than the send_1 time i.e.

if ($now_time > $row[send_1] ) { Send email }

Of course there is yet another field to help decide if the 2nd or 3rd email has to be sent...

All this seems to work.

But my question is, is this the best way to do this job? I want to make the script as simple as possible coz it will run every minute.

G
[ I hope I was clear enuff]

    There is a program related to cron called "at" (the man page for cron often mentions this), which is for one-off jobs that need to be performed at a particular time. For each submission you could send the one mail immediately, and set up two at jobs to send the other emails 24 and 48 hours later.

      hey,
      I can't find any info on this at google.
      but i still think its better to use google coz I'd hafta make modifications to the DB once the email is sent too!

        ganeshp, you posted your question because your intuition tells you that there's a simpler way. Your intuition is right. You're entangling your business rules (when to send mail) with your data (the database table), and that's not a good idea.

        All the database really needs to know for selection purposes is the timestamp of the forms submission. The query can figure out and apply the business logic algorithmically. I'll explain how in a moment, but first let me make it more complicated:

        1) Do the emails have to be sent PRECISELY 24/48 hours later? If "within an hour" is OK, then you can cut back on those cron processes a lot.

        2) What happens if the system clock drifts ahead by a minute or two, then gets reset to the proper time? With your approach, somebody gets duplicate emails.

        3) What happens if the system goes down for ten minutes and gets rebooted? With your approach, somebody misses an email.

        I would have my table include an automatic timestamp "tstamp" and an integer "mail_sent" default 0 to record the number of nag notes.

        That has several benefits. It lets you deal with the potential duplicate mail problem implied by my point 2, and the potential no-mail problem implied by my point 3. Very importantly, it lets the database optimize the query, especially if you index mail_sent. Comparing integers is a lot faster than examining timestamps.

        To send the first reminder
        SELECT * FROM mytable WHERE mail_sent = 0 AND tstamp < DATE_SUB(NOW(), INTERVAL 1 DAY);

        (untested code but I think I have it right)

        ... then update mail_sent for each corresponding record upon successful mail().

        For the second reminder
        SELECT * FROM mytable WHERE mail_sent = 1 AND tstamp < DATE_SUB(NOW(), INTERVAL 2 DAY);

        And so forth.

        This approach works even if the server goes down for an hour or two, or jumps ahead in time because the clock was corrected. And it does not have to be adjusted if you back your cron process off to firing once every hour, or every four hours, or even daily.

          hey,
          I had to read your post twice to understand the code hehe, anyway thanks a lot.
          I never thought abt the automatic timestamp thing. I will try the code and see if it works. And it doesn't have to be exact 24 hrs, it can be a little late too.
          G

            Write a Reply...