Date question again

[dustbuster]

I've searched through the forums but haven't quite found what I need. I would like to do the following.

1)
I have a table that contains some information as well as 2 columns: date_registered and date_expires.

I would like to add the current date to date_registered everytime I add a new row. I would also like to put into date_expires the current date plus a certain amount of time.

Eg:
date_registered="2003-01-19";
date_expires="2003-01-19" + 6 months;

How do I do this with a MySQL query?

2)
I would like to write some sort of script that goes through this table everyday and sees if one of my entries is about to expire. It would then send off an e-mail telling me which entry is about to expire.

Any help would be appreciated.
D.

[nnichols]

1)
I have a table that contains some information as well as 2 columns: date_registered and date_expires.

I would like to add the current date to date_registered everytime I add a new row. I would also like to put into date_expires the current date plus a certain amount of time.

Eg:
date_registered="2003-01-19";
date_expires="2003-01-19" + 6 months;

How do I do this with a MySQL query?

INSERT INTO tbl_name (blah, blah, blah, date_registered, date_expires)
VALUES ('blah', 'blah', 'blah', NOW(), DATE_ADD(NOW(), INTERVAL 1 YEAR))

this inserts current date into date_registered and (current_date+1year) into date_expires.

2)
I would like to write some sort of script that goes through this table everyday and sees if one of my entries is about to expire. It would then send off an e-mail telling me which entry is about to expire.

the easiest way to do this is to write a script to do it and then run it as a CRONTAB. If you do a search on here you should find a few good posts on this sort of thing.

Hope this helps 😉

[dustbuster]

Thanks that is what I was looking for!

If I have INTERVAL 6 MONTHS will that add six months or is it different syntax?

[nnichols]

Almost got it -

INTERVAL 6 MONTH