Inserting multiple rows dynamically in mysql database node js?

Question

I am trying to insert multiple rows in mysql database using node js.The idea is that I have four arrays which has 20 values each. My goal is to dynamically insert all the values in my database using node js. Now, I am a bit confused how I should go about it. This is my server file-

server.js - 
const express = require('express');
const app = express();

var db = require("./DatabaseConfig.js");

var cookieParser = require('cookie-parser');

var path = require('path');

var bodyParser = require('body-parser');
app.use( bodyParser.json() );       // to support JSON-encoded bodies
app.use(bodyParser.urlencoded({     // to support URL-encoded bodies
    extended: true
}));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));

// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'ejs');

app.get('/', function (req, res) {
    res.send('Hello World!')
});

app.listen(4000, function () {
    console.log('Example app listening on port 4000!')
});

app.get("/home",function(req,res){
    res.render("Home.ejs");
});

app.post("/home",function(req,res)
{
var data = JSON.parse(req.body.info);
console.log(data.length);
var counter = [];
var publicationtitle=[];
    var publicationdate =[];
    var publicationlink=[];
for(var i=0;i<data.length;i++)
{
    counter.push(0);
    publicationtitle.push(data[i]["title"]);
    publicationdate.push(data[i]["pubDate"]);
     publicationlink.push(data[i]["link"]);
}
var res = db.dbconfig();


    var values = [
        publicationtitle, publicationlink,publicationdate,counter
    ];

    console.log("values",values);
   db.insertrecord(values);
    values=[];


});



DatabaseConfig.js

    var mysql      = require('mysql');
    var con = mysql.createConnection({
        host     : 'localhost',
        user     : '****',
        password : '****',
        database : 'test',
        multipleStatements: true
    });


    var values = [];






    module.exports = {
        dbconfig: function(error) {
            con.connect(function(err) {
                if (err) throw err;
                console.log("Connected!");
            });

        },

        insertrecord:function(values){
            var sql = "INSERT INTO rsscontent (title,link,date,count) VALUES ?";


            con.query(sql,[values], function(err) {
                if (err) throw err;
                con.end();
            });

        }


    };

Any suggestions or advise will be highly appreciated.

Check this stackoverflow.com/questions/6889065/…

Mohamed Abbas
– Mohamed Abbas

2017-06-24 09:54:32 +00:00
Commented Jun 24, 2017 at 9:54 — Mohamed Abbas
– Mohamed Abbas, Commented Jun 24, 2017 at 9:54

lehaininh · Accepted Answer · 2017-06-24 10:24:44Z

2

You can build your query and values like this:

    let placeholder = users.map(() => "(?, ?)").join(",");
    let query =
        `INSERT INTO ${TABLE_NAME.USER}
        (id, username)
        VALUES ${placeholder}`;

    let values = [];
    users.forEach(user => {
        values.push(user.id);
        values.push(user.username);
    });

Been using this and it worked fine.

answered Jun 24, 2017 at 10:24

lehaininh

491 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rahul · Accepted Answer · 2017-06-24 10:36:34Z

0

Other than what have been answered, your INSERT statement is bound to throw error since date is a keyword and should be escaped like

var sql = "INSERT INTO rsscontent (title,link,`date`,count) VALUES ?";

answered Jun 24, 2017 at 10:36

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Collectives™ on Stack Overflow

Inserting multiple rows dynamically in mysql database node js?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related