[SOLVED] Outputting new records

[clay1]

I have a site which I upload records to-- we can call them leads. They are coded with a market and given a date they are uploaded.

 

I then have a query script which I can select a date and then email the leads based on that date. However this requires me looking up and seeing when the last day I emailed each market was.

 

I've been thinking of ways I can automate this so all new leads are emailed automatically each week or whatever(running a cron job)

 

I thought about adding a timestamp field to my markets table which is updated whenever the email script is run for that market.

 

I also thought of having a flag column for each record that is set when the record has been emailed.

 

Any suggestions on the best way to go about this?

[btherl]

I would go with the timestamp option.  More information is usually better.  Then you can mail any market with a timestamp older than 7 days.

 

Another thing I would consider is a table recording every batch of emails ever done.

 

Are you using postgres?

[clay1]

Using pg yes.

 

How would I go about updating the timestamp?

 

Could you elaborate on the table with the emails?

 

Thanks

[btherl]

Well if it's a market that you want the timestamp associated with, I would do something like this:

 

ALTER TABLE markets ADD COLUMN last_mail_ts timestamp without time zone;

 

Or you could make it with time zone if you prefer.

 

To check if it's time for a mailout:

 

SELECT market, now() - last_mail_ts > '7 days'::interval AS its_time FROM markets;

 

Then each time you do a mailout:

 

UPDATE markets SET last_mail_ts = now() WHERE market = 'foomarket';

 

The other table I was talking about is just for logging, in case you want to look back and ask "When did i send mail to foomarket?".  It would be something like:

 

CREATE TABLE market_email_log
(
  mail_ts timestamp without time zone not null default now(),
  market text not null,
);

 

You might add a primary key or maybe additional information about what was mailed out.

[clay1]

Cool thanks a lot!