CODEWITHSUNDEEP
mysqlsqlarraysnode.jsfor-loop
devguy
devguy

Reputation: 17

How to insert into a SQL table using a for-loop in NodeJS?

I am having trouble finding the proper SQL syntax on adding items to my table using a for loops and an array. This is what I am trying to do: 

var mysql = require('mysql');

var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    database: 'DUSAgeocodeDB'
});

var addreses = ['addres1', 'address2', 'address3'];
var latitude = [12, 45, 789];
var longitude = [987, 654, 321];

for (var i =0; i <addreses.length; i++) {

    con.connect(function(err) {
        con.query(`INSERT INTO maintable (mainaddress, latitude, longitude) VALUES (${addreses[i]}, ${latitude[i]}, ${longitude[i]} )`, function (err, result) {
            if(err) throw err;

            console.log('VALUES ADDED')
        });

    })
}

What would be the proper syntax on the con.query() statement in order to save those values to mysql table? This is something else that I've tried but it does not work 

for (var i =0; i <addreses.length; i++) {

    con.connect(function(err) {
        con.query("INSERT INTO maintable (mainaddress, latitude, longitude) VALUES ("+${mysql.escape(addreses[i])}+", "+${mysql.escape(latitude[i])}+", "+${mysql.escape(longitude[i])}" )", function (err, result) {
            if(err) throw err;

            console.log('VALUES ADDED')
        });

    })
}

Upvotes: 0

Views: 3406

Answers (2)

Dan Starns
Dan Starns

Reputation: 3825

I would suggest leveraging Promise.all this way you can fire multiple async operations off in parallel. mysql does not come with promise support, however, you could use msql2 that does. Here I have wrapped the existing con.query in its own promise.

function queryPromise(query) {
    return new Promise((resolve, reject) => {
        con.query(query, (err, result) => {
            if (err) {
                return reject(err);
            }

            return resolve(result);
        });
    });
}

con.connect(async (err) => {
    await Promise.all(
        addreses.map((_, i) => {
            return queryPromise(
                `INSERT INTO maintable (mainaddress, latitude, longitude) VALUES (${addreses[i]}, ${latitude[i]}, ${longitude[i]} )`
            );
        })
    );
});

Upvotes: 4

Dhananjai Pai
Dhananjai Pai

Reputation: 6015

you could write

var mysql = require('mysql');

var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    database: 'DUSAgeocodeDB'
});

var addreses = ['addres1', 'address2', 'address3'];
var latitude = [12, 45, 789];
var longitude = [987, 654, 321];

con.connect(function(err) { // create a single connection to execute all the queries
   for (let i=0; i < addreses.length; i++) { //use let instead of var to prevent issues with closures in asynchronous code
      con.query(`INSERT INTO maintable (mainaddress, latitude, longitude) VALUES (${addreses[i]}, ${latitude[i]}, ${longitude[i]} )`, function (err, result) {
            if(err) throw err;
            console.log('VALUES ADDED')
        });
    })
}

Upvotes: 0

Related Questions