CODEWITHSUNDEEP
mysqlsqlnode.jsreactjs
Nirav Panchal
Nirav Panchal

Reputation: 59

Writing multiple sql queries in nodejs

I want to display all the values from two tables from my database and display it as console.log. If I write a single query in var sql and display it as console.log(results) it works but not for multiple queries.

 var express = require('express');
    var app = express();

    let mysql = require('mysql')

    let connection = mysql.createConnection({
        host: 'localhost',
        user: 'root',
        password: '',
        database: 'pitch_perfect_db2',
        multipleStatements: true
    })


    app.get('/',(req, res) => {
        connection.connect();
        var sql = 'SELECT * FROM investors?; SELECT * FROM member_info?;'
        connection.query(sql, function(err, results, fields){
            if (!err) {
                // res.send(JSON.stringify(results[0]));
                // res.send(JSON.stringify(results[1]));
                console.log('hey');
                //console.log(results);
                console.log(results[0]);
                console.log(results[1]);

            }   else{
                console.log('Error while performing query.');
            }
        });
        connection.end();
    })

    //app.listen(port, () => console.log('Server Started pn port ${port}'));
    app.listen(3002);

Upvotes: 1

Views: 9464

Answers (2)

Jim Castro
Jim Castro

Reputation: 894

I was able to get it to work but I had to do 2 things:

First I renamed the tables to remove the question mark as it was always getting translated to a '1' and the table name no longer matched what was in the DB.

Second, I added an array to the connection.query(). After that it worked just fine.

More info here

var express = require('express');
var app = express();

let mysql = require('mysql')

let connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'pitch_perfect_db2',
    multipleStatements: true
})


app.get('/',(req, res) => {
    connection.connect();
    var sql = 'SELECT * FROM investors; SELECT * FROM member_info;';
    //var sql = 'SELECT * FROM investors;';
    connection.query(sql, [1, 2], function(err, results, fields){
        if (!err) {

            res.send(JSON.stringify(results[0]) + JSON.stringify(results[1]));

            console.log('hey');
            //console.log(results);
            console.log(results[0]);
            console.log(results[1]);

        }   else{
            console.log('Error while performing query.');
            console.log(err);
        }
    });
    connection.end();
})

//app.listen(port, () => console.log('Server Started pn port ${port}'));
app.listen(3002);

Upvotes: 4

Lord Elrond
Lord Elrond

Reputation: 16002

In node you don't use ; in your sql statements. Assuming both the investors and member_info tables have the same number of columns, you will need to use this:

var sql = 'SELECT * FROM investors UNION ALL SELECT * FROM member_info';

Alternatively, if investors and member_info are unrelated tables, you will need to journey into callback hell to get what you need:

app.get('/',(req, res) => {
    connection.connect();
    var sql1 = 'SELECT * FROM investors';
    var sql2 = 'SELECT * FROM member_info?';
    connection.query(sql1, function(err, investors){
        if (err) throw err; //you should use this for error handling when in a development environment
        console.log(investors); //this should print

        connection.query(sql2, function(err, members) {
            if (err) throw err;
            console.log(members);

            res.render('your view', {investors:investors, members:members});
        });  
    });
});

If you decide on the latter approach, I would urge you to reconsider your database layout.

If either of the tables in your examples have a foreign key relation with each other, you should definitely be using some kind of JOIN statement on these tables, instead of a UNION.

Upvotes: 0

Related Questions