How to save multiple record to mysql by express server?

Question

I am building an express server to receive request (a dict with 10 items) from my react front end and then save the data to database. Below is my code. I found that conn.query will only call the query of the last item which mean the same query is called 10 times. Only 1 record is updated. I want to use for loop is because I want to get the err msg if any item is not saved successfully. So I don't want to put all 10 items in one UPDATE query. How can I solve this problem?

The request data: 
{{.....}, {.....}, {.....}, {.....}, {.....}} #10 item

Code:
router.post('/fruit', (req, res) => {
    var err_list = [];
    for (let r in req.body) {
        #for-loop here is used to put each r into query
        query = "UPDATE ........."
        console.log(query) #output 10 queries with different params

        getConnection(function(err, conn){
            if (err) {
                return res.json({ success: false, error: err })
            } else {
                conn.query(query, function (err, result, fields) {
                conn.release()
                console.log(query, err) #output 10 same queries
                if (err) {
                   err_list.push(err);
                }
              });
            }
          })
        }
        if (err_list.length === 0){
            return res.json({ success: true });
        } else {
            return res.json({ success: false, data: err_list });
           }
        });

connection.js:
const p = mysql.createPool({
  "connectionLimit" : 100,
  "host": "example.org",
  "user": "test",
  "password": "test",
  "database": "test",
  "multipleStatements": true
});

const getConnection = function(callback) {
    p.getConnection(function(err, connection) {
        callback(err, connection)
    })
};

module.exports = getConnection

isn't it working with for loop ? note: in modern world use forEach instead of traditional for loop. — Juhil Somaiya, Mar 13 '20 at 10:51
@JuhilSomaiya Thanks for your reply. The loop is work, when I add console.log(query) right after query = "UPDATE...", it outputs 10 queries with different parameter. However, the console.log(query, err) inside the conn.query will output 10 same queries. At the end, only one record is successfully updated in DB. Why will this happen? — WILLIAM, Mar 14 '20 at 15:40

Guerric P · Accepted Answer · 2020-12-03T17:51:59.497

The reason why this is happening is that the query variable is not declared anywhere. This should cause an error in strict mode (and you should activate it). Now the behavior in your code is the following:

Since query is not declared, it is implicitly a global variable.
As query is global, it's being reassigned on every iteration
getConnection is asynchronous. That means you are telling 10 times "open a connection later" and when the connections are open, the loop has already ended. Every getConnection callback can then access the single query variable that contains the last query.

In short, in order to fix the code, declare query as let query so that it is scoped to the loop, and every loop iteration has its own query variable. Also, prefer using strict mode in order to throw explicit errors when the code is dirty, and spend less time with problems like this.

If `query` is not declared, then it is *not* implicitly a `var`. It's *an implicit global* and a assigned as a property of the global object. Check [the section "Implicit variable declarations"](https://stackoverflow.com/a/500459/). — VLAZ, Mar 24 '20 at 21:31
Thanks for pointing this mistake, not used to the sloppy mode — Guerric P, Mar 24 '20 at 21:32

How to save multiple record to mysql by express server?

1 Answers1