PHP and MySQL – Notify About Starting Event

MySQLPHP

I am having quite a hard time figuring out how to solve this problem. Basically I have a mini social network with notifications and events. I want to add such functionality where 1 day before an event is about to start and 1 hour before the event starts, users participating in the event get notifications that the event is respectively starting in 1 day or 1 hour.

I'm using MySQL + PHP so this is how I think about it:

In my events table I have a column of type DATETIME which holds the starting date and time of each event. In order to be able to send notifications for starting events I need to run checks and this is where I'm having a bit of trouble. If I check with this technique

SELECT * FROM events WHERE starting > NOW() AND starting - INTERVAL 1 DAY < NOW()

I will check for events with a starting time between 00:00 today and 23:59 today. However this means that I am only supposed to run 1 check per day which is not reliable and is also impossible to solve the 1 hour before event starts notification problem. Also I think this would be inefficient because this query is not a simple select, meaning that only one of the expressions will benefit from the index, the other one will still have to loop through all results the first one found. I suppose this may be a minimalist concern but it matters to me.

Can anyone suggest another solution to this problem? I am not looking for complete code but an idea that can be realized in either MySQL or PHP, but I'm looking for it to be efficient and simple.

Also another concern I have, as a side question: in time, the table events will eventually get filled up with events and many if not most of them will be past events that have already happened, therefore I am asking myself if it would be a good idea to create another table for past events and move those there, in order to not slow down mysql when searching for events that are about to start to send out notifications?

Best Answer

Create a table which will store information about notifications: 

TABLE notifications
    event_id
    user_id
    datetime // date and time when notification must be sent

Every time someone joins some event, you should insert a new row in the notifications table.

To notify users, you need script which will perform a mysql query like: 

SELECT * FROM notifications WHERE `datetime` <= NOW()

After selecting users, notify them and delete the rows from table.

This script may be started automatically by cron. The shorter the interval between script starts, the more precise the notification.

Related Topic