I am trying to create an HTML page that will display data as a table from MySql table. I am able to get the data from Mysql into JSON but not able to get it to display on the HTML page. It can either read from the database directly or a JSON page, whichever is better practice. I am stuck getting it to display as a table, once it is displayed I would like to be able to add more data such as age and have a sort/filter function. The code I have is very basic and from a tutorial I saw so thank you for any assistance!
table_routes.js
module.exports = function(app, passport) {
app.get('/', function(req, res){
res.render('index.ejs');
});
//-SQL QUERY
var express = require('express')
, http = require('http')
, mysql = require('mysql'); // <---- HERE
var app = express();
app.use(function(req, res, next) {
res.header("Access-Control-Allow-Origin", "http://localhost:8080");
res.header("Access-Control-Allow-Headers", "Origin, X-Requested-With, Content-Type, Accept");
next();
});
var connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: "password",
database: 'db'
});
connection.connect(); // <---- AND HERE
// all environments
app.set('port', process.env.PORT || 7003);
connection.query('SELECT * FROM testtable', function(err, rows, fields){
if(err) {
console.log(err);
res.json({"error":true});
}
else {
// console.log(result);
console.log(JSON.stringify(rows));
// response.writeHead(200, {'Content-Type': 'application/json'});
res.json(rows);
// response.end(JSON.stringify(rows));
}
});
http.createServer(app).listen(app.get('port'), function(){
console.log('Express server listening on port ' + app.get('port'));
});
};
admintable.ejs
<!DOCTYPE html>
<head>
<title>Home | Table</title>
<link type="text/css" rel="stylesheet" href="css/styles.css" />
</head>
<body>
<h1>Dashboard</h1>
<table id="servers">
<thead>
<tr>
<th width="150">ID</th>
<th width="150">Name</th>
<th width="150">Last Name</th>
<th width="150">Row</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
$.get('/getTable', function(result,fields) {
var rows = JSON.parse(result);
var row = rows[i];
console.log(rows);
for (i=0 ; i < rows.length ; i++){
$('#servers tbody').append('<tr><td>'+row.id+'</td><td>'+row.name+'</td><td>'+row.lastname+'</td><td>'+row.testtablecol+'</td></tr>');
}
});
});
</script>
<script type="text/javascript" src="js/script.js" />
</body>
