使用Node和Express如何向MySQL表中插入数据？

Question

我正在使用Node和Express以及MySQL数据库技术。我创建了一个ejs文件，其中有一个表单（method=POST）。

在我的服务器文件中，我可以检索表单发送的数据，甚至可以console.log并获得所需的输出。由于表单中存在多个输入项，因此我在服务器文件中使用不同的变量来存储这些输入项。

在查询中，当我将这些变量传递给数据库进行插入操作时，数据库仅插入Null，而不是实际存储在变量中的数据。

下面是表单的代码:

    <form action="/newuser" style="border:1px solid #ccc" method="post">
  <div class="container">
    <h1>Sign Up</h1>
    <p>Please fill in this form to create an account.</p>
    <hr>

    <label for="Name"><b>Name</b></label>
    <input type="text" placeholder="Enter Name" name="name" required>

    <label for="email"><b>Email</b></label>
    <input type="text" placeholder="Enter Email" name="email" required>


    <label for="psw"><b>Password</b></label>
    <input type="password" placeholder="Enter Password" name="psw" required>

    <hr>

    <label for="address"><b>Address</b></label>
    <input type="text" placeholder="House no and Street" name="address" required>


    <label for="city"><b>City</b></label>
    <input type="text" placeholder="Enter City" name="city" required>

    <label for="pin"><b>Postal Code</b></label>
    <input type="number" placeholder="Enter pin" name="pin" required>

    <label for="country"><b>Country</b></label>
    <input type="text" placeholder="Enter Country" name="country" required>

    <label for="mobile"><b>Mobile Number</b></label>
    <input type="number" placeholder="Enter Mobile Number" name="mobile" required>




    <div class="clearfix">

     <a href="/"><button type="button" class="cancelbtn">Cancel</button></a>
      <button type="submit" class="signupbtn">Sign Up</button>
    </div>
  </div>
</form>

服务器文件中的代码如下：

 var express = require("express");
var bodyParser = require("body-parser");
var app = express();
var mysql = require('mysql');

app.use( express.static( "public" ) );
app.use( bodyParser.json() );       // to support JSON-encoded bodies
app.use(bodyParser.urlencoded({     // to support URL-encoded bodies
  extended: true
})); 


var connection = mysql.createConnection({
    host: "localhost",
    user:"root",
    password:"",
    database:"bakery"
});


//some more code to get different routes

connection.connect(function(error){
    if(error){
        console.log("Error while connecting to database")
    }
    else{

        // console.log("connected");
 connection.query("SELECT * FROM products", function (err, result, fields) {
    if (err) throw err;
        for (var i = 0; i < result.length; i++) {
       var row = result[i];
       console.log(row.ProductName, "costs", row.Price, "and its from category", row.Category  );
}   
    // console.log(result);
    })
}});


app.use(bodyParser.urlencoded({ extended: true }));
app.set("view engine", "ejs");

app.post("/newuser", function(req, res) {
    // get data from forms and add to the table called user..

    var name = req.body.name;
    var email = req.body.email;
    var password = req.body.psw;
    var city = req.body.city;
    var address = req.body.address;
    var country= req.body.country
    var pin = req.body.pin;
    var mobile = req.body.mobile;

    console.log(name, mobile, pin);

    connection.query("INSERT INTO user (Name, Email, Address, City, Country, password) VALUES", (name , email, address, city, country , password), function (err, result) {
    if (err) throw err;
    console.log("1 record inserted");
  });
    res.redirect("/");

使用这段代码，无论我在表单中输入什么内容，它都只会将 Null 插入到数据库中。

- Mursil

---

你已经记录了 req.body 吗？它包含数据吗？ - Zeeshan Hassan Memon

是的，我做了，并且它在日志中完美地展示了数据。 - Mursil

Answer 1

试试这样做：

var sql = `INSERT INTO user 
            (
                Name, Email, Address, City, Country, password
            )
            VALUES
            (
                ?, ?, ?, ?, ?, ?
            )`;
connection.query(sql, [name , email, address, city, country , password], function (err, data) {
    if (err) {
        // some error occured
    } else {
        // successfully inserted into db
    }
});

- Zeeshan Hassan Memon

---

非常感谢！它完美地运行了。 您能否请解释一下我写的代码为什么不起作用？ - Mursil

太好了，它对你起作用了 :) 我认为语法有问题，你可以继续使用我展示给你的风格，它很容易理解。 - Zeeshan Hassan Memon

Answer 2

我认为问题出在你的查询语法上。我重构了你的代码，改成了下面这个版本。希望能解决问题。

app.post("/newuser", function(req, res) {
    // get data from forms and add to the table called user..

    var reqBody = req.body;

    const name = reqBody.name;
    const email = reqBody.email;
    const password = reqBody.psw;
    const city = reqBody.city;
    const address = reqBody.address;
    const country= reqBody.country
    const pin = reqBody.pin;
    const mobile = reqBody.mobile;

    const queryString = `INSERT INTO user (Name, Email, Address, City, Country, password) VALUES ('"+name+"', '"+email+"', '"+address+"', '"+city+"', '"+country+"', '"+password+"')`;

    connection.query(queryString, function (err, result) {
    if (err) {
        // Throw your error output here.
        console.log("An error occurred.");
    } else {
        // Throw a success message here.
        console.log("1 record successfully inserted into db");
    }
  });

- tMan44wiz

---

这篇文章帮了我很多！非常感谢。 - Valdemar Vreeman

Answer 3

    let name = req.body.name;
    let email = req.body.email;
    let password = req.body.psw;

     const sql = `INSERT INTO user (name, email, password)
       VALUES ('${name}','${email}', '${password}')`;
         db.query(sql, (err, res)=> {
         if(err) throw err;
      })

- Joe Powell