PHP Reminder System

Soldato
Joined
8 Oct 2005
Posts
4,185
Location
Midlands, UK
Hi,

I've been set the task of creating a simple reminder system (where the user can specify a date and time) using php and mysql, but could do with some opinions. I have a simple reminders table:
ID, reminderText, date, userID, status (when reminder has been sent update staus).

I'm having a bit of trouble with my logic though :(

I was thinking of setting up a cron job to run every 15 mins (E.g. at 1:15, 1:30 etc.) to select:

  • status = 0 (no sent)
  • reminder date equals todays date
  • the reminder time is 30 mins before current time
I'm having a bit of trouble understanding the logic as the script runs every 30 mins and how I should store my dates/times (E.g. as a timestamp or as as two seperate fields to make comparing easy).

Thanks
 

Pho

Pho

Soldato
Joined
18 Oct 2002
Posts
9,281
Location
Derbyshire
SQL Server syntax but it should be the same pretty much..

After you've sent an email:
PHP:
UPDATE [reminder] SET last_sent = GETDATE()

And alter your select logic to (you'll have to play with it if you store times in your date field to get it to work):
PHP:
SELECT * FROM [reminder] WHERE status = 0 AND [reminder_date] = GETDATE() AND last_sent < GETDATE()
 
Soldato
OP
Joined
8 Oct 2005
Posts
4,185
Location
Midlands, UK
Woohoo, think I got it. Found a really useful mysql function along the way too to solve the fact that the script runs every 30 minutes:

PHP:
SELECT *, DATE_ADD(NOW(), INTERVAL 30 MINUTE) AS plus30Mins
FROM reminders 
WHERE reminderDate > NOW() 
AND reminderDate < DATE_ADD(NOW(), INTERVAL 30 MINUTE)
AND userID = '1'
AND remStatus = '0'
 
Last edited:
Top Bottom