How to implement push notification system on a mysql database with node.js

javascript mysql node.js push-notification

11,459

Solution 1

I have made a simple app like your requirement.

You can get help from following lines of code.You need to understand the basics of code. after that you will easily achieve your target. most of things from your requirement covered in this demo app.

Its not a exact but you will meet your target through this.

In this example a status post by any user will emit to all other users also at same time. we can manipulate it to achieve "1 new status".

make a table in database where your entries to be saved CREATE TABLE status

(
    `status_id` INT NOT NULL AUTO_INCREMENT,
    `s_text` TEXT,
    `t_status` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
     PRIMARY KEY ( `status_id` )
);

//server.js

var app = require("express")();
var mysql = require("mysql");
var http = require('http').Server(app);
var io = require("socket.io")(http);

/* Creating POOL MySQL connection.*/

var pool = mysql.createPool({
    connectionLimit: 100,
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'fbstatus',
    debug: false
});

app.get("/", function(req, res) {
    res.sendFile(__dirname + '/index.html');
});


io.on('connection', function(socket) {
    console.log("A user is connected");

    socket.on('status added', function(status) {
        add_status(status, function(res) {
            if (res) {
                io.emit('new status', status);
            } else {
                io.emit('error');
            }
        });
    });
});

var add_status = function(status, callback) {
    pool.getConnection(function(err, connection) {
        if (err) {
            connection.release();
            callback(false);
            return;
        }
        connection.query("INSERT INTO `status` (`s_text`) VALUES ('" + status + "')", function(err, rows) {
            connection.release();
            if (!err) {
                callback(true);
            }
        });
        connection.on('error', function(err) {
            callback(false);
            return;
        });
    });
}

http.listen(3000, function() {
    console.log("Listening on 3000");
});

//index.html

<html>
  <head>
    <title>Socket.io</title>
    <script src="/socket.io/socket.io.js"></script>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
    <link rel="stylesheet" href="//maxcdn.bootstrapcdn.com/bootstrap/3.3.2/css/bootstrap.min.css">
    <script src = "http://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/js/bootstrap.min.js"></script>
    <script>
    $(document).ready(function(){
          var socket = io();
          $("#add_status").click(function(){
            socket.emit('status added',$("#comment").val());
          });

          socket.on('new status',function(msg){
              var count = $('#count_status').text();
              var valCount = parseInt(count);
              if(valCount>=1) {
                  valCount = valCount+1;
              } else {
                  valCount = 1;
              }
              var showMsg = '<div id="count_status"> '+valCount+' </div>  new status';
              $("#show_comments").html(showMsg);

          });
    });
    </script>
  </head>
  <body>
    <div id="comment_box" style = "padding:5%;">
      <textarea id="comment" rows="5" cols="70"></textarea><br /><br />
      <input type="button" id="add_status" value="Add">
    </div>
      <div id= "show_comments"  class = "jumbotron"></div>
  </body>
</html>

Run the app with following command node Server.js

Now run http://localhost:3000/ in browser and to see the result open a new window in which you post a status and see your new status notification in both the window.

Thanks

Edited: This a great startup tutorial. a few thing needs modification.

connection.release() code ends up unreadable and not working. you should comets or remove it.

2.The actual output in my case:

Solution 2

You can do it 2 ways:

Query the server every n seconds for any new messages. Pass a timestamp of the last time you checked as a parameter and if any notification since the last check, return as json and display them in the client. This is called a pull strategy.
Or you can use websockets which maintains a permanent connection between your client and server, and then you can send notifications to the client from your server code in real-time. See socket.io tutorials. This is called a push strategy.

11,459

hamed

Web developer with Java Spring MVC. Familiar with php yii framework. Very interested in Object Oriented design and OO design patterns. Client side programmer with angular, javascript and jquery.

Updated on June 04, 2022

Comments

hamed about 2 years

I'm totally new to node.js and I want to implement push notification system on a MySql database. I have a notification table in my database. In this table I have store recipient_id that specify the recipient of the notification. Now I want when a new notification with recipient_id is equal to current logged in user's id notify that user. Something like Stackoverflow If you are in the for example java tagged questions, every time a new question with java tag create, a notification appear on top of the page : 1 question with new activity. Sorry for my poor English. Please help me to implement this system, because I'm new to it.
hamed over 9 years

Thank you for your reply. I know the solution is socket.io. But my question is "how" can implement it in real time way.