How do I tell if this email was sent already?

[frogggg]

I have a page that checks in two different, ever changing, tables for matches and emails users if matches were found. I set the page to run once a day. But, how can I keep track of which matches were already found so I don't keep sending the same email every day with the same info? I suppose I could make another table in the db to hold all the matches and flag the records when the emails were sent, but this is a huge waste of resources.
Does anyone have any other ideas of how I could do this?

Thanks.

 

[Jorgandr]

My initial solution for this is to add another field to one of the tables (if there isn't one already) called (DateSent). When your code that identifies matches should also Update date() into the DateSent field for that matching record. the Next day when the process is run again the code that identifies matches will just have a WHERE Clause like WHERE DateSent = "", effectively omitting the ones you just sent. Not only does it solve that problem but it will also give you the option of seeing on what date that match was sent.

 

[frogggg]

This idea won't work because the data is changing every day. Even if a message was sent today, is no guarantee I will need to send another one tomorrow. I guess I have no choice but to use another table.
Thanks.

 

[B827]

Is it possible to go back to the source data and record the in a Field the the date the data was updated or added. Then compare this date with the date last sent and todays date and make a decision on that? Sandy

 

[rico14d]

I dont quite understand the structure of the two tables. Could you please describe them.

 

[frogggg]

Alright, I'll try to be a little more specific.
Two types of people visit my website: Candidates - people looking for jobs and posting resumes, and Employers - people who are looking for employees and posting jobs.
Among many other tables, there is a CandidateContactInfo table which has a CandidateSkills table related to it. A candidate can choose one or more skills that will be posted to his resume. There is also an JobInfo table which holds all info about a job post, and has a JobSkills table related to it which holds one or more skills that are required for this specific job post.
The asp page searches for all matches between JobSkills and CandidateSkills and emails the employer that there is a Candidate who matches and gives a link to find his resume and info.
Since (hopefully) candidates and employers are always adding more posts, I don't want to email the employer six times that the same candidate matches his skills. On the other hand, I can't just assume that just because today I found one match for his skills, tomorrow I won't find three more.
So now I hope you understand my question a little better, and if anyone has any brainstorms, I'd like to hear about them.

Thanks!

 

[rico14d]

You could try this.

Create another table called email_sent.
candidate_no,
Employer_no,
email_sent_no.

Each time you were going to send an email,
first check that there is not a record
in this table for the employer and the candidate.

If there isnt, send the email and add a record for the employer and candidate.

If there is, dont send the email