I'm new to node.js and try to display a simple table on a html site. This table should be filled with the data from a mysql select (eg: select * from testtable).
But I really don't get it how to do it. For someone with PHP background how can I display some sql content in a HTML in nodejs? I know the git mysql nodejs wiki but it didn't help at all since this is only about getting the data and not displaying it on a webpage.
What is the best practice to do something like that in nodeJS? Is there an easy understandable way, or should I stay with PHP? I really like the idea about node.js but the start is like climbing a slippery cliff.
I also buyed a book about it, but the book never gets deep into express (because this would be too much information..). So I know about express, pug, serve-static and I was able to serve a simple html. But that's it, no CRUD or REST and I payed 50€ for nothing.
EDIT: Do I need to use an API or is maybe using Angular.js the correct way for this?
EDIT2:
C:\Users\user\node_test\CRUD2>node app.js
module.js:327
throw err;
^
Error: Cannot find module './routes'
at Function.Module._resolveFilename (module.js:325:15)
at Function.Module._load (module.js:276:25)
at Module.require (module.js:353:17)
at require (internal/module.js:12:17)
at Object.<anonymous> (C:\Users\user\node_test\CRUD2\app.js:2:14)
at Module._compile (module.js:409:26)
at Object.Module._extensions..js (module.js:416:10)
at Module.load (module.js:343:32)
at Function.Module._load (module.js:300:12)
at Function.Module.runMain (module.js:441:10)
EDIT3:
C:\Users\user\node_test\CRUD2>node app.js
Express server listening on port 4300
C:\Users\user\node_test\CRUD2\routes\testtable.js:4
var query = connection.query('SELECT * FROM testtable',function(err,rows){
^
TypeError: Cannot read property 'query' of undefined
at C:\Users\user\node_test\CRUD2\routes\testtable.js:4:29
at C:\Users\user\node_test\CRUD2\node_modules\express-myconnection\lib\express-myconnection.js:87:41
at Pool.<anonymous> (C:\Users\user\node_test\CRUD2\node_modules\mysql\lib\Pool.js:47:16)
at Handshake.Sequence.end (C:\Users\user\node_test\CRUD2\node_modules\mysql\lib\protocol\sequences\Sequence.js:78:24)
at Handshake.ErrorPacket (C:\Users\user\node_test\CRUD2\node_modules\mysql\lib\protocol\sequences\Handshake.js:101:8)
at Protocol._parsePacket (C:\Users\user\node_test\CRUD2\node_modules\mysql\lib\protocol\Protocol.js:205:24)
at Parser.write (C:\Users\user\node_test\CRUD2\node_modules\mysql\lib\protocol\Parser.js:62:12)
at Protocol.write (C:\Users\user\node_test\CRUD2\node_modules\mysql\lib\protocol\Protocol.js:37:16)
at Socket.<anonymous> (C:\Users\user\node_test\CRUD2\node_modules\mysql\lib\Connection.js:73:28)
at emitOne (events.js:77:13)
here an example:
app.js
var express = require('express');
var routes = require('./routes');
var http = require('http');
var path = require('path');
//Including controller/dao for testtable
var testtable = require('./routes/testtable');
var app = express();
var connection = require('express-myconnection');
var mysql = require('mysql');
// all environments
app.set('port', process.env.PORT || 4300);
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'ejs');
app.use(express.logger('dev'));
app.use(express.json());
app.use(express.urlencoded());
app.use(express.methodOverride());
app.use(express.static(path.join(__dirname, 'public')));
// development only
if ('development' == app.get('env')) {
app.use(express.errorHandler());
}
app.use(
connection(mysql,{
host: 'localhost',
user: 'your_user',
password : 'your_password',
port : 3306, //port mysql
database:'dbname'
},'pool')
);
app.get('/testtable', testtable.list);
app.use(app.router);
http.createServer(app).listen(app.get('port'), function(){
console.log('Express server listening on port ' + app.get('port'));
});
routes/testtable.js
exports.list = function(req, res){
req.getConnection(function(err,connection){
var query = connection.query('SELECT * FROM testtable',function(err,rows){
if(err)
console.log("Error Selecting : %s ",err );
res.render('testtable',{page_title:"Test Table",data:rows});
});
});
};
views/testtable.ejs
<html><body>
<table border="1" cellpadding="7" cellspacing="7">
<tr>
<th width="50px">No</th>
<th>Field 1</th>
<th>Field 2</th>
<th>Field 3</th>
</tr>
<% if(data.length){
for(var i = 0;i < data.length;i++) { %>
<tr>
<td><%=(i+1)%></td>
<td><%=data[i].field1%></td>
<td><%=data[i].field2%></td>
<td><%=data[i].field3%></td>
</tr>
<% }
}else{ %>
<tr>
<td colspan="3">No user</td>
</tr>
<% } %>
</table>
</body></html>
To run this "project" you have to install some node modules, you could create a file called "package.json" in your root directory:
{
"name": "application-name",
"version": "0.0.1",
"private": true,
"scripts": {
"start": "node app.js"
},
"dependencies": {
"ejs": "^1.0.0",
"express": "3.5.1",
"express-myconnection": "1.0.4",
"jade": "*",
"mysql": "2.2.0"
}
}
and call "npm install" under your project root.
I hope it helps you.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With