CODEWITHSUNDEEP
mysqlnode.js
Mohamed Gabr
Mohamed Gabr

Reputation: 716

How to make multy queries on mysql with node.js

I'm trying to run 2 queries against a MySQL database and they are not working. And display it, getting errors, can I do this a better way? I'm mainly asking how to display it in the home.handlebars file and how to make more than 1 query from MySQL database.

var express = require('express');
var handlebars = require('express-handlebars');
var bodyParser = require('body-parser')
var mysql = require('mysql');

var app = express();

app.engine('handlebars', handlebars({defaultLayout: 'main'}));
app.set('views', __dirname + '/views');
app.set('view engine', 'handlebars');
app.use(express.static('public'));
app.use(bodyParser.json()); // support json encoded bodies
app.use(bodyParser.urlencoded({ extended: true })); // support encoded bodies

var connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: 'translation_project'
});

connection.connect();

app.get('/', function(req, res) {

    var translatorid = 45;

    var sqlQuery = "SELECT title,type FROM itemtable;"
    connection.query(sqlQuery,function(err,rows,fields){
        if(err){
            console.log('Error'+err);
        }else{
            res.render('home',{items:rows});    // to be displayed in the home page
        }
    });
    var sqlQuery2 = "SELECT dateoftranslation FROM translateditems;"
    connection.query(sqlQuery2,function(err,rows,fields){
        if(err){
            console.log('Error'+err);
        }else{
            res.render('home',{dates:rows});    // to be displayed in the home page
        }
    });

});

app.listen(3000, function() {
    console.log('Server is running at port 3000');
});

and Here is the home.handlebars file

<h1> My Applied Items </h1>
{{#each items}} <!-- the items refreneced in the index page-->

<p>{{title}}</p>
<p>{{type}}</p>


{{/each}}
{{#each dates}} <!-- the dates refreneced in the index page-->

<p>{{dateoftranslation}}</p>


{{/each}}

and Here is the home.handlebars file

<!doctype html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <title>Users Profile</title>
</head>

<body>
    <h1> Mohamed </h1>
    {{{body}}}
</body>
</html>

Upvotes: 3

Views: 163

Answers (2)

vkstack
vkstack

Reputation: 1644

You should do it in callback nesting due to asynchronous nature of connection.query().

app.get('/', function(req, res) {

  var translatorid = 45;
  var sqlQuery = "SELECT title,type FROM itemtable;",
    sqlQuery2 = "SELECT dateoftranslation FROM translateditems;";
  connection.query(sqlQuery,function(err,rows1){
    if(err){
      console.log('Error'+err);
    }
    else{
      connection.query(sqlQuery2,function(err,rows2){
        if(err){
          console.log('Error'+err);
        }
        else{
          res.render('home',{items:rows1,dates:rows2});
        }
      });
    }
  });
});

If you are performing several queries and you don't want Callback nesting(Due to non-readability of multi-nesting callbacks) then you should try promise or you can do that async way. below is async way.

var async=require('async');
connection.connect();

app.get('/', function(req, res) {

  var translatorid = 45;
  var sqlQuery = "SELECT title,type FROM itemtable;",
    sqlQuery2 = "SELECT dateoftranslation FROM translateditems;",
    sqlQuery3="someQuery",
    sqlQuery4="someOtherQuery",
    sqlQuery5="someOtherOtherQuery";

  async.parallel([function(cb){
    connection.query(sqlQuery,function(err,rows1){
      if(err){
        console.log('Error'+err);
        cb(err);
      }
      else{
        cb(null,rows1);
      }
    });
  },function(cb){
    connection.query(sqlQuery2,function(err,rows2){
      if(err){
        console.log('Error'+err);
        cb(err);
      }
      else{
        cb(null,rows2);
      }
    });
  },function(cb){
    connection.query(sqlQuery3,function(err,rows3){
      if(err){
        console.log('Error'+err);
        cb(err);
      }
      else{
        cb(null,rows3);
      }
    });
  },function(cb){
    connection.query(sqlQuery4,function(err,rows4){
      if(err){
        console.log('Error'+err);
        cb(err);
      }
      else{
        cb(null,rows4);
      }
    });
  },function(cb){
    connection.query(sqlQuery5,function(err,rows5){
      if(err){
        console.log('Error'+err);
        cb(err);
      }
      else{
        cb(null,rows5);
      }
    });
  }],function(err,results){
    if(err){
    //  out of those 5 tasks at least one caused some error.
    //  handle that.
      return;
    }
  //  no error occurred.
    var rows1=results[0],
      rows2=results[1],
      rows3=results[2],
      rows4=results[3],
      rows5=results[4];
    res.render('home',{items:rows1,dates:rows2,other1:rows3,other2:rows4,other3:rows5});
  });
});

Upvotes: 1

Kable
Kable

Reputation: 1035

I would consider using Promises so you can wait for both queries to return before sending the result to the client:

function query(sqlQuery) {
    return new Promise(function (resolve, reject) {
        connection.query(sqlQuery, function (err, result) {
            if (err) {
                reject(err);
            } else {
                resolve(result);
            }
        });
    });
}

app.get('/', function (req, res) {
    var query1 = query("SELECT title,type FROM itemtable;");
    var query2 = query("SELECT dateoftranslation FROM translateditems;");

    Promise.all(query1, query2).then(function (result) {
        res.render('home', {
             items: result[0],
             dates: result[1]
        });
    });
});

Upvotes: 1

Related Questions