daniel blythe daniel blythe - 1 month ago 9
MySQL Question

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

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:

Database structure

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.

example of what comes out when I console.log(serverData)

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>



Answer

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).