CODEWITHSUNDEEP
mysqlnode.js
Swapnil Nakade
Swapnil Nakade

Reputation: 860

Is there any way to dynamically set multiple VALUES in mysql 'insert' query in nodejs?

I am working on nodejs for the first time. I have a scenario where I am having json array like shown below. I will not know how many json items will be there in that array.

I have tried solution using loops but it will fire the query for multiple times. And I don't want that.

{"qualification":[{"degreeName":"B","domain":"p"},{"degreeName":"A","domain":"q"}]}

And mysql query will be like this

INSERT INTO qualification (degreeName,domain) VALUES (B,p),(A,q);

In the above query I have explicitly written values, but as I will not know how many values will be there I can't write values like that, instead i will have to put all values in a variable and then pass it to the query. So how can I retrive and convert qualification data into varible or tuples to put into mysql query so that I can fire single query and add multiple values.

Upvotes: 1

Views: 2231

Answers (1)

ambianBeing
ambianBeing

Reputation: 3529

You could do something like this: You'll have to generate values array from dynamic values and pass onto the query.

Key point: ? is a unnamed parameter (placeholder alias) to make it Parameterised Query

const mysql = require('mysql');

const con = mysql.createConnection({
  host: "localhost",
  user: "yourusername",
  password: "yourpassword",
  database: "mydb"
});

con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
  let sql = "INSERT INTO qualification (degreeName, domain) VALUES ?";
  let values = [
    ['B', 'p'],
    ['A', 'q'],
    ...
  ]; //this is dynamic value that you can create
  con.query(sql, [values], function (err, result) {
    if (err) throw err;
    console.log("Number of records inserted: " + result.affectedRows);
  });
});

To generate the tuples (values) from the dataset (assumes that data is going be in that format):

const payload = {
  qualification: [
    { degreeName: "B", domain: "p" },
    { degreeName: "A", domain: "q" }
  ]
};
const tuples = payload.qualification.map(obj => [obj.degreeName, obj.domain]);

Which OPs something like this:

[ [ 'B', 'p' ], [ 'A', 'q' ] ]

Upvotes: 4

Related Questions