title: Notification system using php and mysql
body: <p>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".</p>
  
  <p><strong>1. My initial approach was using MYSQL triggers:</strong></p>
  
  <p>I used a Mysql <code>AFTER INSERT trigger</code> to add records to a table named <code>notifications</code>. Something like.</p>
  
  <pre><code>'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);'
  </code></pre>
  
  <p>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.</p>
  
  <p>Notifications are retrieved with something like</p>
  
  <pre><code>SELECT n.* from notifications n 
  JOIN user_class on user_class.class_id = n.class_id where user_class.user_id = X;
  </code></pre>
  
  <p>Note: table user_class link user to class "user_id,class_id,subject_id" -subject is null unless user is a teacher'</p>
  
  <p>Now my next challenges are.</p>
  
  <ol>
  <li>how to keep track of new vs old notifications per user?</li>
  <li>how can i aggregate notifications that are similar to user into one row ? </li>
  </ol>
  
  <p>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'.</p>
  
  <p>Thanks alot</p>
  
  <p><strong>Edit</strong></p>
  
  <p>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</p>
  
  <pre><code>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
  </code></pre>