Node-Webkit mysql data to news feed

I working on a news feed tool that we can send remote news messages to a screen. Now i have done the back end of the system and working now on the frontend/client.

I will use node-web kit to show the news on the screen. I have already a connection with the database and it show the messages also in the console log. But i don't get it right to show it on the news feed page.

I try to make it looks like a CNN news feed banner: CNN feed banner

Styling is not the problem but i don't get the date from the json (mysql output) to the screen.

The output i get from mysql in the console looks like:

[{"id":1,"message":"Test message #1"},{"id":2,"message":"Test message #2"},{"id":3,"message":"Test message #3"}]

And the code from the client looks like:

<html>
  <head>
    <title>NewsFeed</title>

  <script>

  var mysql      = require('mysql');
  var connection = mysql.createConnection({
    host     : 'XXXXX',
    user     : 'XXXXX',
    password : '',
    database : 'XXXXX'
  });

  connection.connect(function(err) {
    if (err) {
      console.error('error connecting: ' + err.stack);
      return;
    }

    connection.query('SELECT * FROM message', function (error, results, fields) {
      if (error) throw error;
      json = JSON.stringify(results);
      console.log(json);
    });
  });

  </script>
  </head>
  <body>
  </body>
</html>

Do someone know how to read the Json information from the console to the HTML view?

And also is there a way the let the news feed automatic update when there is add a new message to the database?

UPDATE: I think auto refresh is not the best way. So i was thinking about remote refresh. The idea is;

User login on a backend system add a message and then press on a button to reload the page.

Like this image: Idea reload remote

Solution

Try this.

First of all, add a node element in you DOM which will contail all the messages.

<body>
    <h1>Messages</h1>
    <div id="message-container"></div>
</body>

Then make you function write in the DOM:

connection.connect(function(err) {
    if (err) {
      console.error('error connecting: ' + err.stack);
      return;
    }

    connection.query('SELECT * FROM message', function (error, results, fields) {
      if (error) throw error;
      json = JSON.stringify(results);
      console.log(json);

      var container = document.getElementById("message-container")

      // Use this in case you can't perform a more precise query
      container.innerHTML = "";

      results.forEach(function(item){
          var node = document.createElement("p");
          var textnode = document.createTextNode(item.message); 

          node.appendChild(textnode);
          container.appendChild(node); 
      })
    });
});

To keep you data up to date, you can do some good ol' polling:

function startTimer() {
    setTimeout(query, 10000)
}

function query() {
  connection.query('SELECT * FROM message', function (error, results, fields) {
      if (error) throw error;
      json = JSON.stringify(results);
      console.log(json);

      var container = document.getElementById("message-container")

      // Use this in case you can't perform a more precise query
      container.innerHTML = "";

      results.forEach(function(item){
          var node = document.createElement("p");
          var textnode = document.createTextNode(item.message); 

          node.appendChild(textnode);
          container.appendChild(node); 
          startTimer()
      })
    });
}

connection.connect(function(err) {
    if (err) {
      console.error('error connecting: ' + err.stack);
      return;
    }
    query()
})

This way you have a first request. Once that is done, the app waits 10 seconds and prompt a second request. After that one is done, it waits for 10 seconds and so on...