将mysql数据库打印到套接字中的html页面。io和node . js

I am trying to be able to print mysql data to a html page just to test it so i can start learning

我正在尝试将mysql数据打印到html页面，只是为了测试它，这样我就可以开始学习了

Upadated Code

Upadated代码

here is what i have tried on my server.js

这是我在服务器上尝试过的。

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

app.get('/', function(req, res){
  res.sendfile('testsql.html');
  //res.sendfile('/login/');
});
http.listen(3000, function(){
  console.log('listening on *:3000');
});
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "password",
    database: "users"
});


io.on('connection', function (socket) {

    console.log('a client connected');

    con.query('SELECT * FROM users',function(err,rows){
      if(err) throw err;
      console.log('Data received from Db:\n');
      console.log(rows);
      socket.emit('showrows', rows);
    });



});

**And Here is My Client Code **

这是我的客户代码

<!doctype html>
<html>
  <head>
    <title>Testing socket.io</title>
  </head>
  <body>
    <h1 id="socketio"> not connected </h1>
    <div id="display"> </div>
    <script src="/socket.io/socket.io.js"></script>
    <script>
      var socket = io();
      socket.on('connect', function() {
        document.getElementById("socketio").innerHTML = "socket connected";
      });
      socket.on('showrows', function(dbData) {
        document.getElementById("display").innerHTML = dbData;
      });
    </script>
  </body>
</html>

the client is showing that it is connecting to the server but it is not displaying the query result on the client

客户端显示它正在连接服务器，但它没有在客户端显示查询结果

3 个解决方案

#1

You can try the following changes:

你可以尝试以下改变:

in the server:

在服务器:

var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "password",
    database: "users"
});


io.on('connection', function (socket) {

    console.log('a client connected');

    con.query('SELECT * FROM users',function(err,rows){
      if(err) throw err;
      console.log('Data received from Db:\n');
      console.log(rows);
      socket.emit('showrows', rows);
    });



 });

in the client:

在客户端:

<!doctype html>
<html>
  <head>
    <title>Testing socket.io</title>
  </head>
  <body>
    <h1 id="socketio"> not connected </h1>
    <div id="display"> </div>
    <script src="/socket.io/socket.io.js"></script>
    <script>
      var socket = io();
      socket.on('connect', function() {
        document.getElementById("socketio").innerHTML = "socket connected";
      });
      socket.on('showrows', function(rows) {
        var html='';
        for (var i=0; i<rows.length; i++) {
          html += rows[i].firstname + ' ' + rows[i].lastname + '<br>';
        }  
        document.getElementById("display").innerHTML = html;
        console.log(rows);
      });
    </script>
  </body>
</html>

#2

I see the following typo: socket.brodcast.emit('showrows', rows); which should be socket.broadcast.emit('showrows', rows);

我看到了下面的排版:socketter .brodcast。发出(showrows,行);这应该socket.broadcast。发出(showrows,行);

But in your case, you only need socket.emit('showrows', rows). And in order to listen to this on the client side, you need to have this:

但在你的情况下，你只需要插座。发出(showrows,行)。为了在客户端听这个，你需要

socket.on('showrows', function(rows){
    console.log(rows);
    jQuery("#display").append(rows);
});

#3

First, you are trying to create a connection to mysql server twice.

首先，尝试创建到mysql服务器的连接两次。

And I believe there is something that you missed out for your client side.You forgot to connect to your socket.io server.

我相信你在客户方面漏掉了一些东西。你忘了连插座了。io服务器。

var socket = io.connect('http://localhost:3000', {'reconnect': true, 'force new connection': true, 'connect timeout': 1000});

socket.on('showrows', function(rows) {
    jQuery("#display").append(rows);
});

You should also make sure your client is connected to the server before emitting your query result.

在发出查询结果之前，还应该确保客户端已经连接到服务器。

io.sockets.on('connection', function (socket) {

   //Run your mysql query method

  //Socket disconnected
    socket.once('disconnect', function () {

    });
});

Update: Only end the mysql connection when you are done querying.

更新:只在完成查询时才结束mysql连接。

con.connect(function(err){
  if(err){
    console.log('Error connecting to Db');
    return;
  }
  console.log('Connection established');
});

//Don't recreate a mysql connection


con.query('SELECT * FROM users',function(err,rows){
  if(err) throw err;

  console.log('Data received from Db:\n');
  console.log(rows);
});


con.end(function(err) {
  // The connection is terminated gracefully
  // Ensures all previously enqueued queries are still
  // before sending a COM_QUIT packet to the MySQL server.
});

#1