PHP Mailing List - Best way to track emails sent already, for multiple campaigns

[3dron]

I have a decent mailing list program I wrote that I use for 10,000 emails. It has a full unsubscribe link that runs a script that marks an email status '0' inactive. Bounced emails are piped to another script that macrks them as status '3' non-existent. Viewed and visited scripts handle records into a second table that holds email, campaign numbers, viewed, visited.

But what I need is the best method to record whether an email has sent a campaigns email. Mainly because my server only allows 200 per hour. (bt alos to show stats) My sending query is something to the effect of:

Select DISTINCT * FROM email_list WHERE active = 1 and sent < $campaign LIMIT 200

Right now my campaigns are all from one company. So as a campaign is run the 'sent' column for that email's database entry is marked with the current campaign. Since my campaigns are numbered in order I can always look up an email and now/asume that it was sent to the campaign number shown and all previous.

But this wont help if lets say I want to add 5000 emails to the same database and run the previous campaigns. Lets say the current campaign is '7'. As soon as this email gets run through the current campaign it will be marked as 'sent' = 7.

Therefore if I try to run campaign 6 my code will think it was already run.

Is the best way really to have the sending script generate entries to a third database with email, campaign. That would mean this third database would contain 70,000 entries that I would have to query against emails in the mail list to see if they had been sent already or not. Isn't this a lot of overhead?

My main concern is that I will be tackling a 500k record mail list soon and this seems like to big of overhead to be checking emails to send against emails listed as sent in a second table.

Ron

[johanafm]

Assuming you only ever run one campaign at the time, e.g. no emails for campaign 6 will ever start being sent until campaign 7 is completed

"... AND NOT sent = $campaign ...";

Another way, with the same assumption as above is storing how many 200 runs you have made and

$start = $runs * 200;
$query = "... LIMIT $start, 200";

But no, a few million rows is no problem. Just index appropriate fields and you'll be fine.

[3dron]

Well, if I need to add emails to same list then continue running campaign, all will be set to 7. Not only can I not run campaign 6 just to catch what new emails where missed before, I also will have no stats showing if there new emails ever ran or not in the previous campaign.

Also, it serves no purpose to track how many 200 runs have been made. Because the 'active' mail list is changes lengths as people unsubscribe or bounce back. The email that was number 201 at start of campaign might be number 185 after emails 0-200 change active status.

[johanafm]

Well, four tables ought to do it in that case:
subsriber: id, name, email, ...
campaign: id, name, mail_to_send, ...
campaign_date: id, campaign_id, date (when to send emails), ...
sent: subscriber_id, campaign_date_id

[3dron]

Sounds good to go.

So it's not overkill to say have 500,000 records in the sent table. And to do a query loop comparing subscriber table to sent table to pull out 200 emails?

I am assuming my sent table might get as large as 500,000 records as 100k emails are sent to 5 campaigns over time.

[johanafm]

As long as you index all columns involved in joins and where clauses, there will be no problems.