I wanted to implement a notification system for our school, it's a php/mysql webapp that is not opened for public, so it doesn't receive much traffic. "daily 500-1000 visitor".
1. My initial approach was using MYSQL triggers:
I used a Mysql
AFTER INSERT trigger to add records to a table named
notifications. Something like.
'CREATE TRIGGER `notify_new_homwork` AFTER INSERT ON `homeworks` FOR EACH ROW INSERT INTO `notifications` ( `from_id`, `note`, `class_id`) VALUES (new.user_id, concat('A New homework Titled: "',left(new.title,'50'), '".. was added' ) ,new.subject_id , 11);'
This kind of black magic worked very well, yet i couldn't keep track of if this notification is new "to show count of new notifications for user". so i added a page named notifications.
Notifications are retrieved with something like
SELECT n.* from notifications n JOIN user_class on user_class.class_id = n.class_id where user_class.user_id = X;
Note: table user_class link user to class "user_id,class_id,subject_id" -subject is null unless user is a teacher'
Now my next challenges are.
- how to keep track of new vs old notifications per user?
- how can i aggregate notifications that are similar to user into one row ?
example if 2 user commented on something, then do not insert a new row, just update the old one with something like 'userx and 1 other commented on hw'.
As per answer below, to set a read/unread flag on row, i will need to have a row for each student not just a row for the whole class.. which means editing the trigger to something like
insert into notifications (from_id,note,student_id,isread) select new.user_id,new.note,user_id,'0' from user_class where user_class.class_id = new.class_id group by user_class.user_id