Creating a notification system for your website using Javascript, PHP and MySQL

Creating a notification system for your website using Javascript, PHP and MySQL

Create a notification system for your website that allows you to notify users about activity relating to them. Asynchronously sort and load notifications from your server to a client.


Many interactive websites these days have a system to notify users of specific actions that have been taken in relation to them, perhaps another user on your site has commented on their picture or left a message on their page, either way it is beneficial to notify that user so they are more likely to engage with the activity.


Creating a notification system for your website can seem like a complicated task, but by starting with a simple example, you'll be able to implement and build on this knowledge.


For this tutorial we will assume a working knowledge of Javascript and PHP as well as database programming with MySQL.


Creating a table to store notifications


The first thing you'll need is a place to store notifications, for this we'll use a database table called "notification". The fields you will need to include will vary depending on your needs but as a basic example we'll use the following:


- notificationID ( integer ) : the primary key we'll use to keep track of notifications


- type ( string ) : as your site may have many sources of notifications such as comments or messages, it will be handy to keep track of them with this "type" field.


- forUser ( string ) : for this example we'll assume you have a user table storing the unique username as a primary key, this field will act as a foreign key to that user table.


- entityID ( integer ) : stores a reference to the specific entity (piece of content) that the notification relates to ( ie. postID, comment ID, message ID, rating ID etc. )


- read ( boolean ) : stores whether or not the notification has been read by the user ( as MySQL has no boolean data type, we'll use the TINYINT datatype to store either a 1 or 0 )


- time ( date-time ) : stores the time at which the notification was added or updated.


The MySQL statement used to create this table is as follows:


CREATE TABLE `notification`(   
 `notificationID` INT PRIMARY KEY AUTO_INCREMENT,   
 `type` VARCHAR( 10 ),   
 `forUser` VARCHAR( 50 ),   
 `entityID` INT,   
 `read` TINYINT( 1 ) DEFAULT 0,   
 `time` DATETIME DEFAULT NOW(),   
 FOREIGN KEY( `forUser` ) REFERENCES `user`( `username` )   
);   


This particular notification system will work by adding a record each


time a unique action is performed such as commenting on a specific post, it will store the ID of the post as a reference as well as the notification type "comment". If a specific action such as commenting has already been performed on that post, then it will simply update the "time" column to the latest time and the "read" column to false so the user is notified that there is new activity on that post.


If there is a record in the notification table of type "comment", it will search the comment table for comments relating to that specific post (referenced by entityID) and return the usernames of those who commented. An example notification that would be given to the user would be:


"bob, john and 3 others commented on your post"


For this example we will also use two extra tables "post" and "comment" which will be used as examples for the notifications.


post


CREATE TABLE `post`(  
 `postID` INT PRIMARY KEY,  
 `text` VARCHAR( 250 )  
);  


comment


CREATE TABLE `comment`(  
 `commentID` INT PRIMARY KEY,  
 `postID` INT,  
 `author` VARCHAR( 100 ),  
 `text` VARCHAR( 250 ),  
 FOREIGN KEY( `postID` ) REFERENCES `post`(`postID`)  
)  

The entityID field in the notification table will be referring to `postID` for notification type "comment".


Creating server-side script to add notifications


On the server, we will create a PHP function called "notify" that will check if a record exists where the "type", "forUser" and "entityID" fields are equal to the new notification you are trying to add, if no such record exists then it will create a new one, if that record does exist then it will update the "time" field to the current time and the "read" field to false


The "notify" function will be placed in its own PHP file so that it can be included in any other PHP file that needs it.


notify.php


function notify( $type, $forUser, $entityID ){   
 $con = new mysqli( "localhost", "root", "password", "testDB");   
   
 $sql = "SELECT `notificationID` FROM `notification` WHERE `forUser`='" . $forUser . "' AND `entityID`=" . $entityID . " AND `type`='" . $type . "';";   
 $result = $con->query( $sql );   
    
 // if query returned a row, it means the notification exists   if( $result->num_rows > 0 ){   
  // update the existing record, set read to false and time to the current time   
  $sql = "UPDATE `notification` SET `read`=0, `time`=NOW() WHERE `forUser`='" . $forUser . "' AND `entityID`=" . $entityID . " AND `type`='" . $type . "';";   
  $con->query( $sql );   
 }   
 else{  
  // insert new record with the details  
  $sql = "INSERT INTO `notification`( `type`, `forUser`, `entityID` ) VALUES( '" . $type . "','" . $forUser . "'," . $entityID . " );";   
  $con->query( $sql );   
 }  
 $con->close();  
} 


Note:

if the arguments given to this function can be altered somehow by the user, you should make use of

prepared statements with bound parameters

to mitigate SQL injection.


This function can be included in other scripts to make use of its functionality. It should be placed in scripts that are responsible for processing activity such as a script for posting comments on posts:


include("notify.php");  
  
$postID = 5 ; // an example post to which the comment is referring  
$username = "user1" ; // an example user who created the post  
  
//comment uploading  
  
notify( "comment", $username, $postID );  


Creating server-side code to fetch notifications from the database


Now we will need to create the server-side code to fetch notifications from the database and return them to the client. This script will fetch 10 records at a time ordered by time in descending order (to get the most recent notifications), it will store the amount of loaded notifications in a cookie.


Once the records have been loaded into an array, the script will loop over each notification and acquire details about them to create the text that will display to the user.


As this script will be called using AJAX from Javascript, we'll include a GET parameter that will allow you to reset the cookie that stores how many notifications have been loaded so that you can choose to fetch from the start or continue from where it left off.


This script will be the most complicated part of the notification system, so take your time to understand it.


getNotifications.php


$con = new mysqli( "localhost", "root", "password", "testDB");  
  
$reset = (int) $_GET[ "reset" ]; // either 1 or 0 ( true and false )  
  
$username = "user1"; // the user who's notifications we will be loading  
  
if( $reset === 1 ){  
 $sql = "SELECT * FROM `notification` WHERE `forUser`='" . $username . "' ORDER BY `time` DESC LIMIT 10;";  
 setcookie( "loadedNotifications", "10", time() + 86400, "/" ); // store the cookie holding the amount of loaded notifications  
}  
else{  
 $loadedNots=(int) $_COOKIE[ "loadedNotifications" ]; // get the amount of previously loaded notifications  
 $sql = "SELECT * FROM `notification` WHERE `forUser`='" . $username . "' ORDER BY `time` DESC LIMIT " . $loadedNots . " 10;";  
 $loadedNots = (string)( $loadedNots + 10 ); // calculate how many notifications have been loaded after query  
 setcookie( "loadedNotifications", $loadedNots, time() + 86400, "/" ); // update cookie with new value  
}  
  
$result = $con->query( $sql );  
  
$notifications = array(); // declare an array to store the fetched notifications  
  
if( $result->num_rows > 0 ){   
 while( $row = $result->fetch_assoc() ){  
  $notifications[] = array( "id" => $row[ "notificationID" ], "type" => $row[ "type" ], "entityID" => $row[ "entityID" ], "read" => $row[ "read" ], "text" => "" );  
 }  
}   
else{  
 // no more notifications  
}  
  
/*  
* now we need to find the activity that relates to the notification  
* and create a text message that will be displayed to the user  
* containing the users who are responsible for that particular activity  
*/  
  
for( $i = 0; $i < count( $notifications ); $i++ ){  
 $sql = ""; // reset query string each time loop runs  
   
 // use different code for each type of notification ( ie. comments or ratings )  switch( $notifications[ $i ][ "type" ] ){  
  case "comment":  
   $sql = "SELECT `author` FROM `comment` WHERE `postID`=" . $notifications[ $i ][ "entityID" ] . ";";  
   $result = $con->query( $sql );  
     
   /*  
   * For this example we want a maximum of two names in the notification text  
   * if there are more than 2, then we'll include those as a number  
   */  
   if( $result->num_rows === 1 ){  
    $row = $result->fetch_assoc();  
    $name = $row[ "author" ];  
    $notifications[ $i ][ "text" ] = $name . " commented on your post";  
   }  
   elseif( $result->num_rows === 2 ){  
    $row = $result->fetch_assoc();  
    $name1 = $row[ "author" ]; // fetch first name  
    $row = $result->fetch_assoc();  
    $name2 = $row[ "author" ]; // fetch second name  
    $notifications[ $i ][ "text" ] = $name1 . " and " . $name2 . " commented on your post";  
   }  
   elseif( $result->num_rows > 2 ){  
    $total = $result->num_rows - 2 //fetch the number of users who commented minus the two names we will use  
    $row = $result->fetch_assoc();  
    $name1 = $row[ "author" ]; // fetch first name  
    $row = $result->fetch_assoc();  
    $name2 = $row[ "author" ]; // fetch second name  
    $notifications[ $i ][ "text" ] = $name1 . ", " . $name2 . " and " . $total . " others commented on your post";  
   }     
   break;  
  // other cases to suit your needs  
 }  
}  
  
echo( json_encode( $notifications ) ); // convert array to JSON text  


Creating client-side code to request and display notifications


Now all we need to do is make an AJAX request to getNotifications.php from Javascript ( after notifications have been added through activity using notify.php ). To do this we'll create a function that will use a simple XMLHttpRequest object.


function getNotifications( reset ){  
 var xmlhttp = new XMLHttpRequest();  
 xmlhttp.onreadystatechange = function() {  
  if ( xmlhttp.readyState === 4 && xmlhttp.status === 200 ) {  
   var response = xmlhttp.responseText;  
   var notifications=JSON.parse( response ); // create JSON object from response  
    
   for( var i = 0 ; i < notifications.length ; i++ ){  
    var notificationID = notifications[ i ].id;  
    var entityID = notifications[ i ].entityID;  
    var text = notifications[ i ].text;  
    var type = notifications[ i ].type;  
    var read = notifications[ i ].read;  
  
    // add notification into your HTML here  
   }  
  }  
 }  
 xmlhttp.open("GET", "getNotifications.php?reset=" + reset, true);  
 xmlhttp.send();  
}  


Now that you can access the details of notifications, you can add each notification into your HTML and style it however you like. You can also use the entityID to link to a specific piece of content so your user can click a notification and be lead to the content.


If you have any questions or need extra help, feel free to leave a comment and we'll help you out!



Christopher Thornton@Instructobit 6 years ago
or