I want to add TeacherSSN & SectionNameID to the table (section), this is the database:

My question is what wrong in this code in the back end (Node js, MySQL, Express)?

app.post("/addSection", (req, res) => {
    const TeacherName = req.body.TeacherName;
    const SectionName = req.body.SectionName;

    const SSN;
    db.query(`SELECT SSN FROM user WHERE FullName = "${TeacherName}"`, (err, result) => {
      if (err) {
        console.log(err)
      } else {
        SSN = result;
        console.log(result)
      }})

    const ID = db.query(`SELECT ID FROM sectionname WHERE SectionName = "${SectionName}"`);
    
    console.log(SSN);
    console.log(ID);

    db.query(`INSERT INTO section(TeacherSSN, SectionNameID) VALUES (?,?)`,
      [SSN, ID],
      (err, result) => {
        if (err) {
          console.log(err);
        } else {
          res.send("Values Inserted");
        }
      }  
  )
    })

How can i get the SSN and ID? To add them to the section table.

CodePudding user response：

Several remarks about your code:

1. The assignment SSN = result happens asynchronously, after the first database query is completed, but you want to use the value of SSN in the INSERT statement, which is executed synchronously. In other words: The first query is sent, then the second query is sent, then the INSERT statement is sent, all synchronously. Only later do the responses for the two queries come in, asynchronously. That's when the (err, result) => ... functions are executed. So SSN receives its value after it has been used.
2. result does not contain the SSN value directly. According to the mysql documentation, you must write SSN = result[0].SSN;
3. Your second query const ID = db.query(...) uses a different form, without the (err, result) => ... callback function. In this form, it returns a promise, not the section ID that you expect.
4. You create the SQL queries through string operations, which exposes your database to the risk of SQL injection. Use placeholders (?) instead.
5. How is the database field sectionname.ID filled during the insert operation?
6. Can you be sure that the FullName and SectionName are unique in their database tables?

Assuming they are unique and the sectionname.ID is generated by the database automatically, you can perform the insertion with a single SQL statement:

db.query(`INSERT INTO section(TeacherSSN, SectionNameID)
          SELECT user.SSN as TeacherSSN, sectionname.ID as SectionNameID
            FROM user, sectionname
           WHERE user.FullName = ? 
             AND section.SectionName = ?`,
         [req.body.TeacherName, req.body.SectionName],
         (err, result) => ...);

This obviates the need to wait for the result of a query before you make an insertion.