2

I am trying to run a query in a view (.ejs file). However, since the keyword require is not defined in a .ejs file, I need to export it from my main file, server.js.

The whole code for my server.js file is below and this is the specific snippet with which I need help.

app.engine('html', require('ejs').renderFile);

exports.profile = function(req, res) {

res.render('profile', { mysql: mysql }); }

I need to be able to use the mysql.createConnection in my profile.ejs file.

Any help would be great.

// server.js

// set up ======================================================================
// get all the tools we need
var express  = require('express');
var app      = express();
var port     = process.env.PORT || 8080;
var mongoose = require('mongoose');
var passport = require('passport');
var flash    = require('connect-flash');

var morgan       = require('morgan');
var cookieParser = require('cookie-parser');
var bodyParser   = require('body-parser');
var session      = require('express-session');

var configDB = require('./config/database.js');
var Connection = require('tedious').Connection;
var config = {
userName: 'DESKTOP-S6CM9A9\\Yash',
password: '',
server: 'DESKTOP-S6CM9A9\\SQLEXPRESS',
};
var Request = require('tedious').Request;
var TYPES = require('tedious').TYPES;

  var mysql = require('mysql');
  var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "yashm"
  });

 con.connect(function(err) {
if (err) throw err;
console.log("Connected!");
var sql="Select * from test.productlist";
con.query(sql, function (err, result) {
  if (err) throw err;
  console.log(result);
});
});

app.engine('html', require('ejs').renderFile);


exports.profile = function(req, res) {
    res.render('profile', { mysql: mysql });
}



//--------------------------------------------------------------------------------




// configuration ===============================================================
mongoose.connect(configDB.url); // connect to our database

require('./config/passport')(passport); // pass passport for configuration

// set up our express application
app.use(morgan('dev')); // log every request to the console
app.use(cookieParser()); // read cookies (needed for auth)
app.use(bodyParser()); // get information from html forms

app.set('view engine', 'ejs'); // set up ejs for templating

// required for passport
app.use(session({ secret: 'test run' })); // session secret
app.use(passport.initialize());
app.use(passport.session()); // persistent login sessions
app.use(flash()); // use connect-flash for flash messages stored in session

// routes ======================================================================
require('./app/routes.js')(app, passport); // load our routes and pass in our app and fully configured passport

// launch ======================================================================
app.listen(port);
console.log('The magic happens on port ' + port);

Improve this question
5
  • You don't need mysql in your view. Your logic should be in your controller (server.js) en from there just pass some data to your view. Commented Jun 1, 2017 at 8:38
  • I tried that too but I wasn't able to render the result set to the view page. Commented Jun 1, 2017 at 8:43
  • I'll try it again though then. Also, what would I do for the reverse, when I want a button click to insert data into my database? Pass the data to my controller and then run it in controller? Commented Jun 1, 2017 at 8:45
  • Maybe you should reformat? Does the query works? And for posting back data, pass the data to the controller using a form/ajax/... and indeed run your query in the controller Commented Jun 1, 2017 at 8:47
  • Yes, the query is running in the cmd and displaying the results. Commented Jun 1, 2017 at 8:48

1 Answer 1

Reset to default
1

Like already said in the comment, you have to do your query logic in your server.js and then pass the data to your view (or maybe even pre-process it!)

exports.profile = function(req, res) {
    con.query('SELECT 1', function (error, results, fields) {
      if (error) throw error;
      // connected!
      res.render('profile', { data: results });
    });
}

In your ejs you can loop trough the data, and acces the fields as data[i]['fieldname']

<ul>
<% for(var i=0; i<data.length; i++) {%>
   <li><%= data[i]['id'] %></li>
<% } %>
</ul>
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Okay, I tried doing this earlier but I am not understanding how to access this data in my view after passing the result set. I basically want to display it as a table which means I need to access individual rows and add it in <td>. Sorry for the simple questions, I just started Node.js 2 days ago.
II modified my answer to include a simple loop. You can modify this code so it looks like table rows instead of li elements.
Thanks but it isn't working. I know how to write the loop but even a single access isn't working. For instance <td><%= data[1]['Price'] %></td> isn't working. I think the data is not being rendered only. Is there something else that I need to include in the server file for the render to work?
This is the error being shown on the profile page while running: ReferenceError: E:\LoginForm\views\profile.ejs:184 182| 183| <ul> >> 184| <% for(var i=0; i<data.length; i++) {%> 185| <li><%= data[i]['id'] %></li> 186| <% } %> 187| </ul> data is not defined at eval (eval at <anonymous> (E:\LoginForm\node_modules\ejs\lib\ejs.js:237:14))
And you are sure you pass the results (which you can console.log and are not null?) to the controller like in the answer? (res.render('profile', { data: results });) Also, make sure you only call res.render once. The query is aync, so you should only call res.render in the callback
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.