How to check if email has been sent already

Question

Hi,

I have a (PHP) script which runs on a new row in MySQL. However, it will continually send the emails, even if they've already been sent...causing mass emails into the users Inbox.

The script is quite big, so I cant paste it here - http://pastebin.com/6v75F4Gv

Is there anyway I can somehow check if this script has already sent 'xx' email, but if a new row containing 'zz' and the user has specified they wish to receive content containing 'zz' it sends the 'zz' row?

ie. It stops duplicate/etc. emails.

Note: MySQL table is in the following format:
Table: feed
Rows in pager: id, title, description, date

Table: recipients
Rows in recipients: id, email, suburb

Thanks :)

Answer 1

Neither of your tables has a column to log if the email has been sent, which is what I'd expect to see happen here:

if (sendMail($subject, $to, $body, $from, $cc))
{
   echo 'Mail for ' . $to . ' has been sent.<br />';

   //Update database so only one email is sent...
   $sql = "INSERT INTO TRACKING_TABLE 
              (feed_id, recipient_id, isSent)
           VALUES
              ($feed_id, $recip_id, 'Y')";
   $query = mysql_query($sql);
} else
{  ...

...which would mean updating:

$feed_sql = "SELECT id, title, description, rssDate 
               FROM feed 
              WHERE MATCH (title) AGAINST ('" . $rows['suburb'] . "') 
                AND NOT EXISTS(SELECT NULL
                                 FROM TRACKING_TABLE tt
                                WHERE tt.feed_id = feed.id
                                  AND tt.recipient_id = $Recipient_id
                                  AND tt.issent = 'Y')
           GROUP BY pubDate 
           ORDER BY pubDate DESC 
              LIMIT 1";

...to be sure that your query isn't picking up those that have been successfully sent.

DROP TABLE IF EXISTS `example`.`tracking_table`;
CREATE TABLE  `example`.`tracking_table` (
  `feed_id` int(10) unsigned NOT NULL default '0',
  `recipient_id` int(10) unsigned NOT NULL default '0',
  `issent` char(1) NOT NULL default '',
  PRIMARY KEY  (`feed_id`,`recipient_id`),
  FOREIGN KEY (feed_id) REFERENCES pager(id),
  FOREIGN KEY (recipient_id) REFERENCES recipients(id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Answer 2

Well, there is no generic way, which means you'll have to implement it yourself.

Either create a sent flag column in your recipients table, unsetting it in all records before the sending process starts, and setting it to "yes" once E-Mail for that specific recipient has been sent.

Or, more complex but more flexible, create a new table "deliveries" containing all the addresses that already received a certain mailing (identified by the current date, a string [e.g. "Newsletter Q3"] or whatever). This method may be a bit database-heavy, but a great way of keeping track of multiple mailings.

Answer 3

You need to keep track of this in your database. You don't have any fields currently in your tables to support this, so you first need to revise your schema.

By quickly scanning your program, I get the impression that your pager table contains messages, and recipients contains those that receive the messages. The messages are one-to-many, so each message MAY be sent to several recipients. Correct?

That being the case, you will want to have a 3rd table where you keep track of what you sent. Perhaps with fields such as recipient_id, message_id, timestamp, and maybe a field for the success or failure of the message, which you can populate later if the message bounces.