JS & NodeJs: Read table from DB with AJAX and display in table

Question

I’m working on project with NodeJS and I’m trying to read from my MySQL database with AJAX then display the DB table rows in an HTML table.

I think I’m having a problem with how I handle/store the data once I get it out of the DB.

Here is the structure of my DB(completely fictitious data of course), and my code so far:

Also here is what I get if I console.log(serverData); it seems to me that my data should be in some kind of nested array or some kind of structure that allows me to identify what value in the array I’m accessing.

server.js

var http = require('http');
var fs = require('fs');
var mysql = require('mysql');
var url = require('url');
var mime = require('mime');
var config = JSON.parse(fs.readFileSync('config.json'));
var host = config.host;
var port = config.port;

var connection = mysql.createConnection({
  host      : 'localhost',
  user      : 'root',
  password  : 'root',
  database  : 'innovation_one'
});
function connectToDb(){
  connection.connect(function(err){
    if (err){
      console.log('error: ' + err.stack);
      return;
    }
    return console.log('Connected established!');
  });
}

var server = http.createServer(function(request,response){
  var parsed = url.parse(request.url);
  var mimetypeLookup = mime.lookup(request.url);
  if(request.method == "POST") {
// POST
  } else if (request.method == "GET") {
// GET
if (request.url == '/get-servers/'){
  connectToDb();
  connection.query('SELECT * FROM servers', function(err,rows,fields)    {
    if (err) {
      console.log(err);
      return
    }
    var data = [];
    for( i = 0 ; i < rows.length ; i++){

      data.push(rows[i].name);
      data.push(rows[i].client);
      data.push(rows[i].type);
      data.push(rows[i].host);
      data.push(rows[i].ssh);
      data.push(rows[i].mysql);

    }
    response.writeHead(200, {'Content-Type': 'text/html'});
    response.end(JSON.stringify(data,fields));
      });
    }
  }
}).listen(port, host);

console.log('Server running at http://127.0.0.1:4114/');

index.html

<!DOCTYPE html>
<head>
<title>Home | Innovation One</title>
<link type="text/css" rel="stylesheet" href="css/styles.css" />
</head>
<body>
  <h1>Dev sheet dashboard</h1>
  <button id="getServers">Get servers</button>
  <table id="servers">
    <thead>
      <tr>
        <th width="150">Server name</th>
        <th width="150">Client</th>
        <th width="150">Type</th>
        <th width="150">Host</th>
        <th width="150">SSH</th>
        <th width="150">MySQL</th>
      </tr>
    </thead>
    <tbody>
    </tbody>
  </table>
  <div class="log"></div>
  <script src="https://code.jquery.com/jquery-2.2.0.min.js"></script>
  <script type="text/javascript">
  $(document).ready(function() {
      // Code here
      $('button#getServers').on('click',function(){

        var jqxhr = $.get('/get-servers/', function(data,fields) {
        var serverData = JSON.parse(data);
        console.log(serverData);
          var rows = serverData.length / (fields.length - 1);

          for (i=0 ; i < rows ; i++){
            $('#servers tbody').append('<tr>            <td>'+serverData[i]+'</td><td>'+serverData[i]+'</td>    <td>'+serverData[i]+'</td><td>'+serverData[i]+'</td><td>'+serverData[i]+'</td><td>'+serverData[i]+'</td></tr>');
          }
        });


      });
  });
  </script>
  <script type="text/javascript" src="js/script.js" />
</body>

Nivesh · Accepted Answer · 2016-10-26T13:37:02.900

Your rows data is actually and array of objects:

[
    {
        name:'super_company',
        client: 'Super Co Ltd',
        type:'staging',
        host:'156.34.567.34',
        ssh:'gerbguiberug',
        mysql:'49thgrekver'
    },
    {

    },...
]

Simply loop over the array to get a single object and then extract it's properties in simple terms, or use for in loop or for each loop.

Example:

for (i=0 ; i < rows.length ; i++){
    var row = rows[i];
    console.log(row.name);
    console.log(row.client);
    console.log(row.type);
    console.log(row.host);
    console.log(row.ssh);
    console.log(row.mysql);
}

So, u need not to push it into an array for accessing it. Better pass is directly and access as below:

server.js:

connection.query('SELECT * FROM servers', function(err,rows,fields)    {
    if (err) {
      console.log(err);
      return
    }
    response.writeHead(200, {'Content-Type': 'text/html'});
    response.end(JSON.stringify(rows));
});

index.html:

$.get('/get-servers/', function(data,fields) {
    var rows = JSON.parse(data);
    for (i=0 ; i < rows.length ; i++){
        var row = rows[i];
        $('#servers tbody').append('<tr><td>'+row.name+'</td><td>'+row.client+'</td><td>'+row.type+'</td><td>'+row.host+'</td><td>'+row.ssh+'</td><td>'+row.mysql+'</td></tr>');
    }
});

Addition it would be better if you set content type to JSON:

response.writeHead(200, {'Content-Type': 'application/json'});

That way you don't have to parse it in index.html (client side).

Hey Nivesh, thank you for your reply. I'll test your reply in the next couple of hours and let you know how it goes. But your reply looks promising : ). — daniel blythe, Oct 26 '16 at 10:38
Hey Nivesh, thats all working great, thank you! Only thing is when I set the "`Content-Type`" to "`application/json`" I get this response in the console "`Unexpected token o in JSON at position 1`". Would you happen to know how to fix that please? Thanks again for your answer. I know just need to make sure I understand what you've done. Actually can I ask why you had this line "`var row = rows[i];`" in the "`index.html file`"? Thanks. — daniel blythe, Oct 26 '16 at 13:21
you have a list of objects inside an array, to access one particular object you need to do `var obj = array_obj[0]` it will access 0th index object, now to further access its properties you can do `obj.prop_name` — Nivesh, Oct 26 '16 at 13:32
your data is already parsed so no need to parse in that case refer: http://stackoverflow.com/a/33489715/2680461 — Nivesh, Oct 26 '16 at 13:36

JS & NodeJs: Read table from DB with AJAX and display in table

1 Answers1