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

Your code is vulnerable to SQL injection attacks. Also, unless this is just a test app, you really, REALLY, REALLY can't store credit card information in your database like that (look up PCI DSS). — AKX
– AKX, Commented Jul 13, 2021 at 8:10
Why are you doing SELECT * FROM table queries with no WHERE or ORDER clauses? There are no guarantees you'd get what you want. — AKX
– AKX, Commented Jul 13, 2021 at 8:13
yup this is just a test app, but ill probably do encryption of the credit card information in the future — 3dsss
– 3dsss, Commented Jul 13, 2021 at 14:01
No, you do not want to encrypt the data yourself. You'll want to use a credit card processor who can tokenize the card on your behalf. — AKX
– AKX, Commented Jul 13, 2021 at 14:07

AKX · Accepted Answer · 2021-07-13 08:21:50Z

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),
  });
});

Hi, thank you so much for your reply, I plan on doing data validation as well as data encryption in the future, this solution helped with the problem i am facing, thanks alot!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related