title: showing chatlog from sql query node.js socket.io
body: <p>So i have a chatroom i am building <a href="http://billischill.ddns.net:8080" rel="nofollow noreferrer">here</a>and i am storing all of the messages users send in a database and in what chatroom they send it from how could i do something like var query = query the database were chatroom == socket.chatroom </p>
  
  <p>so it would only show messages that corrispond to the chatroom </p>
  
  <p>and how can i display it after the user changes and connects to a new chatroom </p>
  
  <p>here is my server js</p>
  
  <pre><code>var mysql = require("mysql");
  var app = require('express')();
  var http = require('http').Server(app);
  var io = require('socket.io')(http);
  var usernames = {};
  var rooms = ['General-Chat','Tech-Chat','Gamer-Chat',"BillIsChill-Underground-Chat","Programer-Chat","Hacker-Chat","Minecraft-Chat","Gta-Chat","Misc-Chat","Current-Events-Chat"];
  
  
  
   app.get('/', function(req, res){
     res.sendfile('index.html');
     //res.sendfile('/login/');
   });
  http.listen(8080, function(){
    console.log('listening on *:8080');
  });
  
      var con = mysql.createConnection({
      host: "localhost",
      user: "root",
      password: "password",
      database: "messages"
  });
  function updateLog(){
  
  //this is what i have tried
  var log='';
  for (var i=0; i&lt;rows.length; i++) {
    log += rows[i].username + '    ' + rows[i].message +'&lt;br&gt;';
  }
  document.getElementById("chatlog-display-div").innerHTML = log;
  
  
  }
  io.on('connection', function (socket) {
      socket.on('adduser', function(username){
      socket.username = username;
      socket.room = 'General-Chat';
      usernames[username] = username;
      socket.join('General-Chat');
      socket.broadcast.to('General-Chat').emit('updatechat', '&lt;b class="servertxt"&gt;SERVER: &lt;/b&gt;', socket.username + ' has connected to this room' + '&lt;hr&gt;');
      socket.emit('welcomeuser', '&lt;b class="servertxt"&gt;SERVER:&lt;/b&gt; Welcome ',  socket.username + '&lt;hr&gt;');
      socket.emit('updaterooms', rooms, 'room1');
  });
  socket.on('switchRoom', function(newroom){
    socket.leave(socket.room);
    socket.join(newroom);
    socket.emit('updatechat', '&lt;b class="servertxt"&gt; SERVER &lt;/b&gt;', 'you have connected to '+ newroom + '&lt;hr&gt;');
    // sent message to OLD room
    socket.broadcast.to(socket.room).emit('updatechat', '&lt;b class="servertxt"&gt; SERVER &lt;/b&gt;', socket.username+' has left this room &lt;hr&gt;');
    // update socket session room title
    socket.room = newroom;
    socket.broadcast.to(newroom).emit('updatechat', '&lt;b class="servertxt"&gt; SERVER &lt;/b&gt;', socket.username+' has joined this room &lt;hr&gt;');
    socket.emit('updaterooms', rooms, newroom);
  });
    console.log('a user connected');
    socket.on('disconnect', function(){
          delete usernames[socket.username];
          io.sockets.emit('updateusers', usernames);
          socket.leave(socket.room);
      });
    socket.on('chat-message', function (usernames, message) {
   console.log('message:', usernames + ': ' + message.text);
   var insert = { roomName: socket.room, username: usernames, message: message.text };
           con.query('INSERT INTO messages SET ?', insert, function(err,res){
            if(err) throw err;
          console.log('Last insert ID:', res.insertId);
        });
       socket.broadcast.to(socket.room).emit("chat-message", usernames, message);
     });
  });
  </code></pre>
  
  <p>and here is my client code </p>
  
  <pre><code>  &lt;html&gt;
    &lt;head&gt;
    &lt;meta name="viewport" content="width=device-width"/&gt;
    &lt;meta name="description" content="BillIsChill-2.0 BETA"/&gt;
    &lt;meta name="keywords" content="billischill,Bill,is,chill,2.0,billischill-2.0,gamer-chat,BillIsChill-Underground-Chat,BILLISCHILL"/&gt;
    &lt;meta name="author" content="Nik Hendricks"&gt;
    &lt;script src="http://code.jquery.com/jquery-1.11.1.js"&gt;&lt;/script&gt;
    &lt;script src="/socket.io/socket.io.js"&gt;&lt;/script&gt;
    &lt;title&gt;BillIsChill-2.0&lt;/title&gt;
    &lt;link type="text/css"  rel="stylesheet" href="http://billischill.ddns.net/billischill-2.0/css/masterStyle.css" /&gt;
   &lt;link type="text/css"  rel="stylesheet" href="http://billischill.ddns.net/billischill-2.0/css/animate.css" /&gt;
    &lt;/head&gt;
    &lt;body&gt;
    &lt;div id="main-header-div"&gt;
    &lt;p id="main-header-div-text"&gt;BillIsChill-2.0&lt;p&gt;
      &lt;form&gt;
        &lt;input id="main-header-div-search-txtinpt" class="search" placeholder="Search" name="search"/&gt;
      &lt;/form&gt;
    &lt;/div&gt;
    &lt;div id="sidebar-controls-div"&gt;
    &lt;div id="sidebar-homebutton"&gt;
      &lt;div class="sidebar-control-img" id="sidebar-homebutton-img"&gt;   &lt;/div&gt;
    &lt;/div&gt;
    &lt;div id="Chatrooms-sidebar-button" class="sidebar-control-button"&gt;
    &lt;div class="sidebar-control-img" id="sidebar-chatroombutton-img"&gt;&lt;/div&gt;
    &lt;/div&gt;
    &lt;div id="avatar-sidebar-button" class="sidebar-control-button"&gt;
      &lt;div class="sidebar-control-img" id="sidebar-avatarbutton-img"&gt;&lt;/div&gt;
    &lt;/div&gt;
    &lt;/div&gt;
    &lt;div id="chat-rooms-div" class="main-div"&gt;
    &lt;div id="rooms"&gt;&lt;/div&gt;
     &lt;/div&gt;
     &lt;div id="chat-box-div" class="main-div"&gt;
    &lt;div id="chatlog-display-div"&gt;&lt;/div&gt;
    &lt;form id="chatform" action=""&gt;
    &lt;hr id="chat-box-div-hr"&gt;
    &lt;div id="chat-controls-div"&gt;
      &lt;input id="chat-box-div-txtinpt" class="big-txtinpt"type="text" spellcheck="false" placeholder="Message"&gt;
      &lt;input id="chat-box-div-submit" class="submit" type="submit" value="Send"&gt;
    &lt;/div&gt;
      &lt;/form&gt;
    &lt;/div&gt;
    &lt;div id="online-users-div"&gt;&lt;div&gt;
  
    &lt;script&gt;
      var socket = io();
  
      socket.on('connect', function() {
  
  
  //here in the future we are going to check to see if the user is logged in or     not and if he isnt redirect him to the login page
  
          var user = prompt("What's your name?");
          socket.username = user;
          socket.emit('adduser', user);
      });
  
      socket.on('updatechat', function (username, data) {
          $('#chatlog-display-div').append(username + data);
      });
  
      socket.on('welcomeuser', function(data, username){
          jQuery("#chatlog-display-div").append(data + username);
      });
  
      socket.on('updaterooms', function(rooms, current_room) {
          $('#rooms').empty();
          $.each(rooms, function(key, value) {
              if(value == current_room){
                  $('#rooms').append('&lt;div id="chatroom-box"&gt;&lt;center&gt;' + value + '&lt;center&gt;&lt;/div&gt;&lt;hr&gt;');
                  $("#chatlog-display-div").scrollTop($("#chatlog-display-div").prop("scrollHeight"));
              }
              else {
                  $('#rooms').append('&lt;div id="chatroom-box"&gt;&lt;center&gt;&lt;a id="chatroom-box-link" href="#" onclick="switchRoom(\''+value+'\')"&gt;' + value + '&lt;/a&gt;&lt;/center&gt;&lt;/div&gt;&lt;hr&gt;');
                  $("#chatlog-display-div").scrollTop($("#chatlog-display-div").prop("scrollHeight"));
              }
           });
      });
  
      function clearBox(elementID){
          document.getElementById(elementID).innerHTML = "";
      }
      function switchRoom(room){
          socket.emit('switchRoom', room);
      }
  
      $('form').submit(function(e) {
        e.preventDefault();
        //right here is were i started changin it
        //gets the value from the message text feild and sets it as the    message var
        var message = {
          text : $('#chat-box-div-txtinpt').val()
        }
        if (message.text.trim().length !== 0) {
          socket.emit('chat-message', socket.username, message);
          //append the message to the chatlog-display-div
          $('#chat-box-div-txtinpt').focus().val('');
          jQuery("#chatlog-display-div").append('&lt;div&gt;&lt;b&gt;'+socket.username+':&lt;/b&gt;&amp;nbsp'+message.text+'&lt;/div&gt;&lt;hr&gt;');
          $("#chatlog-display-div").scrollTop($("#chatlog-display-div").prop("scrollHeight"));
        }
      });
  
       socket.on('chat-message', function (usernames, message) {
       jQuery("#chatlog-display-div").append('&lt;div&gt;&lt;b&gt;'+ usernames + ':&lt;/b&gt;&amp;nbsp'+message.text+'&lt;/div&gt;&lt;hr&gt;');
        $("#chatlog-display-div").scrollTop($("#chatlog-display-div").prop("scrollHeight"));
      });
  
    &lt;/script&gt;
  &lt;/body&gt;
  &lt;/html&gt;
  </code></pre>