How to display nodejs mysql results on html page?

Question

I am learning Nodejs as an alternative to PHP which I am familiar with. Typically I would use Ajax to call PHP scripts to return json data and work from there. It is pretty simple to show it on a website.

Now I am trying to us Nodejs to load data from a MySQL database on page load and show it on the html page. The following is my app.js script.

I have two ideas listed below #1 using socket.io and #2 just loading it, they are not running concurrently, just different attempts.

const express = require('express');
const mysql = require('mysql');

const app = express();
const http = require('http').Server(app);
const io = require('socket.io')(http);

const port = process.env.PORT || 3000;

app.use(express.static(__dirname + '/public'));

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

//mysql

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

io.on('connection', (socket) => {
    socket.on('chat message', (msg) => {
        io.emit('chat message', msg);
    });
    socket.on('typing', (data) => {
        socket.broadcast.emit('typing', data);
    });
    socket.on('typing_clear', (data) => {
        socket.broadcast.emit('typing_clear', data);
    });

    // #1 - This is one attempt but crashes the app if loaded from a second page... It works but then if I open a second browser and load the url it crashes the app. I just want this to load once each time a user goes to the URL to load historical data from the database.

    socket.on('get_posts', (data) => {
        connection.connect();

        connection.query('SELECT `status` FROM `training`', function (error, results) {
            if (error) {
                console.log(error);
            }
            var string = JSON.stringify(results);
            // console.log(string);
            io.emit('get_posts', string);

        });

        connection.end();

    });

});


// #2 - This is another attempt. It grabs the data and successfully console log the data. I don't know what do from there. How can I get it to the client side JS page to use json parse and display it.

connection.connect();

connection.query('SELECT `status` FROM `training`', function (error, results) {
    if (error) {
        console.log(error);
    }
    var string = JSON.stringify(results);
    console.log(string);

});

connection.end();


http.listen(port, () => {
    console.log(`Socket.IO server running at http://localhost:${port}/`);
});

I have a second JS script on the client side that does front end stuff with socket.io and others.

Am I going about this wrong?

---

Edit: Here is the relevant part of the client side JS script.

load();
function load() {
    socket.emit('get_posts', "none");
}

socket.on('get_posts', function (data) {

    var results = JSON.parse(data);

    jQuery.each(results, function (index, item) {

        var status = item.status;
        var sticky = '<div class="sticky">' + status + '</div>';
        $("#messages").append(sticky);
    });

    var x = '<div class="sticky">END</div>';
    $("#messages").append(x);
});

Error for #2:

I am doing this on local host to test. I do nodemon app.js and go to localhost:3000.

Looks great, loads the data and displays as expected. Then I open a second browser window to see how another person may see it. It loads the html but does not load the data and in the server console (VS Code) I get:

Socket.IO server running at http://localhost:3000/
events.js:292
      throw er; // Unhandled 'error' event
      ^

Error: Cannot enqueue Handshake after invoking quit.
    at Protocol._validateEnqueue (/Users/username_hidden/node/node_modules/mysql/lib/protocol/Protocol.js:215:16)
    at Protocol._enqueue (/Users/username_hidden/node/node_modules/mysql/lib/protocol/Protocol.js:138:13)
    at Protocol.handshake (/Users/username_hidden/node/node_modules/mysql/lib/protocol/Protocol.js:51:23)
    at Connection.connect (/Users/username_hidden/node/node_modules/mysql/lib/Connection.js:116:18)
    at Socket.<anonymous> (/Users/username_hidden/node/app.js:36:20)
    at Socket.emit (events.js:315:20)
    at Socket.emitUntyped (/Users/username_hidden/node/node_modules/socket.io/dist/typed-events.js:69:22)
    at /Users/username_hidden/node/node_modules/socket.io/dist/socket.js:428:39
    at processTicksAndRejections (internal/process/task_queues.js:75:11)
Emitted 'error' event on Connection instance at:
    at Connection._handleProtocolError (/Users/username_hidden/node/node_modules/mysql/lib/Connection.js:423:8)
    at Protocol.emit (events.js:315:20)
    at Protocol._delegateError (/Users/username_hidden/node/node_modules/mysql/lib/protocol/Protocol.js:398:10)
    at Handshake.<anonymous> (/Users/username_hidden/node/node_modules/mysql/lib/protocol/Protocol.js:232:10)
    at Handshake.emit (events.js:315:20)
    at Handshake.Sequence.end (/Users/username_hidden/node/node_modules/mysql/lib/protocol/sequences/Sequence.js:78:12)
    at /Users/username_hidden/node/node_modules/mysql/lib/protocol/Protocol.js:236:14
    at processTicksAndRejections (internal/process/task_queues.js:75:11) {
  code: 'PROTOCOL_ENQUEUE_AFTER_QUIT',
  fatal: false
}
[nodemon] app crashed - waiting for file changes before starting...

---

Found another post noting you do not need connection.connect(); and connection.end() if already connected. Seemed to have fixes it so far... Testing in progress.

Answer 1

Replce your second code with a REST api like below

const connection = mysql.createConnection({
  host: dbConfig.HOST,
  user: dbConfig.USER,
  password: dbConfig.PASSWORD,
  database: dbConfig.DB
});

// open the MySQL connection
connection.connect(error => {
  if (error) throw error;
  console.log("Successfully connected to the database.");
});
app.get('/get-status', (req, res) => {
     connection.query('SELECT `status` FROM `training`', function (error, results) {
         if (error) {
             console.log(error);
         }
         res.send(results);
     });
  });

Now from your front end you could just use ajax to get the REST data using jquery

jQuery.get( "/get-status", function( data ) {
    jQuery.each(data , function (index, item) {
        var status = item.status;
        var sticky = '<div class="sticky">' + status + '</div>';
        $("#messages").append(sticky);
    });
    var x = '<div class="sticky">END</div>';
    $("#messages").append(x);
});

Now what i have suggested abave is a REST api solution that is for your second case. If you like to use sockets on the first go it will be little bit tough to understand try understanding how websockets work then it will be easy.