How do you send weekly reminders?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
kinetek
Forum Newbie
Posts: 5
Joined: Fri May 21, 2004 2:20 am

How do you send weekly reminders?

Post by kinetek »

Hi,

Well, here's what I want to do. Send weekly reminders (up to 2) after a someone orders something. I'm thinking of running a cron job in cron.daily for this php script. I've got everything down except for comparing dates.

The date is currently formatted like this in the database:
YYYY-MM-DD HH:MM:SS
2004-05-18 10:57:33

How do I make it so the script compares the current server time and the time the order was placed? If it's longer than 7 days, then execute the next part. Any help would be greatly appreciated.
leenoble_uk
Forum Contributor
Posts: 108
Joined: Fri May 03, 2002 10:33 am
Location: Cheshire
Contact:

Post by leenoble_uk »

SELECT * FROM your_table WHERE UNIX_TIMESTAMP(date_column) < (UNIX_TIMESTAMP(NOW())-604800);

This will select every row where the date column is older than 604800 seconds from the current time (7 days)
kinetek
Forum Newbie
Posts: 5
Joined: Fri May 21, 2004 2:20 am

Post by kinetek »

Thanks. So if I want to do 2 weeks I would just multiply that number by 2 right? So it would be:

SELECT * FROM your_table WHERE UNIX_TIMESTAMP(date_column) < (UNIX_TIMESTAMP(NOW())-10295600);
leenoble_uk
Forum Contributor
Posts: 108
Joined: Fri May 03, 2002 10:33 am
Location: Cheshire
Contact:

Post by leenoble_uk »

yup
leenoble_uk
Forum Contributor
Posts: 108
Joined: Fri May 03, 2002 10:33 am
Location: Cheshire
Contact:

Post by leenoble_uk »

One caveat though. This method is finding all records where the date is EXACTLY one week older than NOW.
So if it's 10:54 am then any record from a week ago at 10:55 won't be caught. Either this needs to be run at midnight or you need some more mathematical jiggery pokery to capture a less picky set.
I generally stick to using unix timestamps because you can do simple maths with them like the above and you don't have to take account of months, days, hours etc.

So what you may want to do is work out the unix timestamp for midnight tonight and use that to capture records just from 1 week ago today and run it every day.

Code: Select all

This isn't any actual code but a description of the method.

get unix timestamp for this second - time()
get a formatted date for today - date("d M Y", time());
get unix timestamp for midnight tonight or 11:59.

select all records where the unix_timestamp is between 604800 and 518400 seconds older than midnight tonight.

So as of now it will give you every order placed in the 24hr period which was last Friday.
The tomorrow it'll do last Saturday and so on.
That way there is no lag of up to 13 days for some people while others get something in 7.
Post Reply