Storing mysql query rows in variable for later use in another mysql query

Question

I have 2 different tables where the first table is used for retrieving the data to store in the second table, so for example, if there are 3 items in the first table, ill run a for loop 3 times to retrieve the information and storing it in the second table row by row, i also have to use a unique id for the primary key for the second table, so i have to run another mysql query to retrieve all the ID in the second table and add 1 to the id to generate a new ID, but the problem for me is that when i try to put a connection.query in another connection.query, the outer connection.query runs 3 time before running the inner connection.query, hence the items does not get stored and the id does not update, giving me a duplicate primary key error, i tried using functions but it doesnt help, here is

app.get('/submit-check', function (request, response) {
  function insert(sql2) {
    connection.query(sql2, function (err, result1) {
      if (err) throw err;
    });
  }
  function other(value, index) {
    let sql = "SELECT * FROM ORDERS"
    connection.query(sql, function (err, results) {
      var id;
      if (results.length == 0) {
        id = 0
      } else {
        id = results[results.length - 1].orderID;
      }
      // for (let j = 0; j < results.length; j++) {
      //   list.push(results[j].orderID)
      // }
      // if (list.length == 0) {
      //   id = 0
      // }
      // else {
      //   var largest = 0;
      //   for (let i = 0; i <= list.length; i++) {
      //     if (parseInt(list[i]) > largest) {
      //       var largest = parseInt(list[i]);
      //     }
      //   }
      //   id = largest + 1
      // }
      id = id + 1;
      var add = request.query.state + " " + request.query.address + " " + request.query.Unumber + " " + request.query.zip
      var custID = value[index].custID
      var bookID = value[index].bookID
      var Email = request.query.email
      var CardName = request.query.cardname
      var CardNumber = request.query.cardnumber
      var ExDate = request.query.expmonth + "/" + request.query.expyear
      var CVV = request.query.cvv
      var qty = 1
      var sql2 = "INSERT INTO orders (orderID,custID, bookID, QTY, CardName, CardNumber, ExDate, CVV, Email, Address, createdAt, updatedAt) VALUES('" + id + "','" + custID + "', '" + bookID + "', '" + qty + "', '" + CardName + "', '" + CardNumber + "', '" + ExDate + "', '" + CVV + "', '" + Email + "', '" + add + "', CURDATE(), CURDATE() )"
      insert(sql2)
    })
  }
  function setValue(value) {
    for (let index = 0; index < value.length; index++) {
      other(value, index)
    }
  }
  let sql3 = "SELECT * FROM carts"
  var cart_db;
  connection.query(sql3, function (err, result) {
    cart_db = result
    setValue(cart_db)
  })
  // console.log(cart_db)
  response.render("aftercheck", { attributes: request.query, cardno: "****-****-****" + request.query.cardnumber.slice(-5,) });
})

thank you in advance

Answer 1

There are many, many things wrong with your code. I've peppered this await/async-based rewrite with TODO comments where you'll probably need to fix up things. Naturally I haven't been able to test this since I don't have your database.

async function queryP(connection, sql, parameters = undefined) {
  return new Promise((resolve, reject) => {
    connection.query(sql, parameters, function (err, results) {
      if (err) return reject(err);
      resolve(results);
    });
  });
}

app.get("/submit-check", async function (request, response) {
  // TODO: these need to be validated
  const {
    address,
    Unumber,
    zip,
    expyear,
    state,
    expmonth,
    email: Email,
    cardname: CardName,
    cardnumber: CardNumber,
    cvv: CVV,
  } = request.query;
  const add = `${state} ${address} ${Unumber} ${zip}`;
  const ExDate = `${expmonth}/${expyear}`;

  const cartData = await queryP(connection, "SELECT * FROM carts"); // TODO: this is not safe with multiple customers
  const [maxOrderIdRow] = await queryP(connection, "SELECT MAX(id) as maxid FROM orders");
  const orderId = (maxOrderIdRow.maxid || 0) + 1; // TODO: this is not safe in the face of concurrent requests
  // Using a for loop rather than .map and await and so on is simpler here
  for (let i = 0; i < cartData.length; i++) {
    const cartRow = cartData[i];
    const custID = cartRow.custID;
    const bookID = cartRow.bookID;
    const qty = 1;
    // TODO: rethink how credit cards are saved – we can't save them in the database like this or we'll be out of business
    await queryP(
      connection,
      `INSERT INTO orders (orderID, custID, bookID, QTY, CardName, CardNumber, ExDate, CVV, Email, Address, createdAt, updatedAt) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CURDATE(), CURDATE() )`,
      [orderId, custID, bookID, qty, CardName, CardNumber, ExDate, CVV, Email, add],
    );
  }

  response.render("aftercheck", {
    attributes: request.query,
    cardno: "****-****-****" + CardNumber.slice(-5),
  });
});