Create live notifications using PHP and MySQL just like in Facebook

Introduction

In order to create notification system which is used as popup yellow notification highlighter in Facebook following approach can be taken.Let me introduce some snippets of the code in PHP/MySQL/JavaScript/HTML to show the concepts.

It can be applied anywhere else also on other platforms.The complete solution can be broken into four components:

  • Database table to store notifications logged in user-wise
  • Server side script which manages notification
  • Front end JavaScript code to display notifications
  • Administrative screen to push notifications to logged in users
FIRST STEP:  Create database table to store notifications logged in user-wise

Let us create a MySQL table [notifications] to store notifications on server side.
     id-Long Integer - PK (Primary Key, Auto Incremented)
        Unique Identification For The Notification
    creation_date_time - varchar(30)
        Store date time stamp as text when notifications are created by administrator
    view_date_time - varchar(30)
        Store date time as text when it is viewed by logged in user
    user_id- Long Integer
        Unique Identification (FK) from the [user] table for whom notification has been created
    notification_text - varchar(255)
        Text notification in HTML format so that images can also be displayed
    is_viewed- varchar(3)
        "YES/NO" to indicate whether notification has been viewed or not by logged in user

 Following is the MySQL command to create the table structure:

     
 CREATE TABLE `notifications` (
        `id` int(11) NOT NULL,
        `creation_date_time` varchar(30) NOT NULL,
        `view_date_time` varchar(30) NOT NULL,
        `user_id` int(11) NOT NULL,
        `notification_text` varchar(255) NOT NULL,
        `is_viewed` varchar(3) NOT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

SECOND STEP: Server side script which manages notifications

Let us create a .PHP file (notifications_controller.php) which will be used to manage notifications:

<?php
     //This script will accept following three commands as GET parameter and will act on as follows:
     //INSERT_NOTIFICATION - Insert the notification in the [notifications] table
     //FETCH_NOTIFICATIONS - Get all notifications for a user from [notifications] table
     //DELETE_NOTIFICATIONS - Deletes all notifications for a user from [notifications] table
     //SET_IS_VIEWED_FLAG - Call this command from front-end when user has viewed the notification
    
     $command = "";
     if (isset($_GET['command'])) { $command = $_GET['command']; }
    
     $user_id = "";
     if (isset($_GET['user_id'])) { $user_id = $_GET['user_id']; }
    
     if (strcmp($user_id,"") == 0) 
        { echo "<br>Error: user_id must be specified as GET parameter."; exit; }
    
     if (strcmp($command,"") == 0) 
       {
        echo "<br>Sorry, no command specified. Exiting...";
        exit;
       }
    
     //Open the MySQL database connection here and select the database in which [notifications] table has been created
     $servername = "localhost";
     $username = "root";
     $password = "";
     $dbname = "notifications_db";
    
     //Create connection
     $conn = new mysqli($servername, $username, $password, $dbname);
     //Check connection
    if ($conn->connect_error) 
        {
           die("Connection failed: " . $conn->connect_error);
        }
    
    if (strcmp($command,"INSERT_NOTIFICATION") == 0) 
    {
        $notification_text = "";
        if (isset($_GET['notification_text'])) { $notification_text = $_GET['notification_text']; }
    
        $is_viewed = "";
        if (isset($_GET['is_viewed'])) { $is_viewed = $_GET['is_viewed']; }
        if (strcmp($notification_text,"") == 0) 
          {
            echo "<br>Error: notification_text must be specified as GET parameter.";
            exit;
         }
        if (strcmp($is_viewed,"") == 0) 
         {
           echo "<br>Error: is_viewed must be specified as GET parameter.";
           exit;
         }

       //Insert notification into the table for given user
       $sql = "select * from notifications";
       $result = $conn->query($sql);
       $id = mysql_num_rows($result);
       $id = $id + 1;
       $creation_date_time = date("Y:m:d H:i:s");
       $view_date_time = date("Y:m:d H:i:s");
       $is_viewed = “NO”;
       $sql = "INSERT INTO `notifications`(`id`, `creation_date_time`, `view_date_time`, 
       `user_id`, `notification_text`, `is_viewed`) VALUES ('".$id."',"'.$creation_date_time."','".$view_date_time."','".$user_id."','".$notification_text."','".$is_viewed."')";
       $result = $conn->query($sql);
       $conn->close();
       exit;
    }


if (strcmp($command,"FETCH_NOTIFICATIONS") == 0) 
    {
       //Select all notifications for a given user_id and return in JSON format
       $sql = "select * from notifications where use_id = '".$user_id."' and is_viewed = ‘NO’ ";
       $res = mysqli_query($con,$sql);
       $result = array();
       while($row = mysqli_fetch_array($res)) 
        {
           array_push($result,array('id'=>$row[0],'creation_date_time'=>$row[1],
           'view_date_time'=>$row[2],'user_id'=>$row[3],'notification_text'=>$row[4],'is_viewed'=>$row[5]));
        }
        $conn->close();
        echo json_encode(array("result"=>$result_array));
        //Here you can format all notifications into well-organized HTML code just like Facebook in order to present in nice way
        //For example purpose , it is being returned in JSON format
        exit;
    }
    
    
if (strcmp($command,"DELETE_NOTIFICATIONS") == 0)
       {
          //Delete all notifications for a given user_id
          $sql = "delete from notifications where use_id = '".$user_id."'";
          $res = mysqli_query($con,$sql);
          $conn->close();
          exit;
       }
       
if (strcmp($command,"SET_IS_VIEWED_FLAG") == 0) 
  {
     //Update here is_viewed flag to YES for notification and user_id
     exit;
  }
?>     


THIRD STEP: Front end JavaScript code to display notifications:

 In order to display notifications in front-end HTML page, we can make Ajax call to our controller (notifications_controller.php) like this:

display_notifications.html

<html>
<head>
<script>
function show_notifications()
{
  //user_id = 1 First User's Id
  //command = FETCH_NOTIFICATIONS
  var show_notifications_url = "http://www.yourdomanin.com/notifications_controller.php?command=FETCH_NOTIFICATIONS&user_id=1";
       $( document ).ready(function() 
         {
            $.ajax({
                     url: show_notifications_url,
                     cache: false,
                     success: function(html) 
                      {
                          $("#notification_results").append(html);
                          //Change the content of the DIV in User's profile page where notifications will be displayed
                      } 
                   }); 
         });
} //function show_notifications

function startTimer() 
   {
      //call show_notifications
      show_notifications();
      //then start interval
      setInterval(show_notifications, 30000); //Refresh after 30 seconds
   } //function startTimer()
</script>
</head>

<body onload = "startTimer()">
   //Call show_notifications function automatically when HTML page is loaded and set a JavaScript timer to keep it refreshing every 30 seconds
   <div id="notification_results"></div>
</body>
</html>

FOURTH STEP: Administrative screen to push notifications to logged in users: 
Create a GUI in HTML format which will accept user's details and notification details and it will push into [notifications] table.

create_notifications.html

<html>
<head>
<script>

function create_notifications() 
{
     //user_id = 1 First User's Id
     //command = INSERT_NOTIFICATION
     var create_notifications_url = 
     "http://www.yourdomanin.com/notifications_controller.php?command=INSERT_NOTIFICATION&user_id=1&creation_date_time=201610101200&view_date_time=2016101012000&notification_text=Hello&is_viewed=NO";
     $( document ).ready(function() 
     {
         $.ajax({
                   url: create_notifications_url,
                   cache: false,
                   success: function(html)
                   {
                   } 
                }); 
    }); 
}
</script>
</head>

<body>
  <input type="button" value="Create Notification" onclick="create_notifications()">
</body>
</html>
 



Summary

It is an Ajax based notification system using MySQL and PHP.

5 comments:

  1. This is excellent information. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
    Mobile Marketing Service
    Mobile Marketing Companies

    ReplyDelete
  2. Dear Jessica,

    Thanks for appreciating my post. You are welcome.

    Regards
    The out of box salesman

    ReplyDelete
  3. This article is very much helpful and i hope this will be an useful information for the needed one. Keep on updating these kinds of informative things...
    Digital Marketing SMS
    Digital Marketing Text
    Digital Mobile Marketing
    Text Whitelabel Solutions
    sms Whitelabel Solutions

    ReplyDelete
  4. Dear Rosy,

    Thank you for appreciating my post. You are welcome.

    Regards


    ReplyDelete