Inserting multiple information in different table, Mysq, Node.js

Question

so, i have three tables, user, modules, and an intermediate table user_module, to do an inner join between user and module i have to access thorough user_module

"SELECT user.uName, module.moduleName FROM user_module INNER JOIN user ON user.userID = user_module.userID_FK INNER JOIN module ON module.moduleID = user_module.moduleID_FK;",

i have a form that allows me to insert the information for the user, and i would like to chose which module a user take. until now i have been putting the information manually with phpmyadmin for the user_module table, the rest f the information for user comes from the node.js code

const parsed = bodyParser.urlencoded({ extended: false });
app.post("/", parsed, (req, res, next) => {
  console.log(req.body);
  connection.query(
    "INSERT INTO user(name,surname,gender,dateOfBirth,email,userName,password) values('" +
      req.body.name1 +
      "','" +
      req.body.surname1 +
      "','" +
      req.body.gender +
      "','" +
      req.body.birtdate +
      "','" +
      req.body.email1 +
      "','" +
      req.body.username1 +
      "','" +
      req.body.password1 +
      "');",
    (err, row, field) => {
      if (!err) {
        res.redirect("/");
      } else {
        console.log(err);
      }
    }
  );
});

But how i can take the information from the form, and put the 2 id's in the intermediate table?

<form action="/" method="post">
      <p>Name:</p>
      <input type="text" name="name1" /><br />
      <p>Surname:</p>
      <input type="text" name="surname1" /><br />
      <p>Gender:</p>
      <select name="gender">
        <option value="m">Male</option>
        <option value="f">female</option> </select
      ><br />
      <p>course:</p>
      <select name="module">
        <option value="1">Programming</option>
        <option value="2">webApp</option> </select
      ><br />
      <p>Date of Birth:</p>
      <input type="date" name="birtdate" placeholder="select Birth date" />
      <p>Email:</p>
      <input type="email" name="email1" />
      <p>Username:</p>
      <input type="text" name="username1" />
      <p>Password:</p>
      <input type="password" name="password1" /> <br />
      <input type="submit" value="submit" />
    </form>

i never worked with node.js but i do know EcmaScript (Javascript) but i am pretty sure that code is prone to (blind) SQL injections.. — Raymond Nijland
– Raymond Nijland, Commented Nov 7, 2019 at 19:48
A manual about using a MySQL in node.js seams to be supporting mine statement -> Escaping query values — Raymond Nijland
– Raymond Nijland, Commented Nov 7, 2019 at 19:53

T0plomj3r · Accepted Answer · 2019-11-07 20:03:17Z

1

The order of actions you do is really important here:

1) You have to save the user first because you need to have its id. After you have successfully saved it, then you need to get its id. Sometimes after successful save some frameworks provide the ID of the saved entity, but you can always get it manually, via a new SQL query to get the id of the user with a specific email.

SELECT id from users where email='email_of_the_user_you_just_saved"

2) After you have user ID, and you have collected module id from the frontend, then simply make insert into intermediate table user_module.

INSERT INTO user_module (user_id, module_id) VALUES (users_id_you_got_from_step_1, module_id_you_got_from_frontend);

answered Nov 7, 2019 at 20:03

T0plomj3r

6951 gold badge9 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Inserting multiple information in different table, Mysq, Node.js

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related