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

Question

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.

ambianBeing · Accepted Answer · 2022-12-02 23:13:17Z

4

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' ] ]

edited Dec 2, 2022 at 23:13

answered Sep 1, 2019 at 12:40

ambianBeing

3,5492 gold badges17 silver badges28 bronze badges

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

2 Comments

Swapnil Nakade Over a year ago

Thank you so much! It worked. You exactly got what I wanted to say.

ambianBeing Over a year ago

@SwapnilNakade Great. Happy to help! if the solution resolved you issue please mark it as accepted.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related