Insert into two dependent tables in MySQL with node.js

Question

I am new to MySQL and node.js (as well as the callback). I try to insert data into table B, which depends on data in table A. Here is an example.

employee table (table A):

CREATE TABLE employees (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(50),
  location varchar(50),
  PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

age table (table B, I do not include age column in table A on purpose):

CREATE TABLE age (
  id int(11) NOT NULL AUTO_INCREMENT,
  index_id int(11),
  age int(50),
  PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

The problem:

For each row inserted into table A, I can get the id for it. I want to use the id as the index_id in table B, and insert the corresponding age info into age table B. The problem occurs when I have multiple rows to insert.

The example:

I implement the following function to achieve above goal.

var mysql = require("mysql");
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "root",
    database: "sitepoint"
});

function insert_employees() {
    var employees = [
        {name: 'Jasmine', location: 'Australia', age: 24},
        {name: 'Jay', location: 'India', age: 25},
        {name: 'Jim', location: 'Germany', age: 26},
        {name: 'Lesley', location: 'Scotland', age: 27}
    ];
    var name_id;
    for (var i = 0; i < 4; i++) {
        var employee = employees[i];
        var command = sprintf('INSERT INTO employees (name, location) VALUES ("%s", "%s");', employee.name, employee.location);
        con.query(command, function (err, res) {
            if (err) throw err;
            name_id = res.insertId;
            console.log('Last insert ID in employees:', res.insertID);

            // insert age here
            var age = employee.age;
            var command = sprintf('INSERT INTO age (index_id, age) VALUES (%d, %d);', name_id, age);
            con.query(command, function (err, res) {
                if (err) throw err;
            });
        });
    }
}

The Output:

The employee table is fine, but for the age table, the age column is 27 for all fields, instead of 24, 25, 26, 27

The Question:

I think the problem is on my misuse of callback feature, but I still don't know how to solve it. Could anyone help me with it? Thanks a lot!

Answer 1

var employee = employees[i];

Change the above line to below, so that variable employee has the correct scope:

let employee = employees[i]; 

Add the following to the beginning of your script, so that let works:

'use strict';