Skip to content
Advertisement

Notification system using php and mysql

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.

JavaScript

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

JavaScript

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.

  1. how to keep track of new vs old notifications per user?
  2. 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’.

Thanks alot

Edit

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

JavaScript

Advertisement

Answer

Well this question is 9 months old so i’m not sure if OP is still in the need of an answer but due the many views and the tasty bounty I would like to also add my mustard (German saying..).

In this post I will try to make a simple explained example on how to start building a notification system.

Edit: Well ok this turned out way, way, way longer than I expected it to be. I got really tired in the end, i’m sorry.

WTLDR;

Question 1: have a flag on every notification.

Question 2: Still store every notification as a single record inside your database and group them when they are requested.


Structure

I assume that the notifications will look something like:

JavaScript

Behind the curtains this could look something like this:

JavaScript

Note: I don’t recommend to group the notifications inside the database, do that on runtime this keeps things a lot more flexible.

  • Unread
    Every notification should have a flag to indicate if the recipient has already opened the notification.
  • Recipient
    Defines who receives the notification.
  • Sender
    Defines who triggered the notification.
  • Type
    Instead of having every Message in plain text inside your database create types. This way you can create special handlers for different notification types inside your backend. Will reduce the amount of data stored inside your database and gives your even more flexibility, enabled easy translating of notification, changes of past messages etc..
  • Reference
    Most notifications will have a Reference to a record on your database or your application.

Every system I have been working on had a simple 1 to 1 reference relationship on a notification, you might have an 1 to n keep in mind that I will continue my example with 1:1. This also means that I don’t need a field defining what type of object is referenced because this is defined by the notification type.

SQL Table

Now when defining a real table structure for SQL we come to a few decisions in terms of the database design. I will go with simplest solution which will look something like this:

JavaScript

Or for the lazy folks the SQL create table command for this example:

JavaScript

PHP Service

This implementation depends completely on the needs of your application, Note: This is an example not the golden standard on how to build an notification system in PHP.

Notification model

This is an example base model of the notification itself, nothing fancy just the needed properties and the abstract methods messageForNotification and messageForNotifications we expected being implemented in the different notification types.

JavaScript

You will have to add a constructor, getters, setters and that kind of stuff by yourself in your own style, i’m not going to provide a ready to use Notification system.

Notification Types

Now you can create a new Notification subclass for every type. This following example would handle the like action of a comment:

  • Ray has liked your comment. (1 notification)
  • John and Jane liked your comment. (2 notifications)
  • Jane, Johnny, James and Jenny liked your comment. (4 notifications)
  • Jonny, James and 12 others liked your comment. (14 notifications)

Example implementation:

JavaScript

Notification manager

To work with your notifications inside your application create something like a notification manager:

JavaScript

The notificationAdapter property should contain the logic in direct communication with your data backend in the case of this example mysql.

Creating notifications

Using mysql triggers is not wrong, because there is no wrong solution. What works, works.. But I strongly recommend to not let the database handle application logic.

So inside the notification manager you might want to do something like this:

JavaScript

Behind the add method of the notificationAdapter can be a raw mysql insert command. Using this adapter abstraction enables you to switch easily from mysql to a document based database like mongodb which would make sense for a Notification system.

The isDoublicate method on the notificationAdapter should simply check if there is already a notification with the same recipient, sender, type and reference.


I cannot point out enough that this is only a example. (Also I really have to shorten the next steps this post is getting ridiculously long -.-)


So assuming you have some kind of controller with an action when a teacher uploads homework:

JavaScript

Will create a notification for every teacher’s student when he uploads a new homework.

Reading the notifications

Now comes the hard part. The problem with grouping on the PHP side is that you will have to load all notifications of the current user to group them correctly. This would be bad, well if you have only a few users it would probably still be no problem, but that doesn’t make it good.

The easy solution is to simply limit the number of notifications requested and only grouping these. This will work fine when there are not many similar notifications (like 3-4 per 20). But lets say the post of a user / student gets about a hundred likes and you only select the last 20 notifications. The user will then only see that 20 people liked his post also that would be his only notification.

A “correct” solution would be grouping the notifications already on the database and selecting only some samples per notification group. Than you would just have to inject the real count into your notification messages.

You probably didn’t read the text below so let me continue with a snippet:

JavaScript

Now you know what notifications should be around for the given user and how many notifications the group contains.

And now the shitty part. I still could not find out a better way to select a limited number of notifications for each group without doing a query for every group. All suggestions here are very welcome.

So I do something like:

JavaScript

I will now continue assuming that the notificationAdapters get method implements this grouping and returns an array like this:

JavaScript

Because we always have at least one notification in our group and our ordering prefers Unread and New notifications we can just use the first notification as a sample for rendering.

So to be able to work with these grouped notifications we need a new object:

JavaScript

And finally we can actually put most of the stuff together. This is how the get function on the NotificationManager might look like:

JavaScript

And really finally inside a possible controller action:

JavaScript
User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement