i'm learning how to use node.js with mysql. I've tried to find some good documentation but in vain. i'm at the point where I can get my mysql data displayed in my browser but I want to handle it through my index.html and a css file at some point.
This is my app.js:
// moduels
var express = require('express');
var app = express();
var mysql = require('mysql');
var bodyParser = require('bodyParser')
//
app.use(express.static(__dirname + '/public'));
app.use(bodyParser.urlencoded({extended: false}));
// connect to database
var con = mysql.createConnection({
host: "localhost",
user: "root",
password: "1234",
database: "BathBombs_DB"
});
// routes
app.get('/', function(request, response){
console.log('GET request received at /')
con.query("SELECT * FROM customers", function (err, result) {
if (err) throw err;
else{
response.send(result)
}
});
});
// listen for trafic and display on localhost:9000
app.listen(9000, function () {
console.log('Connected to port 9000');
});
My index.html site looks like this:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>Document</title>
</head>
<body>
<form action="/" method="POST">
<table type="text" name="firstname" ></table>
</form>
</body>
</html>
FakeRuneFakeRune
asked Nov 22, 2019 at 10:18
5 Answers 5
You have to make an ajax call to get a result from the server and bind with HTML content using javascript as below :
HTML template
<table id="tableData" class="table table-fixed">
<thead>
<tr>
</tr>
</thead>
<tbody class="tbody" >
</tbody>
Here is the script to make an ajax call
$(document).ready(() => {
$.ajax({
url: "http://localhost:9000/list",
method: 'GET',
success: function(response){
if(response.rows.length > 0){
for(let index = 0; index < response.rows.length; index++) {
var newRow = $("<tr>");
var cols = "";
var firstname = '';
var lastname = '';
var gender = '';
cols += '<td> '+ response.rows[index].firstname +'</td>';
cols += '<td> '+ response.rows[index].lastname +'</td>';
cols += '<td> '+ response.rows[index].gender+'</td>';
newRow.append(cols);
$("#tableData .tbody").append(newRow);
}
}
}
})
})
Veve
6,7685 gold badges43 silver badges62 bronze badges
2 Comments
FakeRune
hey i can't seem to get it to display anything and i don't get any errors
NotMyFaultSir
Did you include the script file with priya's answer into your html file? (<script src="yourpath.js"></script>) Also, to display your data, you don't need a form. Having the table in the form may cause difficulties.
You can do simply display like this inside <table> Tag :
<html>
<body>
<table>
<tr>
<th>firstname</th>
</tr>
<% if(result.length){
for(var i = 0;i < data.length;i++) { %>
<tr>
<td><%=result.firstname%></td>
</tr>
<% }
}else{ %>
<tr>
<td>No user</td>
</tr>
<% } %>
</table>
</body>
</html>
answered Nov 22, 2019 at 10:32
Comments
First you need "ship/send" your "index.html" file to browser. For that you need to define an API end point like below (which will send the index.html to the browser).
/* GET home page. */
app.get('/', getMainPage);
function getMainPage(req, res) {
console.debug('Route for mainViewpage: ' + __dirname );
console.log(process.env);
var mainViewFile = __dirname + '/../public/views/index.html'; // Replace this with path to your index.html file
console.log('mainView log' , mainViewFile);
fs.readFile(mainViewFile, function (err, html) {
if (err) {
throw err;
}
res.writeHeader(200, {"Content-Type": "text/html"});
res.write(html);
res.end();
});
}
Once this is done, follow the approach given by @priya tarun in prvious answer.
Note: You also need to include Jquery in your html file. Your "index.html" would look something like this. I havent tested fully, you can do that part.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>Document</title>
<!--Inlcudes JQuery -->
<script src="https://code.jquery.com/jquery-3.2.1.slim.min.js"></script>
</head>
<body>
<form action="/" method="POST">
<table type="text" name="firstname" ></table>
</form>
</body>
$(document).ready(() => {
$.ajax({
url: "http://localhost:9000/getCustomerData",
method: 'GET',
success: function(response){
if(response.rows.length > 0){
for(let index = 0; index < response.rows.length; index++) {
var newRow = $("<tr>");
var cols = "";
var firstname = '';
var lastname = '';
var gender = '';
cols += '<td> '+ response.rows[index].firstname +'</td>';
cols += '<td> '+ response.rows[index].lastname +'</td>';
cols += '<td> '+ response.rows[index].gender+'</td>';
newRow.append(cols);
$("#tableData .tbody").append(newRow);
}
}
}
})
})
</html>
Note: Rename your API end point to get customer data as "/getCustomerData" instead of just "/" . Use the API end point "/" to serve your "index.html" to the client/browser.
Add comments in case you have any confusion on this
answered Dec 2, 2019 at 18:09
1 Comment
Shiva
Note that I have slightly modified @priya tarun's answer.
An easy way is to use a query builder like Knex JS. It provides better experience and is easier to use. I am sure following code will make sense to you:
const knex = require('knex');
const http = require('http');
const knex = require('knex')({
client: 'mysql',
connection: {
host : '127.0.0.1',
user : 'your_database_user',
password : 'your_database_password',
database : 'myapp_test'
}
});
const record = await knex.select('title', 'author', 'year').from('books');
const displayBody = record.map(single => {
return `<tr>
<td>${single.title}</td>
<td>${single.author}</td>
<td>${single.year}</td>
</tr>`;
})
let handleRequest = (request, response) => {
response.writeHead(200, {
'Content-Type': 'text/html'
});
response.write('<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>Homepage</title>
</head>
<body>
<table>
<thead>
<tr>
<th>Title</th>
<th>Author</th>
<th>Year</th>
</tr>
</thead>
<tbody>');
response.write(displayBody);
response.write('</tbody>
</table>
</body>
</html>');
response.end();
};
http.createServer(handleRequest).listen(8000);
In the above code we are getting the data from books table to display or return that.
answered Dec 3, 2019 at 11:18
Comments
This you can handle using plain javascript, please find below code snippet, I have used hardcode data, but you can handle with API response as on success you can trigger GenerateTable() to render in HTML:
function GenerateTable() {
//Build an array containing Customer records.
var customers = new Array();
customers.push(["Customer Id", "Name", "Country"]);
customers.push([1, "John Hammond", "United States"]);
customers.push([2, "Mudassar Khan", "India"]);
customers.push([3, "Suzanne Mathews", "France"]);
customers.push([4, "Robert Schidner", "Russia"]);
//Create a HTML Table element.
var table = document.createElement("TABLE");
table.border = "1";
//Get the count of columns.
var columnCount = customers[0].length;
//Add the header row.
var row = table.insertRow(-1);
for (var i = 0; i < columnCount; i++) {
var headerCell = document.createElement("TH");
headerCell.innerHTML = customers[0][i];
row.appendChild(headerCell);
}
//Add the data rows.
for (var i = 1; i < customers.length; i++) {
row = table.insertRow(-1);
for (var j = 0; j < columnCount; j++) {
var cell = row.insertCell(-1);
cell.innerHTML = customers[i][j];
}
}
var dvTable = document.getElementById("dvTable");
dvTable.innerHTML = "";
dvTable.appendChild(table);
}
function handleApiResponse() {
//$.ajax({
// type: "GET",
// url: URL,
// dataType: "jsonp",
// error: function (response) {
// alert('Error: There was a problem //processing your request, please refresh the browser and //try again');
// },
// success: function (response) {
// GenerateTable(response)
// }
// });
GenerateTable()
}
<input type="button" value="Generate Table" onclick="handleApiResponse()" />
<hr />
<div id="dvTable"></div>
answered Dec 3, 2019 at 15:49
Comments
default