PHP Reminder System

Soldato
Joined
8 Oct 2005
Posts
4,184
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
 
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()
 
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:
Back
Top Bottom